April 10, 2013 at 9:31 am
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
April 10, 2013 at 10:01 am
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 10, 2013 at 10:24 am
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 10, 2013 at 10:33 am
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
April 10, 2013 at 10:36 am
just got second message... apologies for the bloody eyes.... processing response
April 10, 2013 at 10:47 am
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...
April 10, 2013 at 10:50 am
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.
April 11, 2013 at 1:43 am
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.
April 11, 2013 at 3:16 am
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
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
April 11, 2013 at 3:28 am
-- 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
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
April 11, 2013 at 8:27 am
wow - thank you - that is far more than I expected.
April 11, 2013 at 8:33 am
wow - thank you - that was far more than I expected
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply