March 5, 2009 at 9:28 am
Hi there
I am working few SQL tables. I am using lots of temp tables to derive some columns. Finally, I am joining these temp tables to get desired result set. I have created a stored procedure for executing the sql script.
Though the execution time has decreased from earlier but I am trying to reduce it more. for which, I am seeking some tips/points for making sproc executes faster as in where it consists of lots of temp tables.
Please Suggest.
Thanks!
March 5, 2009 at 9:50 am
Generically, make sure that the queries that populate the temp tables are properly set up (indexes, etc.), make sure that any of the temp tables that can benefit from indexes on them have such, and make sure the temp tables themselves are well-built for fast inserts and queries.
Without more data, like the definition of the proc, I can't easily get more specific than that. Is that something you can provide?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 5, 2009 at 10:05 am
Please don't cross post. It just wastes peoples time and fragments replies.
No replies to this thread please. Direct replies to: http://www.sqlservercentral.com/Forums/Topic669345-360-1.aspx
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 5, 2009 at 10:44 am
Some generals
* Use of "SET NOCOUNT ON" is an important option for stored procedures.
* Avoid to much dynamic SQL generation
* If you use dynamic SQL generation determine if RECOMPILE may help you for statistics and execution planning
* Avoid much reads to the same data within the main tables. If you access many times the same part of the data tables determine if it gets faster to get the specific data into a temp table and only work with this (do not forget that this becomes a snapshot!)
* Indexes and primary keys (as already posted) on temp tables are very important
* Avoid "SELECT *" statements
* Avoid sub-queries
* Avoid "WHERE ... IN (SELECT bla FROM blubb)" !!!
* Avoid CURSORS !!!
For more specific information please describe your business case.
Greets
Flo
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply