|
... and performance. I know dynamic sql queries now get the query plan cached, but for anything slightly complex a stored procedure will consistently outperform dynamic sql. You are also (or should be) moving lots less data around the network - consider the select statment for a table of 25 columns compared to a simple "up_getaccount @accountid= 'abc'"
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Ashfield wrote: and performance
Negligable these days... Also you can really screw up a sproc if it has a conditional statement with each path requiring a wildly different query plan. The first time the sproc is used it will cache the query plan. When it goes down the other path in subsequent calls it will be using an inefficient query plan.
|
|
|
|
|
Colin Angus Mackay wrote: if it has a conditional statement with each path requiring a wildly different query plan.
Should not be in the same proc then. Maybe have one main proc which checks the conditional variable and calls a further proc dependant on the value (which will then be properly optimised)
As for using stored procs, if you want more than a reasonably straight forward query there are far more opportunities for perfomance tuning in a proc than a piece of sql.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Ashfield wrote: Should not be in the same proc then.
Absolutely - But is it something most people will think about?
Ashfield wrote: there are far more opportunities for perfomance tuning in a proc than a piece of sql.
Really? Isn't a stored proc just "a piece of sql"?
|
|
|
|
|
Colin Angus Mackay wrote: Absolutely - But is it something most people will think about
Well, if people don't think about performance it's their own fault.
Colin Angus Mackay wrote: Really? Isn't a stored proc just "a piece of sql"?
I was, I thought rather obviously, talking about real world code, where you frequently do a bit more than select a few fields from a couple of tables. For example taking a transactioon file of 2 million records and generating positions - try getting decent performance in something like that without stored procs. However, if you prefer to live in the world of CP type questions and believe that everything can be done with a few selects thats up to you - those of us that produce code for a living know the pros and cons of different methods but are always open to suggestion.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Ashfield wrote: those of us that produce code for a living know the pros and cons of different methods but are always open to suggestion.
You don't sound very open to suggestion. It sounds like you are tying to insult me.
All I am saying is that a stored proc is just a piece of SQL. It happens to be stored in the database rather than have an application send it out. But, you can pack quite a lot in to a SqlCommand object if you have to. And if the SQL is highly changable I'm glad of that feature. It is, admittedly, not something I've put into production code, but if you need something quick, ad hoc, and throw away these features can be very useful.
Also, you can do just as much by sticking all the SQL into the SQL Command as you can by sticking the same SQL in a stored procedure. I also don't recommend doing that in a production environment. But those of us that produce code for a living know that sometimes you need something quick and dirty to get something going, such as a test environment or for a one time migration of data from one system to another.
At the end of the day you cannot simply say you have to use stored procedures for everything. You have to be open to the possibility that you can do the job with out them sometimes and it is acceptable to do so. You also have to know that they do make a lot of sense in many other scenarios.
|
|
|
|
|
Colin Angus Mackay wrote: You don't sound very open to suggestion. It sounds like you are tying to insult me.
Any insult was unintentional, I was merely trying to emphasise the difference between the college type of question we frequently get here on CP and the real world.
For a 'quick and dirty' we all do things we wouldn't put into production.
Colin Angus Mackay wrote: At the end of the day you cannot simply say you have to use stored procedures for everything.
As a long tme contractor, developing solutions in SQL, C#, VB.NET and ASP.NET, I have worked fr many clients (mostly blue chip financials), and the vast majority will not allow embedded sql, all database access is via stored procedures - for the very reason of change. Making a change to a stored procedure (which may then be replicated out) is far preferable and much quicker than co-ordinating an application roll out an to users worldwide for what may only be a one column change. As far as I am concerned embedding sql in a production strength application is an absolute no-no, but everyone to their own opinion.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Ashfield wrote: I have worked fr many clients (mostly blue chip financials), and the vast majority will not allow embedded sql, all database access is via stored procedures
What, even if your application is just kicking of one-time migration scripts in a specific sequence?
|
|
|
|
|
Colin Angus Mackay wrote: What, even if your application is just kicking of one-time migration scripts in a specific sequence?
Here we have the difference. You see, I would not consider that a production app, although I do see what you mean - it running in a production environment. I consider a production app as one released out to users, running regularly, with all the overheads of releases. The situation described by you I would describe more as a utility program - one written to assist with a one off specific task. In this instance I would use (within reason obviously) whatever technique achieved the desired results in the shortest time.
Different perspectives make for misunderstandings I guess.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Ashfield wrote: I would not consider that a production app
Fair enough. I see anything that runs on a live system as a production app - even if it is a one time thing like a migration process.
|
|
|
|
|
Ashfield wrote: those of us that produce code for a living know the pros and cons of different methods but are always open to suggestion.
That's an arrogant attitude you've got going for you there Bob. Before you try belittling somebody, perhaps you ought to take a look at who they are. Let's see - Colin; Microsoft MVP. CodeProject MVP 2005 through to 2008. Author of several well respected articles. Which bit of this suggests that he doesn't write code for a living to you? Well?
Perhaps you should have dialled back the attitude a bit and actually looked at what Colin had to say - rather than taking the chance to blow your own trumpet.
|
|
|
|
|
OK, apologies to any one who was offended or felt they were offended. I have to admit, I didn't look at Colin's profile, but as I explained in a subsequent post, I was trying to emphasise the difference between the college type environment and the real world, where you are dealing with large volumes of data and performing much more complicated operations.
However, once again I apologise if anyone was offended.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Everything you can do in the body of a stored proc you can do in a regular sql call, and the engine does a pretty good job at caching execution plans. Most stored proc performance benefits have been optimized away over the past few releases of SQL server.
I can imagine the sinking feeling one would have after ordering my book,
only to find a laughably ridiculous theory with demented logic once the book arrives - Mark McCutcheon
|
|
|
|
|
Nope I agree with Bob, while you "can" use embedded SQL it is not a good solution for a production app (and I have the same opinion of the 1 off solution). Mind you I'm in the same boat, long term contractor with financial institutions so probably run under the same restrictions.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
The last app I worked on was a shrink wrap CRM application that allowed customers to add their own business objects and tables. For that we had to generate SQL dynamically. There are pros and cons to any approach. When I'm designing a basic application, I usually start off with stored procedures just because I like their simplicity.
I can imagine the sinking feeling one would have after ordering my book,
only to find a laughably ridiculous theory with demented logic once the book arrives - Mark McCutcheon
|
|
|
|
|
Speed: You only pass the SP parameters and it pass back only the result and not the full table.
|
|
|
|
|
A Wong wrote: You only pass the SP parameters and it pass back only the result and not the full table
Who said they were passing full tables? If you perform a query on a table you only get back what you asked for. You never get back the full table unless that is explicitly what was requested.
|
|
|
|
|
Colin Angus Mackay wrote: Who said they were passing full tables?
Well he/she said:
What is the benifit i get using stored procedure instead of tables in my web application using asp.net with c#
From what I understand of the question, he/she is pulling in the tables and splitting out the information he/she wants in C#/asp.net. Of course, I could be wrong in my interpretation as the question is not quite solid.
|
|
|
|
|
A Wong wrote: From what I understand of the question
Well, you might be right. But who in their right mind would do that?
|
|
|
|
|
Convenience, if you use stored procedures, you are embedding a catalog of the sql queries used on the database with the database. It can make it more convenient to add indexes for those queries at a later point without having to search through the text in your profiler output.
I can imagine the sinking feeling one would have after ordering my book,
only to find a laughably ridiculous theory with demented logic once the book arrives - Mark McCutcheon
|
|
|
|
|
Not shure I should ask this here but don't really know a better forum for it so here goes
Since about 4 hours now I'm (suddenly) unable to connect to my company's sql-server
The errormessage: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified
I tried everything I could find on the net (using google) but nothing works
I checked the server and the client protocols. All are enabled and match.
Using named pipes or tcp/ip doesn't change anything (same error)
My connectionstring:
"Data Source=<servername\instancename>;Initial Catalog=<dbname>;User ID=<id>;Password=<pass>"
The connectionstrings I tryed:
"Data Source=tcp:<servername\instancename>,1433;Initial Catalog=<dbname>;User ID=<id>;Password=<pass>"
"Data Source=np:<servername\instancename>;Initial Catalog=<dbname>;User ID=<id>;Password=<pass>"
I'm still able to ping to the server and even open network shares (read/write to the networkshare)
I tryed making an dns connection but that fails aswell
The only thing that changed is that the client was inserted into the domain yesterday but I was still able to connect this morning and at the moment I'm still logging in localy.
Otherwise nothing changed on the server or the client (no updates, no new software, ...)
There is no firewall enable on the server or the client.
The really strange thing is that when I started my programme at 12.25 everything worked perfectly but when I started it 2 min later I started to get this error.
In those 2 min nothing happend (I didn't even change any code)
The client logs show nothing for that moment.
I'm at a lost to what it might be.
Any help would be appriciated
EDIT:
the problem appears to have solved itself
I just tryed to connect again and now I have no problems anymore
If anyone has any suggestions to what it might have been please tell me because I hate it when things like this happen
modified on Tuesday, June 24, 2008 10:25 AM
|
|
|
|
|
Hi,
iam using "Firebird Isql" Command Promt Database..
iam searching for Export and Import options..I find "FBExport" tool..
now my problem is how to use "FBExport" tool...
i download files which are available in SourceForge.net,and extract the files and run exe
it's just saving the path...
please inform how can i use this "FBExport" tool...
murali krishna
|
|
|
|
|
I already replied to this, suggesting you contact the product vendor. Nobody else replied, either because they couldn't suggest anything better or they had no idea. Posting the same question again (with only 1 other question inbetween) will not help.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
How i can Register new sql server in enterprise edition.
|
|
|
|
|
Menu View>registered Servers then Right Click New>server registration
Bob
Ashfield Consultants Ltd
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
