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.

           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 ,
more ▼

asked Apr 19 at 01:26 PM in Default

avatar image

1.3k 88 93 96

Can you post the actual query plan?

Apr 19 at 02:06 PM ThomasRushton ♦♦
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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

more ▼

answered Apr 24 at 06:04 PM

avatar image

Håkan Winther
16.6k 37 45 57

@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.

5 days ago Oleg
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Apr 19 at 01:26 PM

Seen: 55 times

Last Updated: 5 days ago

Copyright 2016 Redgate Software. Privacy Policy