Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Migration from MSDE (SQL2K) to SQLEXPRESS (SQL2K8) - Query Performance Degradation


Migration from MSDE (SQL2K) to SQLEXPRESS (SQL2K8) - Query Performance Degradation

Author
Message
staceyhopkins78
staceyhopkins78
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 8
I have migrated some databases from MSDE (SQL2K) to SQLEXPRESS (SQL2K8)

Just using the backup and restore functions provided and made no other changes.

I notice a query in particular has gone from taking 2 seconds in MSDE to taking 988 seconds in SQLEXPRESS. Hmmmm!

Can anyone advise on why this would be happening?

Query below... can't provide any sample data unfortunately but if you need any more info please let me know - not sure what would be needed?!?!

(TBL_INSTANCES contains 12946 rows and TBL_RELATIONSHIPS contains 18137 rows)

SELECT [Build1].dbo.TBL_TYPE_DEF.TYPE_NAME, [Build1].dbo.TBL_INSTANCES.DOC_NAME,[Build1].dbo.TBL_INSTANCES.DOC, [Build1].dbo.TBL_INSTANCES.SEC, [Build1].dbo.TBL_INSTANCES.VER,[Build1].dbo.TBL_RELATIONSHIPS.JUSTIFICATION FROM [Build1].dbo.TBL_INSTANCES INNER JOIN [Build1].dbo.TBL_TYPE_DEF ON [Build1].dbo.TBL_INSTANCES.TYPE_ID = [Build1].dbo.TBL_TYPE_DEF.TYPE_ID LEFT OUTER JOIN [Build1].dbo.TBL_RELATIONSHIPS ON [Build1].dbo.TBL_INSTANCES.DOC = [Build1].dbo.TBL_RELATIONSHIPS.PARENT_DOC AND [Build1].dbo.TBL_INSTANCES.SEC = [Build1].dbo.TBL_RELATIONSHIPS.PARENT_SEC AND [Build1].dbo.TBL_INSTANCES.VER = [Build1].dbo.TBL_RELATIONSHIPS.PARENT_VER WHERE (([Build1].dbo.TBL_INSTANCES.DOC + [Build1].dbo.TBL_INSTANCES.SEC + ISNULL(CONVERT(VARCHAR(10), [Build1].dbo.TBL_INSTANCES.VER), '')) NOT IN (SELECT PARENT_DOC + PARENT_SEC + ISNULL(CONVERT(VARCHAR(10), PARENT_VER), '') FROM [Build1].dbo.TBL_RELATIONSHIPS AS TBL_RELATIONSHIPS_1 WHERE ((PARENT_DOC + PARENT_SEC + ISNULL(CONVERT(VARCHAR(10), PARENT_VER), '')) IN (SELECT DOC+SEC+ISNULL(CONVERT(VARCHAR(10), VER),'') FROM [Build1].dbo.TBL_INSTANCES AS TBL_INSTANCES_1 WHERE (TYPE_ID IN (SELECT TYPE_ID FROM [Build1].dbo.TBL_TYPE_DEF AS TBL_TYPE_DEF_1 WHERE (TYPE_ID NOT IN (SELECT CHILD_TYPE FROM [Build1].dbo.TBL_RELATIONSHIPS_ALLOWED)))))) AND (CHILD_DOC IS NOT NULL))) AND ([Build1].dbo.TBL_INSTANCES.TYPE_ID IN (SELECT TYPE_ID FROM [Build1].dbo.TBL_TYPE_DEF AS TBL_TYPE_DEF_1 WHERE (TYPE_ID NOT IN (SELECT CHILD_TYPE FROM [Build1].dbo.TBL_RELATIONSHIPS_ALLOWED AS TBL_RELATIONSHIPS_ALLOWED_1)))) ORDER BY [Build1].dbo.TBL_TYPE_DEF.TYPE_ID,DOC,SEC,VER


I am a total newbie to query optimization, so any help, guidance or direction appreciated

Thanks in advance!

Stacey
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16542 Visits: 16997
You need to update stats and rebuild your indexes.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16542 Visits: 16997
Lynn Pettis (another long time poster around here) pointed out there is a LOT more wrong with your query. I didn't bother to look past the hideous formatting but he ran it through a formatter. Once I did, my eyes started bleeding.




SELECT [Build1].dbo.TBL_TYPE_DEF.TYPE_NAME
   ,[Build1].dbo.TBL_INSTANCES.DOC_NAME
   ,[Build1].dbo.TBL_INSTANCES.DOC
   ,[Build1].dbo.TBL_INSTANCES.SEC
   ,[Build1].dbo.TBL_INSTANCES.VER
   ,[Build1].dbo.TBL_RELATIONSHIPS.JUSTIFICATION
FROM [Build1].dbo.TBL_INSTANCES
INNER JOIN [Build1].dbo.TBL_TYPE_DEF ON [Build1].dbo.TBL_INSTANCES.TYPE_ID = [Build1].dbo.TBL_TYPE_DEF.TYPE_ID
LEFT JOIN [Build1].dbo.TBL_RELATIONSHIPS ON [Build1].dbo.TBL_INSTANCES.DOC = [Build1].dbo.TBL_RELATIONSHIPS.PARENT_DOC
   AND [Build1].dbo.TBL_INSTANCES.SEC = [Build1].dbo.TBL_RELATIONSHIPS.PARENT_SEC
   AND [Build1].dbo.TBL_INSTANCES.VER = [Build1].dbo.TBL_RELATIONSHIPS.PARENT_VER
WHERE (
      ([Build1].dbo.TBL_INSTANCES.DOC + [Build1].dbo.TBL_INSTANCES.SEC + ISNULL(CONVERT(VARCHAR(10), [Build1].dbo.TBL_INSTANCES.VER), '')) NOT IN (
         SELECT PARENT_DOC + PARENT_SEC + ISNULL(CONVERT(VARCHAR(10), PARENT_VER), '')
         FROM [Build1].dbo.TBL_RELATIONSHIPS AS TBL_RELATIONSHIPS_1
         WHERE (
               (PARENT_DOC + PARENT_SEC + ISNULL(CONVERT(VARCHAR(10), PARENT_VER), '')) IN (
                  SELECT DOC + SEC + ISNULL(CONVERT(VARCHAR(10), VER), '')
                  FROM [Build1].dbo.TBL_INSTANCES AS TBL_INSTANCES_1
                  WHERE (
                        TYPE_ID IN (
                           SELECT TYPE_ID
                           FROM [Build1].dbo.TBL_TYPE_DEF AS TBL_TYPE_DEF_1
                           WHERE (
                                 TYPE_ID NOT IN (
                                    SELECT CHILD_TYPE
                                    FROM [Build1].dbo.TBL_RELATIONSHIPS_ALLOWED
                                    Wink
                                 Wink
                           Wink
                        Wink
                  Wink
               Wink
            AND (CHILD_DOC IS NOT NULL)
         Wink
      Wink
   AND (
      [Build1].dbo.TBL_INSTANCES.TYPE_ID IN (
         SELECT TYPE_ID
         FROM [Build1].dbo.TBL_TYPE_DEF AS TBL_TYPE_DEF_1
         WHERE (
               TYPE_ID NOT IN (
                  SELECT CHILD_TYPE
                  FROM [Build1].dbo.TBL_RELATIONSHIPS_ALLOWED AS TBL_RELATIONSHIPS_ALLOWED_1
                  Wink
               Wink
         Wink
      Wink
ORDER BY [Build1].dbo.TBL_TYPE_DEF.TYPE_ID
   ,DOC
   ,SEC
   ,VER




You need to use table aliases. IIRC using the full qualification in the select list has been deprecated. Just using aliases would increase the readability a lot. Then there are all the subqueries and column concatenation. You also need to avoid using sql keywords as object or column names. It makes things a lot tougher to work with.

The indexes and stats I mentioned are crucial for this to have a chance, but you could greatly improve this query. It probably needs a complete rewrite.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
staceyhopkins78
staceyhopkins78
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 8
thanks for the response -

have gone through updating first

stats (right click - properties on each item in Statistics and clicking on the "Update statistics for these columns" check box and clicking OK)

and second rebuilding indexes (right click - rebuild)


.... unfortunately no change still taking a long time for the query
staceyhopkins78
staceyhopkins78
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 8
just got second message... apologies for the bloody eyes.... processing response
staceyhopkins78
staceyhopkins78
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 8
thanks for the feedback I guess I will go away and re write - the one concern I have is that I don't know how I would write it any differently with the knowledge I have today....

Understand the readability comment with aliasing... I have done some but not very well but would not think that better aliasing would improve performance?

I obviously have done some bad things but don't what the alternatives are...
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24169 Visits: 37934
If you post the DDL (CREATE TABLE statement) for the table(s), some sample data (not real data) as a series of INSERT INTO statements for the table(s), and expected results based on the sample data we can help with the rewrite.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
staceyhopkins78
staceyhopkins78
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 8
thanks for the kind offer ... that may be more than I am allowed to share (will have to check).... understand why you would need this info.... I guess I am still very perplexed why I have had the massive increase in execution time, doesn't fit right in my brain at least.
ChrisM@Work
ChrisM@Work
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 8957 Visits: 19014
Thanks to Lynn & Sean for the formatted query:

-- code rewrites are iterative in nature. You make a change or two,
-- test thoroughly, amend if necessary, then make another change.
-- Here's two changes (with no data to test against):
-- 1: use of table aliases and USE to eliminate unnecessary characters
-- and 2: a rewrite of the subquery in the WHERE clause.
USE [Build1]

SELECT DISTINCT TYPE_ID
INTO #TypeIDNoChild
FROM TBL_TYPE_DEF
WHERE TYPE_ID NOT IN (SELECT CHILD_TYPE FROM TBL_RELATIONSHIPS_ALLOWED)

SELECT
    td.TYPE_NAME
   ,i.DOC_NAME
   ,i.DOC
   ,i.SEC
   ,i.VER
   ,r.JUSTIFICATION
FROM TBL_INSTANCES i

INNER JOIN TBL_TYPE_DEF td
   ON i.TYPE_ID = td.TYPE_ID
   
LEFT JOIN TBL_RELATIONSHIPS r
   ON i.DOC = r.PARENT_DOC
   AND i.SEC = r.PARENT_SEC
   AND i.VER = r.PARENT_VER

WHERE NOT EXISTS (
   SELECT 1
   FROM TBL_RELATIONSHIPS ri
   INNER JOIN TBL_INSTANCES ii
      ON ii.DOC = ri.PARENT_DOC
      AND ii.SEC = ri.PARENT_SEC
      AND ii.VER = ri.PARENT_VER -- nullable
      AND ii.TYPE_ID IN (SELECT TYPE_ID FROM #TypeIDNoChild)
   WHERE ri.CHILD_DOC IS NOT NULL
      AND ri.PARENT_DOC = i.DOC
      AND ri.PARENT_SEC = i.SEC
      AND ri.PARENT_VER = i.VER
)
            
AND i.TYPE_ID IN (SELECT TYPE_ID FROM #TypeIDNoChild)

ORDER BY td.TYPE_ID
   ,i.DOC
   ,i.SEC
   ,i.VER



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
ChrisM@Work
ChrisM@Work
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 8957 Visits: 19014
-- Here's the last change.
USE [Build1]

SELECT
    td.TYPE_NAME
   ,i.DOC_NAME
   ,i.DOC
   ,i.SEC
   ,i.VER
   ,r.JUSTIFICATION
FROM TBL_INSTANCES i

INNER JOIN TBL_TYPE_DEF td
   ON i.TYPE_ID = td.TYPE_ID
   
LEFT JOIN TBL_RELATIONSHIPS r
   ON i.DOC = r.PARENT_DOC
   AND i.SEC = r.PARENT_SEC
   AND i.VER = r.PARENT_VER

-- simplification
WHERE NOT EXISTS (
   SELECT 1
   FROM TBL_RELATIONSHIPS ri   
   WHERE ri.PARENT_DOC = i.DOC
      AND ri.PARENT_SEC = i.SEC
      AND ri.PARENT_VER = i.VER      
      AND ri.CHILD_DOC IS NOT NULL
)

-- simplification            
AND i.TYPE_ID NOT IN (SELECT CHILD_TYPE FROM TBL_RELATIONSHIPS_ALLOWED)

ORDER BY td.TYPE_ID
   ,i.DOC
   ,i.SEC
   ,i.VER



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search