How to tune this query

Question

How to tune this query

Grace09

SSCommitted

Points: 1532
More actions
September 30, 2015 at 12:11 pm

#399119

I have a simple query, Select Max(Substring(nd_num, 12,3) from NDE where datepart(dd, nd_adate)=2. The query plan is attached. How should I tune this query? It comes to the top 3 queries that consume the most CPU. Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286573 More actions · Answer 1

Grace09 (9/30/2015)
I have a simple query, Select Max(Substring(nd_num, 12,3) from NDE where datepart(dd, nd_adate)=2. The query plan is attached. How should I tune this query? It comes to the top 3 queries that consume the most CPU. Thanks

Well...the google drive you shared does allow anybody permission to view the file so we can't see that part.

The only query you posted is nonSARGable because you have wrapped the column nd_adate in a function. That means it has to evaluate each and every row in the table to determine if it belongs in the resultset or not.

Please take a few minutes and read the first link signature for best practices when posting questions.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Grace09 SSCommitted Points: 1532 More actions · Answer 2

I modified the link to make it viewable. Hopefully you can see it now. Thanks.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Please post the table and index definitions and the actual execution plan, saved as a .sqlplan file. A picture of the query plan is not very useful.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

Grace09 (9/30/2015)
I have a simple query, Select Max(Substring(nd_num, 12,3) from NDE where datepart(dd, nd_adate)=2. The query plan is attached. How should I tune this query? It comes to the top 3 queries that consume the most CPU. Thanks

Fetch the min and max nd_adate from table NDE.

Calculate the number of months between the two dates.

Calculate the second of the month for the min nd_adate

Using this information, construct a lookup table of dates between the min and max nd_adate which contains only the second of each month.

Join your NDE table to this table.

This is how you create the lookup table. I've output extra columns so you can analyse how it works:

SELECT

FirstMonth,

MAXDate,

MonthCount,

n,

DateToMatch = DATEADD(MONTH,n,FirstMonth)

FROM (SELECT MINDate = MIN(nd_adate), MAXDate = MAX(nd_adate) FROM NDE) d

CROSS APPLY (

SELECT MonthCount = DATEDIFF(MONTH,MINDate, MAXDate), FirstMonth = DATEADD(MONTH,DATEDIFF(MONTH,0,MINDate),1)

) x

CROSS APPLY (

SELECT TOP (monthcount+1) n = ROW_NUMBER() OVER(ORDER BY (SELECT NULL))-1

FROM

(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) tRows (n),

(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) hRows (n),

(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) kRows (n)

) y

You could run the results of this into a #temp table, use it as a CTE, or incorporate it into your query as a derived table. This is how to configure it as a CTE:

;WITH DateLookup AS (

SELECT

DateToMatch = DATEADD(MONTH,n,FirstMonth)

FROM (SELECT MINDate = MIN(nd_adate), MAXDate = MAX(nd_adate) FROM NDE) d

CROSS APPLY (

SELECT MonthCount = DATEDIFF(MONTH,MINDate, MAXDate), FirstMonth = DATEADD(MONTH,DATEDIFF(MONTH,0,MINDate),1)

) x

CROSS APPLY (

SELECT TOP (monthcount+1) n = ROW_NUMBER() OVER(ORDER BY (SELECT NULL))-1

FROM

(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) tRows (n),

(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) hRows (n),

(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) kRows (n)

) y

)

SELECT MAX(SUBSTRING(NDE.nd_num, 12,3))

FROM NDE

INNER JOIN DateLookup r

ON r.DateToMatch = NDE.nd_adate

For optimum performance, you could try an index on the date column nd_adate and INCLUDE column nd_num.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden