Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Want something like short-circuit Expand / Collapse
Author
Message
Posted Tuesday, August 17, 2010 9:37 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, March 18, 2011 12:13 AM
Points: 2, Visits: 32
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?
Post #970863
Posted Wednesday, August 18, 2010 1:07 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, September 12, 2014 5:21 PM
Points: 2,262, Visits: 5,406
Try dynamic SQL.. build your final query using @Flag...
Post #970950
Posted Wednesday, August 18, 2010 1:48 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, March 18, 2011 12:13 AM
Points: 2, Visits: 32
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.
Post #970962
Posted Wednesday, August 18, 2010 2:04 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, September 12, 2014 5:21 PM
Points: 2,262, Visits: 5,406
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...
Post #970968
Posted Tuesday, January 4, 2011 1:01 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 3:28 PM
Points: 380, Visits: 648
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.
Post #1042656
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse