question

Sharma avatar image
Sharma asked

SQL query performance issue on SQL 2016 standalone Enterprise edition

Hello, We have recently migrated database from SQL 2012 to 2016 and encounter very strange query performance issue ,The below CTE inner query is executing in 5 sec and returning the 27 records but when we run this with where condition on CTE "WHERE OrdLen = 14" then it keep executing for long even it is working fast if we use >13 and <15. We are not sure why this is taking long time when using the equal to operator. The column OrdNum on table is nvarchar data type. WITH CTE AS ( SELECT CONVERT(VARCHAR(8), a.pDate, 112) AS ProcessDate , LEFT(c.sort, 2) AS Aisle , a.OrdNum , LEN(a.OrdNum) AS OrdLen FROM ProjectAC a LEFT JOIN Project o ON a.OrdNum = o.OrdNum LEFT JOIN ProjectSC c ON a.Store = c.store WHERE '20170405' = CONVERT(VARCHAR(8), a.pDate, 112) AND o.UserID IS NULL AND LEFT(a.OrdNum, 2) = '99' ) SELECT ProcessDate , Aisle , COUNT(DISTINCT ( OrdNum )) AS Orders FROM CTE WHERE OrdLen = 14 GROUP BY ProcessDate , Aisle;
sql-server-2016query-tuning
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Can you post the actual query plan?
1 Like 1 ·

1 Answer

·
Håkan Winther avatar image
Håkan Winther answered
First of all, You have some potential performance issues even in SQL server 2012 due to the where clause. When you use functions on columns, indexes can't be used as it's not a searchable argument. In your case, you could try to convert the value '20170405' to a date instead of the convert of your column. By doing that you can use an index on the pDate column. Second, did you update the statistics when you upgraded to SQL 2016? When you upgrade a database, you need to update the statistics
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@Sharma If the pDate column is datetime then please try to not use the convert to varchar, but opt for dateadd(day, datediff(day, 0, a.pDate), 0) to drop the time part. The hardcoded literal value of '20170405' is then comparable to the datetime value (with time part dropped) as date, not as varchar. So, the line reading WHERE '20170405' = CONVERT(VARCHAR(8), a.pDate, 112) --may read '20170405' = dateadd(day, datediff(day, 0, a.pDate), 0) If the pDate column does not have the time part (because it is date, not datetime) then straight comparison will work, i.e. **WHERE '20170405' = a.pDate** Also, try changing the line reading **AND LEFT(a.OrdNum, 2) = '99'** to read **AND a.OrdNum like '99%'**, this will make the predicate sargable. Most importantly, in the select inside of the CTE, definitely use the dateadd/datediff instead of converting, this will make the grouping much faster: SELECT dateadd(day, datediff(day, 0, a.pDate), 0) AS ProcessDate This way, the column will be already date (datetime with time part dropped), Optionally, you can use **select cast (a.pDate as date) as ProcessDate**, either way the grouping will be faster.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.