SQL Clone
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
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26558 Visits: 17557
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 Modens 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
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26558 Visits: 17557
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 Modens 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-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40462 Visits: 38567
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
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: 16684 Visits: 19557
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
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: 16684 Visits: 19557
-- 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