Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Migration from MSDE (SQL2K) to SQLEXPRESS (SQL2K8) - Query Performance Degradation Expand / Collapse
Author
Message
Posted Wednesday, April 10, 2013 9:31 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, April 11, 2013 10:05 AM
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
Post #1440890
Posted Wednesday, April 10, 2013 10:01 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:28 PM
Points: 13,103, Visits: 11,933
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)
Post #1440917
Posted Wednesday, April 10, 2013 10:24 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:28 PM
Points: 13,103, Visits: 11,933
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
)
)
)
)
)
)
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


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)
Post #1440928
Posted Wednesday, April 10, 2013 10:33 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, April 11, 2013 10:05 AM
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
Post #1440931
Posted Wednesday, April 10, 2013 10:36 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, April 11, 2013 10:05 AM
Points: 7, Visits: 8
just got second message... apologies for the bloody eyes.... processing response
Post #1440933
Posted Wednesday, April 10, 2013 10:47 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, April 11, 2013 10:05 AM
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...
Post #1440938
Posted Wednesday, April 10, 2013 10:50 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 7:41 PM
Points: 23,033, Visits: 31,555
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.



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)
Post #1440941
Posted Thursday, April 11, 2013 1:43 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, April 11, 2013 10:05 AM
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.
Post #1441134
Posted Thursday, April 11, 2013 3:16 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:52 AM
Points: 7,123, Visits: 13,497
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
Post #1441143
Posted Thursday, April 11, 2013 3:28 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:52 AM
Points: 7,123, Visits: 13,497
-- 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
Post #1441147
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse