December 10, 2014 at 9:13 am
Hi guys, I have 'adopted' a view written by someone else on my team. The view creates a GROUP BY aggregate, but one of the fields in the query is a text result built using a case statement. The structure of the query is
SELECT
date,
classification,
MAX(
CASE
WHEN X.field = 1 AND Y.otherfield = 1 THEN 'New'
WHEN X.field = 1 AND Y.otherfield = 0 THEN 'Old',
WHEN X.field = 0 AND Y.otherfield = 1 THEN 'Foo'
END
) AS 'Status'
FROM
SomeTable X
JOIN
AnotherTable Y ON X.someField = Y.someField
GROUP BY
date,
classification
Without the MAX, you would need to either include the CASE statement again in the Group BY, or [my preference], use a CTE to generate the logic for the non aggregated result and the SELECT..GROUP BY on the CTE results set.
The CASE statement will exit once one of the conditions is satisfied, so there will only ever be one CASE result per line.
My question is, can the optimizer detect this or is there a performance hit from writing the logic like this. Would the optimizer still be able to detect this if there is more than one table and the X and Y values came from different places.
December 10, 2014 at 10:30 am
Other than to allow for the use of the MAX field, why are you grouping this data anyway? From the example, you're running no other aggregations.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 10, 2014 at 12:53 pm
my bad, that's what I get for psudoing the query
SELECT
date,
classification,
MAX(
CASE
WHEN X.field = 1 AND Y.otherfield = 1 THEN 'New'
WHEN X.field = 1 AND Y.otherfield = 0 THEN 'Old',
WHEN X.field = 0 AND Y.otherfield = 1 THEN 'Foo'
END
) AS 'Status',
SUM(X.money) AS 'TotalMoney'
FROM
SomeTable X
JOIN
AnotherTable Y ON X.someField = Y.someField
GROUP BY
date,
classification
December 10, 2014 at 3:53 pm
OK. That certainly makes more sense now. Yeah, I'd go with a derived table of some kind (doesn't have to be a CTE, but no reason not to) to get the CASE statement folded in as a column that you can then easily add to the GROUP BY.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 10, 2014 at 4:47 pm
Are you aware of this possible combination of values?
WITH SomeTable AS(
SELECT CAST( '20141209' AS DATE) AS date,
'SomeClass' AS classification,
1 AS field,
1 AS somefield
),
AnotherTable AS(
SELECT 1 AS somefield, 1 AS otherfield UNION ALL
SELECT 1 AS somefield, 0 AS otherfield
)
SELECT
date,
classification,
MAX(
CASE
WHEN X.field = 1 AND Y.otherfield = 1 THEN 'New'
WHEN X.field = 1 AND Y.otherfield = 0 THEN 'Old'
WHEN X.field = 0 AND Y.otherfield = 1 THEN 'Foo'
END
) AS 'Status'
FROM
SomeTable X
JOIN
AnotherTable Y ON X.someField = Y.someField
GROUP BY
date,
classification;
It's easy to define priorities.
WITH SomeTable AS(
SELECT CAST( '20141209' AS DATE) AS date,
'SomeClass' AS classification,
1 AS field,
1 AS somefield
),
AnotherTable AS(
SELECT 1 AS somefield, 1 AS otherfield UNION ALL
SELECT 1 AS somefield, 0 AS otherfield
)
SELECT
date,
classification,
STUFF(MAX(
CASE
WHEN X.field = 1 AND Y.otherfield = 1 THEN '3New'
WHEN X.field = 1 AND Y.otherfield = 0 THEN '2Old'
WHEN X.field = 0 AND Y.otherfield = 1 THEN '1Foo'
END
),1,1,'') AS 'Status'
FROM
SomeTable X
JOIN
AnotherTable Y ON X.someField = Y.someField
GROUP BY
date,
classification;
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply