July 18, 2011 at 4:59 pm
Hello all,
I have a select statement something like this.
SELECT
....
COALESCE
(
here I take the first non-null from 4 conditions, the conditions are fairy complicated
) AS MyComplexCalc
FROM...
My additional requirement is that if the complicated calc returns a negative number replace that with 0.
The following should work but I don't like repeating the calc from a code maintenance or performance perspective. Is there something cleaner where I do not have to repeat the calc?
SELECT
....
CASE COALESCE (here I take the first non-null from 4 conditions, the conditions are fairy complicated) <0
THEN 0
ELSE
COALESCE
(
here I take the first non-null from 4 conditions, the conditions are fairy complicated
)
END AS MyComplexCalc
FROM...
July 18, 2011 at 5:22 pm
Use a cte, like so:
;with cte (newvalue) as (select COALESCE etc FROM sometable)
select case when newvalue < 0 then 0 else newvalue end as newvalue from cte
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
July 18, 2011 at 6:33 pm
July 18, 2011 at 8:44 pm
And also...
-- To make result = 0 when @test-2 < 0
declare @test-2 int -7
select (ABS(@test)+@test)/2 as result
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
July 18, 2011 at 9:32 pm
CELKO (7/18/2011)
SIGN (1 + SIGN(x)) * xbut it means using x twice. Hide it in a CTE and use the twice.
CTEs generally don't help - the expression is evaluated twice.
I included the TOP trick as the only way I know to force the query processor to evaluate just once.
July 18, 2011 at 9:34 pm
The Dixie Flatline (7/18/2011)
And also...
select (ABS(@test)+@test)/2 as result
Neat!
July 19, 2011 at 5:17 pm
SQLkiwi (7/18/2011)
CTEs generally don't help - the expression is evaluated twice.I included the TOP trick as the only way I know to force the query processor to evaluate just once.
Here is another way then (learned from Stefan G) to force the calculation to only happen once:
;with calc(result) as
(
select COALESCE(@A, @b-2, @C, @D)
union all
select null
from calc
where 1=0
)
select calc.result * ((sign(calc.result) + 1) / 2)
from calc
The recursive part of the CTE returns no extra data, but coerces sql into storing the results (it will plop the results in tempdb once there are enough I believe) rather than re-evaluating them - it's a nice little trick that can sometimes help.
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);July 19, 2011 at 5:17 pm
SQLkiwi (7/18/2011)
The Dixie Flatline (7/18/2011)
And also...
select (ABS(@test)+@test)/2 as resultNeat!
Agreed - good thinking there Bob
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 19, 2011 at 5:36 pm
mister.magoo (7/19/2011)
The recursive part of the CTE returns no extra data, but coerces sql into storing the results (it will plop the results in tempdb once there are enough I believe) rather than re-evaluating them - it's a nice little trick that can sometimes help.
I've seen Stefan use that before too. IIRC though, you ought not use WHERE 1 = 0 in the recursive part because contradiction detection in QO kicks in and removes the clause completely. If you try that example code with the four variables, you'll see a plan with a Constant Scan and a Compute Scalar - not the usual recursive structure with its stacking index spool and so on. Nevertheless, even with the contradiction in place, the Constant Scan does evaluate the core expression and the Compute Scalar references that twice. This is no guarantee that the core expression won't be evaluated twice - the following 'bug' for example is not fixed in any current build (though it is fixed in Denali CTP3):
July 19, 2011 at 5:43 pm
CELKO (7/19/2011)
I am hoping that the optimizer will "do magic" when the same scalar value appears in a single expression after the table is constructed.
Generally speaking, the optimizer is not nearly as smart in this area as we should perhaps expect.
In SQL Server, scalar computations are not really costed or optimized at all. The thinking was that simple scalars were always ridiculously cheap - an assumption that rarely holds water these days (functions, for example, may be arbitrarily complex). There do appear to have been a few minor improvements for Denali (see my reply to Mr Magoo) but nothing like a fully generalized expression service or manager which would optimize for the number of expression evaluations. No doubt there are concerns here around backward compatibility (especially for non-deterministic functions like NEWID) but even so...
July 19, 2011 at 6:06 pm
SQLkiwi (7/19/2011)
mister.magoo (7/19/2011)
The recursive part of the CTE returns no extra data, but coerces sql into storing the results (it will plop the results in tempdb once there are enough I believe) rather than re-evaluating them - it's a nice little trick that can sometimes help.I've seen Stefan use that before too. IIRC though, you ought not use WHERE 1 = 0 in the recursive part because contradiction detection in QO kicks in and removes the clause completely. If you try that example code with the four variables, you'll see a plan with a Constant Scan and a Compute Scalar - not the usual recursive structure with its stacking index spool and so on. Nevertheless, even with the contradiction in place, the Constant Scan does evaluate the core expression and the Compute Scalar references that twice. This is no guarantee that the core expression won't be evaluated twice - the following 'bug' for example is not fixed in any current build (though it is fixed in Denali CTP3):
Ah yes, of course, I was too quick to post there - I should have had "where result<>result" in the recursive part to force the recursive structure...
It's hard to say whether it would help or not because it would depend a lot on the cost of the calculation - the recursive overhead would need to be less that the duplicated calculation overhead I suppose.
I accept that there are no guarantees as to how it will work - this technique is definitely one that needs to be evaluated for each use case to see if it helps.
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);July 19, 2011 at 6:09 pm
SQLkiwi (7/18/2011)
You can avoid performing the complex calculation more than once:
DECLARE
@a INTEGER = NULL,
@b-2 INTEGER = -351,
@C INTEGER = 45,
@D INTEGER = 999;
SELECT
Calculation.result * ((SIGN(Calculation.result) + 1) / 2)
FROM
(
SELECT TOP (1)
result = COALESCE(@A, @b-2, @C, @D)
ORDER BY result
) AS Calculation;
Do you have a way of doing that for a set rather than (as in your sample) a single bunch of values?
I can't think how you would do that other than in an OUTER APPLY?
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);July 19, 2011 at 6:35 pm
mister.magoo (7/19/2011)
I can't think how you would do that other than in an OUTER APPLY?
We normally use OUTER APPLY, yes. It's not a nice situation, but sometimes (often) necessary.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply