reducing execution time of Stored Procedure

  • 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!

  • 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

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • 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