SQL Server Performance

Performance query

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by stevedb, Aug 29, 2008.

  1. stevedb New Member

  2. preethi Member

    Hi,In your query you are trying a lot of stuff
    1. Creating a Lot of Temp Tables and Indexes.
      • Using these Temp Tables to join/filter with your main query
        • Drop your Temp tables
        • SQL Server Depends on statistics and Indexes to generate an execution plan. When these conditions change the execution plan becomes invalid. As and when you drop the temp tables the execution plans become invalid and it needs to be recompiled.
          Additionally, You are using a lot of Optional parameters and using 'OR' to join various conditions. This is one of the killing situations. You may have to try with IF conditions to make them choose the correct plan.
          In situations like this I have tried these options.
          • Instead of Temp tables I have used Permanant tables. I have added an additional key SPID to make sure I do not read someone else's data.
            • I seperate different scenarios and write different SQL statements for them. Yes, it is a pain for developer from maintainability point of view, but you will, not only increase the performance but also make the code easier to debug
            • Hope this helps!
  3. stevedb New Member

    Thanks Preethi
    For the reply
    How would I re-write the query to remove the OR to make the query more efficient?
    Also I am interested in the permanent table, this is not something I have though about
    Can you please give me an example of how to write different scenarios and write different SQL statements for them
    And how to create a permanant table with a SPID
    Thanks for your help
  4. stevedb New Member

  5. preethi Member

    To the Moderators/Administrator,
    As most of the posts have been removed, it is not worth having the thread. It is just wasting space.
    Either we should restore the posts back, (which is useful for others to search on) or remove this thread completely. It is also generates another question, Can we allow people to remove the entire post?

Share This Page