SQL Server Performance Forum – Threads Archive

nasty slow query

Hi, I have spent the past day and a half rewriting a query from using temporary tables to subqueries and a view in order to speed it up. Sadly, this has not improved performance much and im at a bit of a loss what to try next. Index tuning wizard gives no recommendations, and from examining the indices by hand all suitable ones seem to be in place. The query is this <pre> DECLARE @capid int SELECT @capid = 14702 select CASE WHEN MAX(ISNULL(parentSet.opt_Type, -1)) < 0 THEN – if option type is NULL, get the next chronological option type, which maps to same generic code – if more than one option type exists for the next available date, take the highest precedence ISNULL( (SELECT MAX(opt_type) FROM vw_chappynvd InnerNVD INNER JOIN nvddictionaryoptiongenericlink InnerGL ON (InnerNVD.opt_optioncode = InnerGL.OGL_OptionCode) WHERE – for same cap id opt_id = parentSet.capid and – linked to same generic option OGL_GenericCode = parentSet.dg_genericcode and – takes effect before the date of the option which has been discontinued nvpose_effectivefrom <= parentSet.effectiveFrom and – make sure this option in question has not also been discontinued before this date ( select count(X.opt_id) from vw_chappynvd X INNER JOIN nvddictionaryoptiongenericlink NestedGL ON (X.opt_optioncode = NestedGL.OGL_OptionCode) where X.opt_id = InnerNVD.opt_id and X.opt_optioncode = InnerNVD.opt_optioncode and X.opt_type IS NULL and X.nvpose_effectivefrom <= parentSet.effectiveFrom and X.nvpose_effectivefrom >= InnerNVD.nvpose_effectivefrom ) = 0 ) , 0) ELSE MAX(ISNULL(opt_Type, 0)) END, effectiveFrom, DG_Description from ( select "capid"[email protected], dg_genericcode, "EffectiveFrom"=nvpose_effectivefrom, opt_type from nvddictionarygeneric INNER JOIN nvddictionaryoptiongenericlink ON (ogl_genericcode = DG_GenericCode) LEFT JOIN vw_chappynvd ON (opt_optioncode = ogl_optioncode) where opt_id = @capid ———- UNION ———- select "capid"[email protected], "DG_GenericCode"=GS_GenericCode, "EffectiveFrom"=GS_EffectiveFrom, "opt_type"=1 from NvdGenericStatus where GS_ID = @capid and GS_Status = ‘N’) parentSet INNER JOIN NVDDictionaryGeneric B ON (parentSet.DG_GenericCode = B.DG_GenericCode) group by parentSet.capid, parentSet.dg_genericcode, B.dg_Description, effectivefrom order by B.dg_description, ISNULL(effectivefrom, GETDATE()) </pre> where the view is defined as <pre> CREATE VIEW dbo.vw_chappynvd as – STANDARD START select se_id as opt_id, se_effectivefrom as nvpose_effectivefrom, se_optioncode as opt_optioncode, 3 as opt_type from dbo.nvdStandardEquipment UNION – STANDARD END select se_id as opt_id, se_effectiveto as nvpose_effectivefrom, se_optioncode as opt_optioncode, NULL as opt_type from dbo.nvdStandardEquipment where se_effectiveto IS NOT NULL UNION – OPTION START select opt_id, opt_effectivefrom as nvpose_effectivefrom, opt_optioncode, CASE WHEN OPT_Default = 1 THEN 3 ELSE 2 END as opt_type from dbo.nvdOptions UNION – OPTION END select opt_id, opt_effectiveto as nvpose_effectivefrom, opt_optioncode, NULL as opt_type from dbo.nvdOptions where opt_effectiveto IS NOT NULL </pre> This results in the following execution plan, which also matches the estimated one <pre> StmtText —————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————– |–Sort(ORDER BY<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />.[DG_Description] ASC, [Expr1093] ASC)) |–Compute Scalar(DEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Expr1092]=If ([Expr1036]<0) then isnull([Expr1090], 0) else [Expr1037], [Expr1093]=isnull([Union1033], getdate()))) |–Nested Loops(Inner Join, WHERE<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />IsFalseOrNull([Expr1036]<0))OUTER REFERENCES<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Union1031], [Union1032], [Union1033])) |–Stream Aggregate(GROUP BY<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Union1031], [Union1032], [Union1033]) DEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Expr1036]=MAX(isnull([Union1034], -1)), [Expr1037]=MAX(isnull([Union1034], 0)), .[DG_Description]=ANY(.[DG_Description]))) | |–Sort(ORDER BY<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Union1031] ASC, [Union1032] ASC, [Union1033] ASC)) | |–Merge Join(Inner Join, MERGE<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />.[DG_GenericCode])=([Union1032]), RESIDUAL<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Union1032]=.[DG_GenericCode])) | |–Clustered Index Scan(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[CAR].[dbo].[NVDDictionaryGeneric].[PK_NVDDictionary_Generic] AS ), ORDERED FORWARD) | |–Sort(DISTINCT ORDER BY<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Union1032] ASC, [Union1031] ASC, [Union1033] ASC, [Union1034] ASC)) | |–Concatenation | |–Hash Match(Inner Join, HASH<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[NVDDictionaryGeneric].[DG_GenericCode])=([NVDDictionaryOptionGenericLink].[ogl_genericcode])) | | |–Clustered Index Scan(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[CAR].[dbo].[NVDDictionaryGeneric].[PK_NVDDictionary_Generic])) | | |–Nested Loops(Inner Join, OUTER REFERENCES<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Union1026]) WITH PREFETCH) | | |–Sort(DISTINCT ORDER BY<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Union1026] ASC, [Union1024] ASC, [Union1025] ASC, [Union1027] ASC)) | | | |–Concatenation | | | |–Index Seek(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[CAR].[dbo].[NVDStandardEquipment].[IX_NVDStandardEquipment]), SEEK<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[NVDStandardEquipment].[SE_Id]=[@capid]) ORDERED FORWARD) | | | |–Clustered Index Seek(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[CAR].[dbo].[NVDStandardEquipment].[PK_NVDStandardEquipment]), SEEK<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[NVDStandardEquipment].[SE_Id]=[@capid]), WHERE<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[NVDStandardEquipment].[SE_EffectiveTo]<>NULL) ORDERED FORWARD) | | | |–Compute Scalar(DEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Expr1016]=If (Convert([NVDOptions].[OPT_Default])=1) then 3 else 2)) | | | | |–Clustered Index Seek(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[CAR].[dbo].[NVDOptions].[PK_NVDOptions]), SEEK<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[NVDOptions].[OPT_Id]=[@capid]) ORDERED FORWARD) | | | |–Clustered Index Seek(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[CAR].[dbo].[NVDOptions].[PK_NVDOptions]), SEEK<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[NVDOptions].[OPT_Id]=[@capid]), WHERE<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[NVDOptions].[OPT_EffectiveTo]<>NULL) ORDERED FORWARD) | | |–Clustered Index Seek(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[CAR].[dbo].[NVDDictionaryOptionGenericLink].[PK_NVDDictionaryOptionGenericLink]), SEEK<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[NVDDictionaryOptionGenericLink].[ogl_optioncode]=[Union1026]) ORDERED FORWARD) | |–Index Seek(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[CAR].[dbo].[NVDGenericStatus].[idx_id_Status]), SEEK<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[NVDGenericStatus].[GS_Id]=[@capid] AND [NVDGenericStatus].[GS_Status]=’N’) ORDERED FORWARD) |–Stream Aggregate(DEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Expr1090]=MAX([Union1062]))) |–Nested Loops(Inner Join, OUTER REFERENCES<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Union1061])) |–Filter(WHERE<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Expr1088]=0)) | |–Nested Loops(Inner Join, OUTER REFERENCES<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Union1059], [Union1060], [Union1061])) | |–Sort(DISTINCT ORDER BY<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Union1059] ASC, [Union1060] ASC, [Union1061] ASC, [Union1062] ASC)) | | |–Concatenation | | |–Index Seek(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[CAR].[dbo].[NVDStandardEquipment].[IX_NVDStandardEquipment]), SEEK<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[NVDStandardEquipment].[SE_Id]=[Union1031]), WHERE<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[NVDStandardEquipment].[SE_EffectiveFrom]<=[Union1033]) O RDERED FORWARD) | | |–Clustered Index Seek(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[CAR].[dbo].[NVDStandardEquipment].[PK_NVDStandardEquipment]), SEEK<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[NVDStandardEquipment].[SE_Id]=[Union1031]), WHERE<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[NVDStandardEquipment].[SE_EffectiveTo]<=[Union1033] AND [NVDStandardEquipment].[SE_EffectiveTo]<>NULL) ORDERED FORWARD) | | |–Compute Scalar(DEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Expr1051]=If (Convert([NVDOptions].[OPT_Default])=1) then 3 else 2)) | | | |–Clustered Index Seek(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[CAR].[dbo].[NVDOptions].[PK_NVDOptions]), SEEK<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[NVDOptions].[OPT_Id]=[Union1031]), WHERE<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[NVDOptions].[OPT_EffectiveFrom]<=[Union1033]) ORDERED FORWARD) | | |–Clustered Index Seek(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[CAR].[dbo].[NVDOptions].[PK_NVDOptions]), SEEK<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[NVDOptions].[OPT_Id]=[Union1031]), WHERE<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[NVDOptions].[OPT_EffectiveTo]<=[Union1033] AND [NVDOptions].[OPT_EffectiveTo]<>NULL) ORDERED FORWARD) | |–Hash Match(Cache, HASH<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Union1033], [Union1059], [Union1060], [Union1061]), RESIDUAL<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />(([Union1033]=[Union1033] AND [Union1059]=[Union1059]) AND [Union1060]=[Union1060]) AND [Union1061]=[Union1061])) | |–Compute Scalar(DEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Expr1088]=Convert([Expr1109]))) | |–Stream Aggregate(DEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Expr1109]=Count(*))) | |–Nested Loops(Inner Join) | |–Sort(DISTINCT ORDER BY<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Union1084] ASC, [Union1085] ASC, [Union1086] ASC, [Union1087] ASC)) | | |–Concatenation | | |–Filter(WHERE<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />3=NULL)) | | | |–Index Seek(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[CAR].[dbo].[NVDStandardEquipment].[IX_NVDStandardEquipment_1]), SEEK<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[NVDStandardEquipment].[SE_OptionCode]=[Union1061] AND [NVDStandardEquipment].[SE_Id]=[Union1059] AND [NVDStandardEquipment].[SE_EffectiveFrom] >= [Union1060] AND [NVDStandardEquipment].[SE_EffectiveFrom] <= [Union1033]) ORDERED FORWARD) | | |–Filter(WHERE<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />NULL=NULL)) | | | |–Clustered Index Seek(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[CAR].[dbo].[NVDStandardEquipment].[PK_NVDStandardEquipment]), SEEK<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[NVDStandardEquipment].[SE_Id]=[Union1059] AND [NVDStandardEquipment].[SE_OptionCode]=[Union1061]), WHERE<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />([NVDStandardEquipment].[SE_EffectiveTo]>=[Union1060] AND [NVDStandardEquipment].[SE_EffectiveTo]<=[Union1033]) AND [NVDStandardEquipment].[SE_EffectiveTo]<>NULL) ORDERED FORWARD) | | |–Filter(WHERE<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Expr1076]=NULL)) | | | |–Compute Scalar(DEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Expr1076]=If (Convert([NVDOptions].[OPT_Default])=1) then 3 else 2)) | | | |–Clustered Index Seek(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[CAR].[dbo].[NVDOptions].[PK_NVDOptions]), SEEK<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[NVDOptions].[OPT_Id]=[Union1059] AND [NVDOptions].[OPT_OptionCode]=[Union1061] AND [NVDOptions].[OPT_EffectiveFrom] >= [Union1060] AND [NVDOptions].[OPT_EffectiveFrom] <= [Union1033]) ORDERED FORWARD) | | |–Filter(WHERE<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />NULL=NULL)) | | |–Clustered Index Seek(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[CAR].[dbo].[NVDOptions].[PK_NVDOptions]), SEEK<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[NVDOptions].[OPT_Id]=[Union1059] AND [NVDOptions].[OPT_OptionCode]=[Union1061]), WHERE<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />([NVDOptions].[OPT_EffectiveTo]>=[Union1060] AND [NVDOptions].[OPT_EffectiveTo]<=[Union1033]) AND [NVDOptions].[OPT_EffectiveTo]<>NULL) ORDERED FORWARD) | |–Row Count Spool | |–Clustered Index Seek(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[CAR].[dbo].[NVDDictionaryOptionGenericLink].[PK_NVDDictionaryOptionGenericLink] AS [NestedGL]), SEEK<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[NestedGL].[ogl_optioncode]=[Union1061]) ORDERED FORWARD) |–Clustered Index Seek(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[CAR].[dbo].[NVDDictionaryOptionGenericLink].[PK_NVDDictionaryOptionGenericLink] AS [InnerGL]), SEEK<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[InnerGL].[ogl_optioncode]=[Union1061] AND [InnerGL].[ogl_genericcode]=[Union1032]) ORDERED FORWARD) </pre> Now, I dont expect anyone to really understand what im trying to do here, but if anyone can see any obvious areas for improvement based on the execution plan Id love to hear them. Thanks for any time you can spare Chappy

in the view, i am guessing from the apparent logic, that you could use UNION ALL instead of UNION

Can you also get rid of the view, as using the view will make your performance analysis more complex? Is there anyway to work around the NULLs issue? Avoiding using ISNULL is always a good thing? —————————–
Brad M. McGehee, MVP
Webmaster
SQL-Server-Performance.Com

Thanks for the advice guys. Joe, you are right, the union should have been a union all. This shaved a second or so off the query, but still stands at 22 secs. Unfortunately I dont think I can do away with the view, as its used multiple times in the query, and neither the view nor the derived table are suitable for becoming an indexed view. Ill keep plodding on, thanks again

So you now have a union all in both the view and the query? Have you tried to break the query up to see if it is a particular part of the query that is the problem? i.e. split the union into two statements do then run efficiently?
also the statment inside the case statment, how efficient is that by itself?
also how efficient is the who query if you remove the case… (select… and replace it with a constant Cheers
Twan

Okay, still working through all of it, but in one part of the first case statement select you do a subquery summing to zero,rather than that, which has to go through all records in the subquery to get a count to ensure that there have been no discounts, can you do a not exists instead? This will make any records that have results in the subquery perform faster, as it will stop the subquery as soon as the first match is found. Its not much, but I’m starting at the top and working my way down. Chris

]]>