August 17, 2010 at 9:37 pm
In a SP, there is something like this:
INSERT INTO #temptable
SELECT *
FROM sometable1
WHERE @flag = 0 AND (...something else...)
UNION ALL
SELECT *
FROM sometable2
WHERE @flag <> 0 AND (...something else...)
That is the @flag is used to choose the data source for #temptable.
The execution plan showed that both sometable1 and sometable2 were read (seek or something else). The profiler also showed a lot of reads. However, we know that if the condition @flag = ? is evaluated first, only one of the tables should be read.
I tried to use if-else and it worked. But the real query is much more complicated (two flags, one has two possible values for choosing tables, the other six possible values for choosing proper where-clause...) and there is no way to maintain the code if if-else is used.
Any good idea to avoid the unnecessary reads?
August 18, 2010 at 1:07 am
Try dynamic SQL.. build your final query using @Flag...
August 18, 2010 at 1:48 am
ColdCoffee (8/18/2010)
Try dynamic SQL.. build your final query using @Flag...
You are right. I did it by using sp_executesql. But I want it runs really fast. The performance penalty may make it slow. Actually it is slower when the cache is hot.
PS: I tried OPTION (RECOMPILE) as well. The number of reads decreased a lot but the overall performance became lower.
August 18, 2010 at 2:04 am
ummfish (8/17/2010)
I tried to use if-else and it worked. But the real query is much more complicated (two flags, one has two possible values for choosing tables, the other six possible values for choosing proper where-clause...) and there is no way to maintain the code if if-else is used.Any good idea to avoid the unnecessary reads?
Create a table ; put all the possible values and it's corresponding WHERE clauses in that (3 column in the table , one for value wit an INDEX, one for WHERE clause and one as IDENTITY column).
Based on your input value (which is going to be one value in your possible values) , pull its corresponding WHERE clause; build the dynamic query using that WHERE clause (i suppose that the SELECT clause and JOIN clauses (if any) will remain the same for all possible values) and execute it.
This way u eliminate the use of IF...ELSE or CASE constructs...
January 4, 2011 at 1:01 pm
I would be VERY tempted to create multiple stored procedures, one as a "parent", and one for each of the possible combinations of the @flag variables.
Call the parent passing in the flag(s), use one or more nested case statements (or a series of IF statements, whatever) in the parent to determine which of the child procedures is to be called. If there are additional parms, other than the flags, they can be passed to the child proc as needed.
Since each of the child procs will do only what is necessary for that flag combination, each can be optimized separately, and only those tables needing to be read will be read for any specific call. You will not have to recompile on each call, and each separate proc, being unique to that specific flag combination, can determine a good query plan for that combination.
It does introduce more separate bits of code (i.e., procedures) to be maintained, but it also simplifies each piece, and makes it much easier when (not if) something changes or a new flag combination is needed, simply create a new SP to handle the new combination, then modify the parent to call the new proc with that flag combination.
It may be a style thing, but I prefer a lot of simple procedures, each doing one specific thing, to one massive proc, filled with conditionals and control-of-flow. I believe it makes the code easier to understand, to document, to maintain, and to troubleshoot.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply