December 18, 2007 at 6:16 am
I am looking at a table-valued function, where performance is a
problem. One thing I notice is an update statement, where scores of
CASE statements are used to determine, if an attribute should be
updated with value A or value B. The CASE statements are all the
same:
WHEN @Ind = 0 THEN A
ELSE B
I wonder: would it be faster to split this update statement in 2
parts, 1 with updates for @Ind=0 and 1 for @Ind=1? We are talking here
about millions or rows being checked.
Greetz,
Hans Brouwer
December 18, 2007 at 7:12 am
Without the context in which you use that - it's going to be hard to know whether it will or not.
Would a query run faster without a case if it can be written without one? It's possible. On large tables though, it's often faster to do a single pass through the data than 2 even if the two actually run faster. Again - without context - hard to be specific.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
December 18, 2007 at 7:14 am
Since this is a case against a variable, I would tend to say it will probably perform ok.
If you case is using a column in the table, the case would need to be evaluated for every row causing a table scan. This may still be happening even with a case on a variable. The best way to be sure is to try running your update (hopefully in a test environment) and check the execution plan.
December 19, 2007 at 5:47 am
Even when you use case against a column in a table, that alone isn't at all connected to if the query will do a scan of that table or not.
The case will be evaluated for every row in the result- if that result can be satisfied by index seek, then there will be no scan - case or no case.
/Kenneth
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply