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;
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
@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.
17 People are following this question.
