Query slowed down after migration from SQL 2000 to 2016

woodwyn
woodwyn used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
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..
ste5anSenior Developer
Commented:
You should complete the migration first. Use the JOIN syntax for your sub queries. Use always table alias names. Then get rid of the unnecessary casts. Why is cha.keyWebOrder = CONVERT(VARCHAR(256), o.KeyWebOrder) needed? Also there concatenation in the search conditions is redundant when you search for keywords, E.g.

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

Open in new window


I have checked the SQL 2000 tables and there are no indexes,
Create the appropriate indices!

Also keep in mind, that the pattern '%keyword%' cannot use a normal index. Consider using full-text search with an FULLTEXT INDEX instead.

Further more using TOP 1 without order by gives you arbitrary results. Also the predicates look pretty weird:

o.KeyWebOrder LIKE '%kansas%'
OR KeyWebOrder LIKE '%kansas%'
OR OrderNumber LIKE '%kansas%'
OR DateCreated LIKE '%kansas%'
OR keyWebStatus LIKE '%kansas%'
OR d.LeftMenuHeader LIKE '%kansas%'

Open in new window


A DateCreated column should be of the data type DATE. Searching in it for keywords is nonsense. Same for status and order number and menu header..

p.s. use the CODE button.

Author

Commented:
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

Become a Microsoft Certified Solutions Expert

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

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

Author

Commented:
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
ste5anSenior Developer

Commented:
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 RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
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

Author

Commented:
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.

Author

Commented:
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

ste5anSenior Developer

Commented:
[N]TEXT is deprecated. You should have migrated this to [N]VARCHAR(MAX)..
SQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018
Commented:
Modified the query slightly..(as ste5an mentioned you should have converted all text/ntext datatype columns to varchar(max)/nvarchar(max) datatypes..
; 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')
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 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.keyWebCheckoutQuestions = chq.keyWebCheckoutQuestions
GROUP BY o.KeyWebOrder
),
checkout as (
SELECT cha.keyWebOrder, MAX(cast(cha.Answer as varchar(256))) 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 webord 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

Author

Commented:
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 RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
>> 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..

Author

Commented:
Why did EE make it difficult to split points between experts?  Can either of you tell me how I can do this?
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

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

Author

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial