March 28, 2006 at 9:15 am
I have an application which is used for Data storage and retrieval. This system has 3 major tables and total db size is near 250 GB. I have an application which requests data using embedded SQL queries. Is thier an advantage to using Stored Procedures rather than embedded SQL?
March 28, 2006 at 9:37 am
Stored procedures should perform better, in general. Network traffic is optimised and SQL Server should be able to reuse execution plans when running SPs multiple times.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
March 28, 2006 at 3:18 pm
Always use stored procedures. I really see absolutely no need to hardcode SQL code.
Cached execution plans. If you execute a stored proc, it will be compiled and held in memory, so when you next access the proc, SQL Server will check it's cache to see if there is a match and if so, use the cached copy
Centralised business logic. It's a complete nightmare for developers to have to modify the application as well as backend code
If you need to change the logic only and not modify parameters, then you update the stored proc, save it..Job done. If the code is embedded in the application you need to roll the application out to all the clients. Admitedly, if its a web application you only need to save the code to the web server, but why recompile code if you don't need to?
Network traffic. If you are using embedded code to run a batch, you'll be sending data to and throw and acting on that data and sending another request to SQL Server. Throw it all in one stored proc and call other procs. Return only the data you actually need
Those are just a few reasons. I'm sure people can suggest other reasons.
Clive
March 29, 2006 at 5:03 am
On the other hand - are you asking because you have problems ? if not then there's always the old addage " if it aint broke don't fix it"
It's worth noting that dynamic sql gets cached too and unless it's very simple will get re-used too . I'm not a lover of any form of access other than procs - but life and applications don't always give that option.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
March 29, 2006 at 8:12 am
I ask the question because I am trying to find ways to 'tune' the performance of my application. Users sometimes complain due to slow responses. The db is used as a large storage system for electonice images and documents. The application lets users choose how they want to search for these documents. They can search by date created, date modified, or some other constraint such as name, or amount.
Are stored procedures flexible enough to be able to handle this type of user interaction?
March 29, 2006 at 8:18 am
SPs do not handle user interaction - your app does that.
It should then call SPs, with appropriate parameters, to do the data processing side - whether it be data retrieval, updates, deletes etc.
So the only embedded SQL in your app consists of procedure calls - your app deals with all user interaction and presentation of the results of the procedure calls.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
March 29, 2006 at 11:58 am
yes and no to the sp question - if you code effectively then yes. Indexing is the main key, or I should say efficient useful indexes, they will speed your searches - regardless of how the queries are formed.
Also make sure your stats are up to date.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
March 30, 2006 at 4:55 am
Another topic about stored procedures:
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=209619#bm210035
Dynamic sql is also compiled at first use and the execution plan is stored in cache. There is no diverence.
March 30, 2006 at 2:21 pm
March 30, 2006 at 10:15 pm
I tried SP's vs Dynamic SQL. Here is the short version. 100 times faster with the dsql and we do over a trillion row lookups per year. If you can put login in the app I see no benifit in putting it in the SP. x = x+1 in Tsql until x = 10,000,000. Try it both .net and Tsql. If logic is a pig in TQSL. In the days of slow networks and where db servers were the fastest boxes in a company and the lack of programming skill sets and poor data access layers, SP's made alot of sense. Outside of administrative tasks, I don't see the benifit. Well that is not true, there are benifits. I should qualify that, speed is NOT a dead sure a benifit, not that I see. Not in my apps, perhaps in some peoples.....
Dave your scenario seems relativly easy to try sevral strategies. So it would seem, I don't know details of course. Try pure dsql returning a recordset.
Good luck!
-Eric
April 11, 2006 at 8:26 pm
Eric, I'm not sue I agree. Dynamic SQL 100 times faster...really? Are you simply trying to say T-SQL won't loop to 10 million quite as fast as truly compliled code. Ok, I'll give you that. Although the CLR might help out with what you have in mind.
Anyway, it comes down to using the right language for each task. Loop like crazy, lose yourself in jagged arrays, and go nuts with conditional logic in .NET. BUT, when it comes to making DB calls and working with SETS of data, use SQL objects when possible.
I would hate to design, test, optimize, etc. some of my more complex SP's that utilize table variables, derived tables, etc. via dsql coming from a web page.
Ryan
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply