RE: Query Performance ~ Split the SP.
From: SQLCatz (SQLCatz_at_discussions.microsoft.com)
Date: 09/07/04
- Next message: Adam Machanic: "Re: removing a secondary log file?"
- Previous message: paolo.ricci_at_gidi.it: "Re: invalid object name"
- In reply to: hdsjunk: "Query Performance"
- Next in thread: Roji. P. Thomas: "Re: Query Performance"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 7 Sep 2004 06:45:05 -0700
Hello Heidi,
Solution below does not involve checking / optimizing indexes.
We had a problem similar to what you are facing. A part of the problem was
that our stored procedure was too generic. Based on the values of the input
parameters - It could execute 1 of 4 different queries.
I re-wrote the functionality in a different way.
Split the single SP into a 2 level hierarchy.
The top level wrapper SP would check the input parameters and then decide on
which 2nd level SP to call. Since there were 3 2nd level SPs - which were
specific - SQL server would create a plan for each one of them - which would
get cached and re-used.
In the earlier scenario - where there was one massive generic SP - SQL
Server would create a plan for the first path that the query would take. When
the parameters changed and the flow inside the SP had to take another route -
the existing plan was not efficient - so SQL Server would create another
plan.
Same process continues for each of the 3 different queries. Very inefficient.
The small change (splitting the SP) itself gave us quite a big performance
gain.
Added advantage - the code was more manageable too.
Our DBA was OK with having dependencies between SPs in his DB!
Try this approach first, before you move onto the indexes.
Cheers!
- Next message: Adam Machanic: "Re: removing a secondary log file?"
- Previous message: paolo.ricci_at_gidi.it: "Re: invalid object name"
- In reply to: hdsjunk: "Query Performance"
- Next in thread: Roji. P. Thomas: "Re: Query Performance"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
