March 22, 2011 at 2:22 pm
I'm looking to help tune a query from a developer that is using left outer joins with a "AND column IS NOT NULL" and some other things (date functions) in the where clause and string function in the group by and wondering if these can cause the optimizer to do an index scan instead of an index seek when I have created covering indexes on all tables? Is there a different way to write this that would be more efficient?
Example is:
SELECT (SELECT CONVERT(varchar(10), MAX(ACCOUNTING_DATE), 101) AS Expr1
FROM DB1.dbo.Table2
WHERE ACCOUNTING_DATE <= GETDATE()) AS AsOfDate,
RTRIM(a.column2) AS column2,
RTRIM(a.column3) AS column3,
SUM(ROUND(curr.column4, 2)) AS column4,
SUM(ROUND(prev_year.column4, 2)) AS column5,
SUM(ROUND(prev_year_minus1.column4, 2)) AS column6,
SUM(ROUND(prev_year_minus2.column4, 2)) AS column7,
SUM(ROUND(prev_year_minus3.column4, 2)) AS column8,
SUM(ROUND(c.column5, 2)) AS column5
FROMDB1.dbo.Table1 AS a
LEFT OUTER JOIN DB1.dbo.Table2 AS c ON a.column1 = c.column1 AND c.[YEAR] = DATEPART(yy, GETDATE()) AND c.column5 IS NOT NULL
LEFT OUTER JOIN DB1.dbo.Table3 AS curr ON a.column1 = curr.column1 AND curr.[YEAR] = DATEPART(yy, GETDATE()) AND curr.column4 IS NOT NULL
LEFT OUTER JOIN DB1.dbo.Table3 AS prev_year ON a.column1 = prev_year.column1 AND prev_year.[YEAR] = DATEPART(yy, DATEADD(yy, - 1, GETDATE())) AND prev_year.column4 IS NOT NULL
LEFT OUTER JOIN DB1.dbo.Table3 AS prev_year_minus1 ON a.column1 = prev_year_minus1.column1 AND prev_year_minus1.[YEAR] = DATEPART(yy, DATEADD(yy, - 2, GETDATE())) AND prev_year_minus1.column4 IS NOT NULL
LEFT OUTER JOIN DB1.dbo.Table3 AS prev_year_minus2 ON a.column1 = prev_year_minus2.column1 AND prev_year_minus2.[YEAR] = DATEPART(yy, DATEADD(yy, - 3, GETDATE())) AND prev_year_minus2.column4 IS NOT NULL
LEFT OUTER JOIN DB1.dbo.Table3 AS prev_year_minus3 ON a.column1 = prev_year_minus3.column1 AND prev_year_minus3.[YEAR] = DATEPART(yy, DATEADD(yy, - 4, GETDATE())) AND prev_year_minus3.column4 IS NOT NULL
GROUP BY RTRIM(a.column2), RTRIM(a.column3)
Owner & Principal SQL Server Consultant
Im Your DBA, Inc.
https://www.imyourdba.com/
March 22, 2011 at 2:29 pm
I would start by putting all that date math into declared variables.
Same for the AsOfDate value.
March 22, 2011 at 2:32 pm
Check out this 4-part article[/url] by MVP Gail Shaw. (Read the other parts as well - VERY useful information!)
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
March 23, 2011 at 9:01 am
WayneS (3/22/2011)
Check out this 4-part article[/url] by MVP Gail Shaw. (Read the other parts as well - VERY useful information!)
Except this query isn't doing an existence check....
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 23, 2011 at 10:04 am
GilaMonster (3/23/2011)
WayneS (3/22/2011)
Check out this 4-part article[/url] by MVP Gail Shaw. (Read the other parts as well - VERY useful information!)Except this query isn't doing an existence check....
I don't want anyone to think that I'm just looking for someone to rewrite the query for me...I'm just looking to be pointed in the right direction so I can learn the method. If you have any suggestions, they will be greatly appreciated.
Owner & Principal SQL Server Consultant
Im Your DBA, Inc.
https://www.imyourdba.com/
March 23, 2011 at 11:13 am
SQLJocky (3/23/2011)
@PamI agree, that dropped the processing time in half. Thanks for the suggestion. 😀
Please post how the code you applied Pam's suggestion to so we can see what was done. Thanks.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 23, 2011 at 11:37 am
DECLARE @CompDtvarchar(2),
@CompDtm1varchar(2),
@CompDtm2varchar(2),
@CompDtm3varchar(2),
@CompDtm4varchar(2)
SET@CompDt = DATEPART(yy, GETDATE())
SET@CompDtm1 = DATEPART(yy, DATEADD(yy,-1,GETDATE()))
SET@CompDtm2 = DATEPART(yy, DATEADD(yy,-2,GETDATE()))
SET@CompDtm3 = DATEPART(yy, DATEADD(yy,-3,GETDATE()))
SET@CompDtm4 = DATEPART(yy, DATEADD(yy,-4,GETDATE()))
SELECT (SELECT CONVERT(varchar(10), MAX(ACCOUNTING_DATE), 101) AS Expr1
FROM DB1.dbo.Table2
WHERE ACCOUNTING_DATE <= GETDATE()) AS AsOfDate,
RTRIM(a.column2) AS column2,
RTRIM(a.column3) AS column3,
SUM(ROUND(curr.column4, 2)) AS column4,
SUM(ROUND(prev_year.column4, 2)) AS column5,
SUM(ROUND(prev_year_minus1.column4, 2)) AS column6,
SUM(ROUND(prev_year_minus2.column4, 2)) AS column7,
SUM(ROUND(prev_year_minus3.column4, 2)) AS column8,
SUM(ROUND(c.column5, 2)) AS column5
FROM DB1.dbo.Table1 AS a
LEFT OUTER JOIN DB1.dbo.Table2 AS c ON a.column1 = c.column1 AND c.[YEAR] = @CompDt AND c.column5 IS NOT NULL
LEFT OUTER JOIN DB1.dbo.Table3 AS curr ON a.column1 = curr.column1 AND curr.[YEAR] = @CompDt AND curr.column4 IS NOT NULL
LEFT OUTER JOIN DB1.dbo.Table3 AS prev_year ON a.column1 = prev_year.column1 AND prev_year.[YEAR] = @CompDtm1 AND prev_year.column4 IS NOT NULL
LEFT OUTER JOIN DB1.dbo.Table3 AS prev_year_minus1 ON a.column1 = prev_year_minus1.column1 AND prev_year_minus1.[YEAR] = @CompDtm2 AND prev_year_minus1.column4 IS NOT NULL
LEFT OUTER JOIN DB1.dbo.Table3 AS prev_year_minus2 ON a.column1 = prev_year_minus2.column1 AND prev_year_minus2.[YEAR] = @CompDtm3 AND prev_year_minus2.column4 IS NOT NULL
LEFT OUTER JOIN DB1.dbo.Table3 AS prev_year_minus3 ON a.column1 = prev_year_minus3.column1 AND prev_year_minus3.[YEAR] = @CompDtm4 AND prev_year_minus3.column4 IS NOT NULL
GROUP BY RTRIM(a.column2), RTRIM(a.column3)
Owner & Principal SQL Server Consultant
Im Your DBA, Inc.
https://www.imyourdba.com/
March 23, 2011 at 2:16 pm
OOOOOOPS!
Typing too fast and didn't realize the declared variables should be 'INT' not 'VARCHAR'. No wonder the data was all NULL. 🙂
Changed that and it still ran twice as fast as the original but with data this time.
Owner & Principal SQL Server Consultant
Im Your DBA, Inc.
https://www.imyourdba.com/
March 23, 2011 at 2:22 pm
One thing that may help - drop the RTRIM off of the group by columns. SQL ignores trailing spaces anyway, and the function may be preventing SQL from leveraging a useful index.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 23, 2011 at 2:41 pm
You might run this first:
(SELECT CONVERT(varchar(10), MAX(ACCOUNTING_DATE), 101) AS Expr1
FROM DB1.dbo.Table2
WHERE ACCOUNTING_DATE <= GETDATE())
And drop that to a variable since you're not using any row-level interaction.
You've gone through a lot of trouble to remove table names and most of the column names, so I'm not sure if you're willing, but an execution plan here would help us focus in on what remains as an issue.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply