SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Want something like short-circuit


Want something like short-circuit

Author
Message
ummfish
ummfish
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 34
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?
ColdCoffee
ColdCoffee
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15727 Visits: 5555
Try dynamic SQL.. build your final query using @Flag...
ummfish
ummfish
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 34
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.
ColdCoffee
ColdCoffee
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15727 Visits: 5555
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...
ben.bizzell
ben.bizzell
Mr or Mrs. 500
Mr or Mrs. 500 (541 reputation)Mr or Mrs. 500 (541 reputation)Mr or Mrs. 500 (541 reputation)Mr or Mrs. 500 (541 reputation)Mr or Mrs. 500 (541 reputation)Mr or Mrs. 500 (541 reputation)Mr or Mrs. 500 (541 reputation)Mr or Mrs. 500 (541 reputation)

Group: General Forum Members
Points: 541 Visits: 1028
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search