January 9, 2017 at 10:09 am
Guys, I have the following CASE statement which I would like to replace with a table because
A) It is a PITA to maintain it in the stored procedures / views and
B) I think it might be killing the execution plan!
CASE
WHEN AssetType_ID = 1 THEN 8
WHEN TenancyType.[TenureTypeCode]='2001 Tenancy Agreement ' AND ServiceCount.NoOfService IS NULL THEN 1
WHEN TenancyType.[TenureTypeCode]='2001 Tenancy Agreement ' AND ServiceCount.NoOfService >= 1 THEN 2
WHEN TenancyType.[TenureTypeCode]='Assured 2015' AND ServiceCount.NoOfService IS NULL THEN 3
WHEN TenancyType.[TenureTypeCode]='Assured 2015' AND ServiceCount.NoOfService >= 1 THEN 4
WHEN TenancyType.[TenureTypeCode]='Assured Shorthold' AND ServiceCount.NoOfService IS NULL THEN 5
WHEN TenancyType.[TenureTypeCode]='Assured Shorthold (3 years)' AND ServiceCount.NoOfService IS NULL THEN 6
WHEN TenancyType.[TenureTypeCode]='Assured Shorthold Monthly ' THEN 7
WHEN TenancyType.[TenureTypeCode]='Garage Account ' THEN 8
WHEN TenancyType.[TenureTypeCode]='Leaseholder' THEN 9
WHEN TenancyType.[TenureTypeCode]='1997 Tenancy Agreement ' AND P.Asset_Type_ID = 3 AND P.Property_Type_ID = 1600 THEN 12-- change order so the it will pick this ID and not need extra logic
WHEN TenancyType.[TenureTypeCode]='1997 Tenancy Agreement ' AND ServiceCount.NoOfService IS NULL THEN 10
WHEN TenancyType.[TenureTypeCode]='1997 Tenancy Agreement ' AND ServiceCount.NoOfService >= 1 THEN 11
WHEN TenancyType.[TenureTypeCode]='Pre 1997 Tenancy Agreement' AND P.Asset_Type_ID = 3 AND P.Property_Type_ID = 1600 THEN 15-- change order so the it will pick this ID and not need extra logic
WHEN TenancyType.[TenureTypeCode]='Pre 1997 Tenancy Agreement' AND ServiceCount.NoOfService IS NULL THEN 13
WHEN TenancyType.[TenureTypeCode]='Pre 1997 Tenancy Agreement' AND ServiceCount.NoOfService >= 1 THEN 14
WHEN TenancyType.[TenureTypeCode]='Shared Ownership - Check' THEN 16
WHEN TenancyType.[TenureTypeCode]='Short Stay Accommodation' THEN 17
WHEN TenancyType.[TenureTypeCode]='Starter Tenancy ' AND ServiceCount.NoOfService IS NULL THEN 18
WHEN TenancyType.[TenureTypeCode]='Starter Tenancy ' AND ServiceCount.NoOfService >= 1 THEN 19
WHEN TenancyType.[TenureTypeCode]='Starter 2015' AND ServiceCount.NoOfService IS NULL THEN 20
WHEN TenancyType.[TenureTypeCode]='Starter 2015' AND ServiceCount.NoOfService >= 1 THEN 21
END AS NewLetterTypesCode
I am suffering brain fade - basically with the CASE statement, when a condition is met then the CASE will break out so the value returned is based on the sequence of the case statements moving from explicit to generic (i.e. the exceptions appear first then the generalizations)
The rules use the following elements, all of which come from different tables
Tenancy.TenureTypeCode - everything will have one of these
ServiceCount.NoOfService - anything from NULL to 30 but discriminator is null/not null [This is a derived table so the return values could be changed to True/False or 0/1]
Asset.Asset_Type_ID - everything will have one of these
Asset.Property_Type_ID - everything will have one of these
The only way I can see to make this work is to replace the CASE statement with a RULES table with a sequence ID, join the rules table to the three source tables using ISNULL()s on the joins to eliminate the elements that are not required for the rule (and ruin the SARGability), which will potentially result in more than one rule being applied and then filter the results on MIN(ruleSequence) which means a massive GROUP BY clause and I am not sure it is going to be any faster.
Can anyone suggest a different solution - Would a function be faster/easier to manage?
January 9, 2017 at 10:27 am
How about making this a persisted computed column using a UDF?
January 9, 2017 at 12:06 pm
I'd suggest an inline-table-valued-function. That will give you performance, reusability and flexibility.
Although I can't see that CASE statement ever affecting the query plan, unless the result of the CASE is used in another table lookup.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
January 9, 2017 at 12:50 pm
We really need to see your full SELECT statement to know how to optimize this although you mention your problem is mostly in maintaining this "PITA" in several procs and views.
I think the CASE statement is efficient as it currently is since it implements a sequential testing as you require. To have that code in one place, it would need to be rewritten as a function with the four columns passed in as arguments and returning the calculated number. This function would be called per row...which is probably fine if it is not called before filtering rows.
I don't think you can use a table approach since you don't know all possible values for the four columns used in the calculation. Otherwise you could get an exact match on a row and avoid doing a GROUP BY and MIN() approach.
January 10, 2017 at 1:43 am
Thanks guys, I din't post the entire query because it is currently over 800 lines long and uses a number of correlated sub-queries (converting to CTEs to make it easier to read, test and edit!) The other huge bottleneck is that the tables that are being joined to actually reside on two different linked servers along with calculated dates so no decent execution plan. I am trying to make maintenance easier first than look at performance as the query is only needed for a once a year process.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply