asked on

Query slowed down after migration from SQL 2000 to 2016

Upgrading an old e-commerce website from SQL 2000 to SQL 2008 (a necessary intermediary step) and finally to SQL 2016 a query on a search page has significantly slowed down. There is one search criteria text object for the users to enter their search data. Multiple tables/columns are searched based on the search criteria. If I eliminate the final OR statement in the where clause the query takes 1-2 seconds. Leaving the final OR statement adds 20 - 30 seconds. Any suggestions on how to speed this up. I have checked the SQL 2000 tables and there are no indexes, so I'm not sure why this worked faster in SQL 2000.

Final OR statement mentioned:
OR (select TOP 1 CONVERT(varchar(500),cha.Answer) from WebCheckoutAnswers as cha, WebCheckoutQuestions as chq
      where cha.keyWebCheckoutQuestions = chq.keyWebCheckoutQuestions
      and cha.keyWebOrder = convert(varchar(256),o.KeyWebOrder)
      and chq.WebOrderColumnName like 'ShowName') LIKE '%12281%')

SELECT top 15 o.KeyWebOrder AS id,
convert(varchar(256),KeyWebOrder) AS KeyWebOrder,
OrderNumber AS OrderNumber,
DateCreated AS DateCreated,
convert(varchar(256),keyWebStatus) AS keyWebStatus,
LTRIM(RTRIM((SELECT TOP 1 CONVERT(varchar(500),Answer) FROM WebCustomerAnswers as cha, WebCustomerQuestions as chq
      WHERE cha.keyWebCustomerQuestions=chq.keyWebCustomerQuestions and cha.keyWebOrder=o.KeyWebOrder and cha.OrderType=1
      and chq.WebOrderColumnName like 'BillingFirstName')) ) +' '+
      LTRIM(RTRIM((SELECT TOP 1 CONVERT(varchar(500),Answer) FROM WebCustomerAnswers as cha, WebCustomerQuestions as chq
      WHERE cha.keyWebCustomerQuestions=chq.keyWebCustomerQuestions and cha.keyWebOrder=o.KeyWebOrder and cha.OrderType=1
      and chq.WebOrderColumnName like 'BillingLastName')) ) AS Ordered_By,
d.LeftMenuHeader AS Subdivision,
(select TOP 1 CONVERT(varchar(500),cha.Answer) from WebCheckoutAnswers as cha,
WebCheckoutQuestions as chq where cha.keyWebCheckoutQuestions = chq.keyWebCheckoutQuestions
      and cha.keyWebOrder = convert(varchar(256),o.KeyWebOrder) and chq.WebOrderColumnName like 'ShowName') AS ShowName,
(SELECT TOP 1 CONVERT(varchar(500),Answer) FROM WebCustomerQuestions as q, WebCustomerAnswers as a
      WHERE q.keyWebCustomerQuestions=a.keyWebCustomerQuestions and a.KeyWebOrder = o.KeyWebOrder and q.WebOrderColumnName like 'ShippingCity') AS Location
FROM WebOrder o
LEFT JOIN WebSubDomains d ON (o.keyWebSubDomains=d.keyWebSubDomains)
WHERE (d.keyWebSubDomains=convert(uniqueidentifier,'8BAB3BAB-A22F-48A9-8ADF-CCC2ED70FBF7')
AND keyWebStatus!=convert(uniqueidentifier,'00000000-0000-0000-0000-000000000000')
AND keyWebPrimaryDomains=convert(uniqueidentifier, '556F21C6-D87F-404B-B42A-797B2870929E'))
AND (o.KeyWebOrder LIKE '%kansas%' OR convert(varchar(256),KeyWebOrder) LIKE '%kansas%'
OR OrderNumber LIKE '%kansas%'
OR DateCreated LIKE '%kansas%'
OR convert(varchar(256),keyWebStatus) LIKE '%kansas%'
OR LTRIM(RTRIM((SELECT TOP 1 CONVERT(varchar(500),Answer) FROM WebCustomerAnswers as cha, WebCustomerQuestions as chq
      WHERE cha.keyWebCustomerQuestions=chq.keyWebCustomerQuestions and cha.keyWebOrder=o.KeyWebOrder and cha.OrderType=1
      and chq.WebOrderColumnName like 'BillingFirstName')) ) +' '+
      LTRIM(RTRIM((SELECT TOP 1 CONVERT(varchar(500),Answer) FROM WebCustomerAnswers as cha, WebCustomerQuestions as chq
      WHERE cha.keyWebCustomerQuestions=chq.keyWebCustomerQuestions and cha.keyWebOrder=o.KeyWebOrder and cha.OrderType=1
      and chq.WebOrderColumnName like 'BillingLastName')) ) LIKE '%kansas%'
OR d.LeftMenuHeader LIKE '%kansas%'
OR (SELECT TOP 1 CONVERT(varchar(500),Answer) FROM WebCustomerAnswers as a, WebCustomerQuestions as q
      WHERE q.keyWebCustomerQuestions=a.keyWebCustomerQuestions and a.KeyWebOrder = o.KeyWebOrder AND a.OrderType=2
      and q.WebOrderColumnName like 'ShippingCity') LIKE '%kansas%'
OR (select TOP 1 CONVERT(varchar(500),cha.Answer) from WebCheckoutAnswers as cha, WebCheckoutQuestions as chq
      where cha.keyWebCheckoutQuestions = chq.keyWebCheckoutQuestions
      and cha.keyWebOrder = convert(varchar(256),o.KeyWebOrder)
      and chq.WebOrderColumnName like 'ShowName') LIKE '%12281%')
ORDER BY OrderNumber

Raja Jegan R

After any DB Server Migration activities, it is recommended to rebuild all the indexes on your tables.
If not, then try updating the statistics once to see whether it helps or not.

To clarify you, after any DB Migration, tables can get fragmented causing it to generate poor execution plans and hence rebuilding the indexes can help create optimal execution plans.

>> I have checked the SQL 2000 tables and there are no indexes, so I'm not sure why this worked faster in SQL 2000.

If you don't have any indexes, then try updating the statistics once which can help create better execution plans..

SOLUTION

ste5an

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

woodwyn

ASKER

Hello. I am wondering if we can simplify and/or narrow down the suggestions some. The SQL migration is complete. If I remove this final segment of the where clause the query takes 1-2 seconds, but if I leave it in there the query takes 29 seconds. Is there something specific that stands out how why this may be?

OR (select TOP 1 CONVERT(varchar(500),cha.Answer) from WebCheckoutAnswers as cha, WebCheckoutQuestions as chq    
       where cha.keyWebCheckoutQuestions = chq.keyWebCheckoutQuestions    
       and cha.keyWebOrder = convert(varchar(256),o.KeyWebOrder)
       and chq.WebOrderColumnName like 'ShowName') LIKE '%12281%')

Open in new window

If I simplify this there is no effect. It still increases the query time to 29 seconds.

$s1 = "(select cha.Answer
	from WebCheckoutAnswers as cha, WebCheckoutQuestions as chq 
	where cha.keyWebCheckoutQuestions = chq.keyWebCheckoutQuestions 
	and cha.keyWebOrder = o.KeyWebOrder
	and chq.WebOrderColumnName like 'ShowName')
      ";

Open in new window

Raja Jegan R

Can you kindly post your finalized query along with execution plan to check it out once..

woodwyn

ASKER

SELECT  top 15 o.KeyWebOrder AS id, 
 convert(varchar(256),KeyWebOrder) AS KeyWebOrder, 
 OrderNumber AS OrderNumber, 
 DateCreated AS DateCreated, 
 convert(varchar(256),keyWebStatus) AS keyWebStatus, 
 LTRIM(RTRIM((SELECT TOP 1 CONVERT(varchar(500),Answer) FROM WebCustomerAnswers as cha, WebCustomerQuestions as chq              
       WHERE cha.keyWebCustomerQuestions=chq.keyWebCustomerQuestions and cha.keyWebOrder=o.KeyWebOrder and cha.OrderType=1              
       and chq.WebOrderColumnName like 'BillingFirstName')) )            +' '+           
       LTRIM(RTRIM((SELECT TOP 1 CONVERT(varchar(500),Answer) FROM WebCustomerAnswers as cha, WebCustomerQuestions as chq              
       WHERE cha.keyWebCustomerQuestions=chq.keyWebCustomerQuestions and cha.keyWebOrder=o.KeyWebOrder and cha.OrderType=1              
       and chq.WebOrderColumnName like 'BillingLastName')) ) AS Ordered_By, 
 d.LeftMenuHeader AS Subdivision, 
 (select TOP 1 CONVERT(varchar(500),cha.Answer)   from WebCheckoutAnswers as cha, 
 WebCheckoutQuestions as chq    where cha.keyWebCheckoutQuestions = chq.keyWebCheckoutQuestions    
       and cha.keyWebOrder = convert(varchar(256),o.KeyWebOrder)    and chq.WebOrderColumnName like 'ShowName') AS ShowName, 
 (SELECT TOP 1 CONVERT(varchar(500),Answer) FROM WebCustomerQuestions as q, WebCustomerAnswers as a           
       WHERE q.keyWebCustomerQuestions=a.keyWebCustomerQuestions and a.KeyWebOrder = o.KeyWebOrder and q.WebOrderColumnName like 'ShippingCity') AS Location 
 FROM WebOrder o 
 LEFT JOIN WebSubDomains d ON (o.keyWebSubDomains=d.keyWebSubDomains) 
 WHERE (d.keyWebSubDomains=convert(uniqueidentifier,'8BAB3BAB-A22F-48A9-8ADF-CCC2ED70FBF7') 
 AND keyWebStatus!=convert(uniqueidentifier,'00000000-0000-0000-0000-000000000000') 
 AND keyWebPrimaryDomains=convert(uniqueidentifier, '556F21C6-D87F-404B-B42A-797B2870929E')) 
 AND (o.KeyWebOrder LIKE '%kansas%' OR convert(varchar(256),KeyWebOrder) LIKE '%kansas%'
OR OrderNumber LIKE '%kansas%' 
 OR DateCreated LIKE '%kansas%' 
 OR convert(varchar(256),keyWebStatus) LIKE '%kansas%'
OR LTRIM(RTRIM((SELECT TOP 1 CONVERT(varchar(500),Answer) FROM WebCustomerAnswers as cha, WebCustomerQuestions as chq              
       WHERE cha.keyWebCustomerQuestions=chq.keyWebCustomerQuestions and cha.keyWebOrder=o.KeyWebOrder and cha.OrderType=1              
       and chq.WebOrderColumnName like 'BillingFirstName')) )  +' '+           
       LTRIM(RTRIM((SELECT TOP 1 CONVERT(varchar(500),Answer) FROM WebCustomerAnswers as cha, WebCustomerQuestions as chq              
       WHERE cha.keyWebCustomerQuestions=chq.keyWebCustomerQuestions and cha.keyWebOrder=o.KeyWebOrder and cha.OrderType=1              
       and chq.WebOrderColumnName like 'BillingLastName')) ) LIKE '%kansas%'
OR d.LeftMenuHeader LIKE '%kansas%'
OR (SELECT TOP 1 CONVERT(varchar(500),Answer) FROM WebCustomerAnswers as a, WebCustomerQuestions as q
       WHERE q.keyWebCustomerQuestions=a.keyWebCustomerQuestions and a.KeyWebOrder = o.KeyWebOrder AND a.OrderType=2 
       and q.WebOrderColumnName like 'ShippingCity') LIKE '%kansas%'
OR (select TOP 1 CONVERT(varchar(500),cha.Answer) from WebCheckoutAnswers as cha, WebCheckoutQuestions as chq    
       where cha.keyWebCheckoutQuestions = chq.keyWebCheckoutQuestions    
       and cha.keyWebOrder = convert(varchar(256),o.KeyWebOrder)
       and chq.WebOrderColumnName like 'ShowName') LIKE '%kansas%')
ORDER BY OrderNumber

Open in new window

WebOrderExecutionPlan.sqlplan

ste5an

The SQL migration is complete.

I wasn't kidding. SQL Server has changed over the years drastically. And your SQL is suboptimal. CONVERT's are in many cases disturbing the row estimation process. EXISTS() is faster than your TOP 1 approach. And last but not least, create indices. Just because there where none in the past, means that you don't have to create them. And you don't have even clustered indices.

To be honest: If "I'm done" is your point, then there cannot be done anything.

Raja Jegan R

I'm not sure why your query was written in a so complicated way but I've spent more than half an hour to rewrite for much clarity as below:
FYI, I've commented few OR conditions which was currently available in your query and doesn't makes sense.
Kindly check the performance of this query and send the execution plan of this one for further troubleshooting.

; with weborder as (
SELECT o.KeyWebOrder AS id
	,convert(VARCHAR(256), o.KeyWebOrder) AS KeyWebOrder
	,OrderNumber
	,DateCreated
	,convert(VARCHAR(256), keyWebStatus) AS keyWebStatus
	,d.LeftMenuHeader AS Subdivision
FROM WebOrder o
LEFT JOIN WebSubDomains d ON o.keyWebSubDomains = d.keyWebSubDomains
WHERE d.keyWebSubDomains = convert(UNIQUEIDENTIFIER, '8BAB3BAB-A22F-48A9-8ADF-CCC2ED70FBF7')
AND keyWebStatus != convert(UNIQUEIDENTIFIER, '00000000-0000-0000-0000-000000000000')
AND keyWebPrimaryDomains = convert(UNIQUEIDENTIFIER, '556F21C6-D87F-404B-B42A-797B2870929E')
AND (o.KeyWebOrder LIKE '%kansas%'
OR OrderNumber LIKE '%kansas%'
--OR DateCreated LIKE '%kansas%'
--OR convert(VARCHAR(256), keyWebStatus) LIKE '%kansas%'
OR d.LeftMenuHeader LIKE '%kansas%')
),
customer as (
SELECT cha.KeyWebOrder
, MAX(CASE WHEN cha.OrderType = 1 AND chq.WebOrderColumnName LIKE 'BillingFirstName' THEN Answer END) BillingFirstName
, MAX(CASE WHEN cha.OrderType = 1 AND chq.WebOrderColumnName LIKE 'BillingLastName' THEN Answer END) BillingLastName
, MAX(CASE WHEN cha.OrderType = 2 AND chq.WebOrderColumnName LIKE 'ShippingCity' THEN Answer END) Location
FROM WebCustomerAnswers AS cha
JOIN WebCustomerQuestions AS chq ON cha.keyWebCheckoutQuestions = chq.keyWebCheckoutQuestions
GROUP BY o.KeyWebOrder
),
checkout as (
SELECT cha.keyWebOrder, cha.Answer ShowName
FROM WebCheckoutAnswers AS cha
JOIN WebCheckoutQuestions AS chq ON cha.keyWebCustomerQuestions = chq.keyWebCustomerQuestions
WHERE chq.WebOrderColumnName LIKE 'ShowName'
)
SELECT TOP 15 id
	,wo.KeyWebOrder
	,OrderNumber
	,DateCreated
	,keyWebStatus
	,ISNULL(BillingFirstName,'') + ' ' + ISNULL(BillingLastName, '') Ordered_by
	,Subdivision
	,ShowName
	,Location
FROM weborder wo
LEFT JOIN customer cu on wo.KeyWebOrder = cu.KeyWebOrder
LEFT JOIN checkout co on wo.KeyWebOrder = co.KeyWebOrder
WHERE BillingFirstName LIKE '%kansas%'
OR BillingLastName LIKE '%kansas%'
OR Location LIKE '%kansas%'
OR ShowName LIKE '%kansas%'
ORDER BY OrderNumber

Open in new window

woodwyn

ASKER

Raja - I get this error:

Msg 252, Level 16, State 1, Line 1
Recursive common table expression 'weborder' does not contain a top-level UNION ALL operator.

woodwyn

ASKER

See the corrections I made to your query. It's almost there. I am now getting a different error. I'm not familiar with a solution to this error.
Msg 8117, Level 16, State 1, Line 21
Operand data type text is invalid for max operator.

 ; WITH Test AS  -- CHANGED WebOrder to Test
(SELECT o.KeyWebOrder AS id
	,convert(VARCHAR(256), o.KeyWebOrder) AS KeyWebOrder
	,OrderNumber
	,DateCreated
	,convert(VARCHAR(256), keyWebStatus) AS keyWebStatus
	,d.LeftMenuHeader AS Subdivision
FROM WebOrder o
LEFT JOIN WebSubDomains d ON o.keyWebSubDomains = d.keyWebSubDomains
WHERE d.keyWebSubDomains = convert(UNIQUEIDENTIFIER, '8BAB3BAB-A22F-48A9-8ADF-CCC2ED70FBF7')
AND keyWebStatus != convert(UNIQUEIDENTIFIER, '00000000-0000-0000-0000-000000000000')
AND keyWebPrimaryDomains = convert(UNIQUEIDENTIFIER, '556F21C6-D87F-404B-B42A-797B2870929E')
AND (o.KeyWebOrder LIKE '%kansas%'
OR OrderNumber LIKE '%kansas%'
--OR DateCreated LIKE '%kansas%'
--OR convert(VARCHAR(256), keyWebStatus) LIKE '%kansas%'
OR d.LeftMenuHeader LIKE '%kansas%')
),
customer as (
SELECT cha.KeyWebOrder
, MAX(CASE WHEN cha.OrderType = 1 AND chq.WebOrderColumnName LIKE 'BillingFirstName' THEN Answer END) BillingFirstName
, MAX(CASE WHEN cha.OrderType = 1 AND chq.WebOrderColumnName LIKE 'BillingLastName' THEN Answer END) BillingLastName
, MAX(CASE WHEN cha.OrderType = 2 AND chq.WebOrderColumnName LIKE 'ShippingCity' THEN Answer END) Location
FROM WebCustomerAnswers AS cha
JOIN WebCustomerQuestions AS chq ON cha.keyWebCustomerQuestions = chq.keyWebCustomerQuestions  -- CORRECTED TYPO
GROUP BY KeyWebOrder  -- CHANGE o.keyWebOrder to cha.keyWebOrder
),
checkout as (
SELECT cha.keyWebOrder, cha.Answer ShowName
FROM WebCheckoutAnswers AS cha
JOIN WebCheckoutQuestions AS chq ON cha.keyWebCheckoutQuestions = chq.keyWebCheckoutQuestions  -- CORRECTED TYPO
WHERE chq.WebOrderColumnName LIKE 'ShowName'
) 
SELECT TOP 15 id
	,wo.KeyWebOrder
	,OrderNumber
	,DateCreated
	,keyWebStatus
	,ISNULL(BillingFirstName,'') + ' ' + ISNULL(BillingLastName, '') Ordered_by
	,Subdivision
	,ShowName
	,Location
FROM Test wo
LEFT JOIN customer cu on wo.KeyWebOrder = cu.KeyWebOrder
LEFT JOIN checkout co on wo.KeyWebOrder = co.KeyWebOrder
WHERE BillingFirstName LIKE '%kansas%'
OR BillingLastName LIKE '%kansas%'
OR Location LIKE '%kansas%'
OR ShowName LIKE '%kansas%'
ORDER BY OrderNumber

Open in new window

ste5an

[N]TEXT is deprecated. You should have migrated this to [N]VARCHAR(MAX)..

ASKER CERTIFIED SOLUTION

Raja Jegan R

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

woodwyn

ASKER

Raja - I've made some additional changes to the query. This now works and is instantaneous. A huge improvement on what the original developers had in place. Mainly, I corrected where needed per errors and moved most of the where clauses to the final selection. Having them in the first select yielded no results.

Ste5en - I haven't integrated the new query to the web site yet. However, I have modified the columns from text to varchar(max) and added a clustered index to the webCheckoutAnswers table. In it's original format the website results have gone from 30 to 2 seconds. Obviously it was the clustered index that improved things so much.

; with webord as (
SELECT o.KeyWebOrder AS id
	,convert(VARCHAR(256), o.KeyWebOrder) AS KeyWebOrder
	,OrderNumber
	,DateCreated
	,convert(VARCHAR(256), keyWebStatus) AS keyWebStatus
	,d.LeftMenuHeader AS Subdivision
FROM WebOrder o
LEFT JOIN WebSubDomains d ON o.keyWebSubDomains = d.keyWebSubDomains
WHERE d.keyWebSubDomains = convert(UNIQUEIDENTIFIER, '8BAB3BAB-A22F-48A9-8ADF-CCC2ED70FBF7')
AND keyWebStatus != convert(UNIQUEIDENTIFIER, '00000000-0000-0000-0000-000000000000')
AND keyWebPrimaryDomains = convert(UNIQUEIDENTIFIER, '556F21C6-D87F-404B-B42A-797B2870929E')
),
customer as (
SELECT cha.KeyWebOrder
, MAX(CASE WHEN cha.OrderType = 1 AND chq.WebOrderColumnName LIKE 'BillingFirstName' THEN cast(Answer as varchar(256)) END) BillingFirstName
, MAX(CASE WHEN cha.OrderType = 1 AND chq.WebOrderColumnName LIKE 'BillingLastName' THEN cast(Answer as varchar(256)) END) BillingLastName
, MAX(CASE WHEN cha.OrderType = 2 AND chq.WebOrderColumnName LIKE 'ShippingCity' THEN cast(Answer as varchar(256)) END) Location
FROM WebCustomerAnswers AS cha
JOIN WebCustomerQuestions AS chq ON cha.keyWebCustomerQuestions = chq.keyWebCustomerQuestions
GROUP BY cha.KeyWebOrder
),
checkout as (
SELECT cha.keyWebOrder, MAX(cast(cha.Answer as varchar(256))) ShowName
FROM WebCheckoutAnswers AS cha
JOIN WebCheckoutQuestions AS chq ON cha.keyWebCheckoutQuestions = chq.keyWebCheckoutQuestions
WHERE chq.WebOrderColumnName LIKE 'ShowName'
GROUP BY cha.KeyWebOrder
)
--SELECT * FROM WebOrd
SELECT TOP 15 id
	,wo.KeyWebOrder
	,OrderNumber
	,DateCreated
	,keyWebStatus
	,ISNULL(BillingFirstName,'') + ' ' + ISNULL(BillingLastName, '') Ordered_by
	,Subdivision
	,ShowName
	,Location
FROM webord wo
LEFT JOIN customer cu on wo.KeyWebOrder = cu.KeyWebOrder
LEFT JOIN checkout co on wo.KeyWebOrder = co.KeyWebOrder
WHERE wo.KeyWebOrder LIKE '%kansas%'
OR OrderNumber LIKE '%kansas%'
OR DateCreated LIKE '%kansas%'
OR convert(VARCHAR(256), keyWebStatus) LIKE '%kansas%'
OR SubDivision LIKE '%kansas%'
OR BillingFirstName LIKE '%kansas%'
OR BillingLastName LIKE '%kansas%'
OR Location LIKE '%kansas%'
OR ShowName LIKE '%kansas%'
ORDER BY OrderNumber

Open in new window

Raja Jegan R

>> This now works and is instantaneous.

Thanks..

>> Obviously it was the clustered index that improved things so much.

Yes, each and every table should have a clustered index and supporting nonclustered index based upon its usage..

woodwyn

ASKER

Why did EE make it difficult to split points between experts? Can either of you tell me how I can do this?

Raja Jegan R

Kindly let us know the way you need to split the points along with comments so that we can assist you accordingly..

woodwyn

ASKER

I would split the points evenly between Ste5ans first comment and Rajas final comment with the revision to the query.