April 14, 2008 at 3:59 pm
What is the best way to write a SQL query or a Stored procedure in terms of Performance?
OR
How can I imporve the performance of a query
April 14, 2008 at 4:16 pm
The advantage of using stored procedures is that SQL Server keeps the execution plan in cache so that subsequent executions of that SP do not require a recompilation, in theory. So placing a query within a stored procedure will give you that benefit.
As far as improving performance of a query, that is too broad a question for me to cover here without writing pages of to-dos. Search SSC through the articles seciton for generalized help with query tuning. If you have a specific query that needs looked at, post it and we'll have a look at it.
Here are some pointers on how to get good help with your query:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
April 14, 2008 at 8:29 pm
jagadish_sds (4/14/2008)
What is the best way to write a SQL query or a Stored procedure in terms of Performance?
Not trying to be a smart guy here, but this very open ended question simply means... avoid RBAR, add the correct indexes, avoid things in the WHERE clause that will prevent an Index Seek from being used, and do things in a set-based fashion... the does NOT mean doing it all in a single query.
How can I imporve the performance of a query
Again, not being a smarty pants here... the truth is that if you've added the correct indexes and performance doesn't increase by much, then you're going to need to rewrite at least parts of the stored procedure to be set-based and index enabled.
And, just in case someone asks, buying hardware twice the size and twice the speed usually doesn't help performance on bad code, RBAR code, or code that prevents indexes from being used in Seeks.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply