February 19, 2015 at 4:00 am
CASE [Country] ---Mod # 2 Start
WHEN 'USA' THEN ''
WHEN 'US' THEN ''
WHEN 'CAN' THEN CASE [State] -- Nasted Case
WHEN 'AB' THEN 'CA-AB'
WHEN 'BC' THEN 'CA-BC'
ELSE ''
END
WHEN 'CA' THEN CASE [State] -- Nasted Case
WHEN 'AB' THEN 'CA-AB'
WHEN 'BC' THEN 'CA-BC'
WHEN 'CD' THEN 'CA-CD'
ELSE ''
END
February 19, 2015 at 4:24 am
I suppose it's not the CASE statement per se that is taking time, but the context it is used on. Is it in a SELECT list or is it a filter/join predicate? Can you post the whole query?
While you're at it, can you also post table definition (CREATE TABLE statements)?
-- Gianluca Sartori
February 19, 2015 at 4:50 am
This nested Case is in Select list
February 19, 2015 at 5:35 am
You're not using this in any other place in the query? I set up a test:
SELECT CASE cr.CountryRegionCode ---Mod # 2 Start
WHEN 'USA' THEN ''
WHEN 'US' THEN ''
WHEN 'CAN' THEN CASE sp.StateProvinceCode -- Nasted Case
WHEN 'AB' THEN 'CA-AB'
WHEN 'BC' THEN 'CA-BC'
ELSE ''
END
WHEN 'CA' THEN CASE sp.StateProvinceCode -- Nasted Case
WHEN 'AB' THEN 'CA-AB'
WHEN 'BC' THEN 'CA-BC'
WHEN 'CD' THEN 'CA-CD'
ELSE ''
END
END,
a.AddressLine1
FROM Person.Address AS a
JOIN Person.StateProvince AS sp
ON sp.StateProvinceID = a.StateProvinceID
JOIN Person.CountryRegion AS cr
ON cr.CountryRegionCode = sp.CountryRegionCode
WHERE a.AddressLine1 LIKE '1%';
I'm getting a clean index seek on the Address table and the function didn't affect performance. But, it can depend on where the Compute Scalar operation occurs within the query plan. I'd suggest checking there and seeing what's happening. This function, while a little odd, shouldn't, by itself, hurt performance too much. It's possible that the function is occurring prior to some additional filtering in your query as opposed to the end of the process in mine. But it's more likely that if this is for sure the performance bottleneck, you're using it in the ORDER BY statement or something, possibly leading to table scans.
"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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply