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

Nested queries problem in SQL2008R2 Expand / Collapse
Author
Message
Posted Saturday, October 06, 2012 4:23 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, January 21, 2013 2:28 PM
Points: 6, Visits: 21
Hello

We did an upgrade to SQL2008R2 last night and the query below seems to take an excessive time to execute - we have only noticed the issue with nested queries- it run in less than 1 second on SQL2000 but since upgrading to 2008R2 it takes 15 seconds - as it is part of a very large report the delay users experiencing is around 15min which has not made us very popular at the moment and we need to find a solution FAST..... We have excluded hardware/disk performance issues on the server (RAID10 - 8 proc with plenty of memory, databases on different drives etc).

It almost looks like the query optimizer is a lot stricter in SQL2008R2 - the script is below - any glaring errors or changes to make or any other help to make it faster would be GREATLY appreciated

SELECT TOP 1
CustomFormFieldDefinitions.FieldName

FROM
Events
INNER JOIN CustomFormFields ON Events.RoomCustFrmDataId = CustomFormFields.CustFrmDataId
INNER JOIN CustomFormFieldDefinitions ON CustomFormFieldDefinitions.FieldId = CustomFormFields.FieldId
INNER JOIN CustomFormData ON CustomFormFields.CustFrmDataId = CustomFormData.CustFrmDataId
LEFT JOIN Entity ON CustomFormFields.EntityIdValue = Entity.EntityId

WHERE
Events.Number_SQL = 374376
AND
(CustomFormFieldDefinitions.FieldName LIKE 'Security/Fireman/Facilities'
OR CustomFormFieldDefinitions.FieldName LIKE 'Security/Firemen/Facilities')

OR EXISTS
(SELECT TOP 1
Resources.Name_SQL

FROM

Events LEFT JOIN Resource_Bookings ON Events.Number_SQL = Resource_Bookings.Event_Number
LEFT JOIN Resources ON Resource_Bookings.Resource_Number = Resources.Number_SQL
LEFT JOIN Resource_Types ON Resources.Type = Resource_Types.Number_SQL
LEFT JOIN Rooms ON Events.Room_Number = Rooms.Number_SQL

WHERE
Events.Number_SQL = 374376
AND
Resources.Classification = 'Sec/Fire/Fac'
AND
Resources.Type = 4)

OR EXISTS
(SELECT
CustomFormFieldDefinitions.FieldName

FROM
Events INNER JOIN EventDocuments ON Events.Number_SQL = EventDocuments.EventNumber
INNER JOIN (SELECT * FROM Documents WHERE FullyQualifiedName LIKE 'Database:%') AS Docs ON EventDocuments.DocumentID = Docs.DocumentID
INNER JOIN CustomFormData ON CustomFormData.CustFrmDataID = SUBSTRING(Docs.FullyQualifiedName,10,12)
INNER JOIN CustomFormFields ON CustomFormFields.CustFrmDataID = SUBSTRING(Docs.FullyQualifiedName,10,12)
INNER JOIN CustomFormFieldDefinitions ON CustomFormFieldDefinitions.FieldID = CustomFormFields.FieldID

WHERE
Events.Number_SQL = 374376
AND
CustomFormFieldDefinitions.CustFrmDefinitionId IN (150,122,99))
Post #1369476
Posted Saturday, October 06, 2012 5:44 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 9:12 AM
Points: 1,651, Visits: 5,195
One change seems to be to remove the Entity table - it is not being used.

...and get rid of the "TOP 1" from the EXISTS...

...and be more selective about the select from Documents


MM


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Post #1369481
    Posted Saturday, October 06, 2012 5:47 PM


    SSCommitted

    SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

    Group: General Forum Members
    Last Login: Today @ 9:12 AM
    Points: 1,651, Visits: 5,195
    ...Can you post an execution plan?

    MM


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Post #1369482
    Posted Saturday, October 06, 2012 5:53 PM
    Forum Newbie

    Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

    Group: General Forum Members
    Last Login: Monday, January 21, 2013 2:28 PM
    Points: 6, Visits: 21
    Yes sure - you OK with an XML?
    Post #1369483
    Posted Saturday, October 06, 2012 5:57 PM
    Forum Newbie

    Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

    Group: General Forum Members
    Last Login: Monday, January 21, 2013 2:28 PM
    Points: 6, Visits: 21
    Have attached the execution plan - thank you so much for taking the time to look into this - it is driving me nuts at the moment....

      Post Attachments 
    slowquerySQLPLAN.zip (1 view, 10.54 KB)
    Post #1369484
    Posted Saturday, October 06, 2012 6:33 PM


    SSCommitted

    SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

    Group: General Forum Members
    Last Login: Today @ 9:12 AM
    Points: 1,651, Visits: 5,195
    Can we see an Actual execution plan as well?

    Have you rebuilt the indexes since the upgrade?

    Is this query parameterised in a stored proc?

    What is the output from SET STATISTICS TIME ON when running the script?


    MM


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Post #1369487
    Posted Saturday, October 06, 2012 6:59 PM
    Forum Newbie

    Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

    Group: General Forum Members
    Last Login: Monday, January 21, 2013 2:28 PM
    Points: 6, Visits: 21
    1. I have re-build all the indexes but that has not made a difference unfortunately

    2. is there a way to post the graphic of the execution plan graphically - I have tried a screen dump but it is impossible to fit it into the screen and looks too small to read if zoomed out too much

    3. the query is parametarized via a stored procedure normally but I am running the specific parts in query analyzer

    4. the stats for this query are

    SQL Server parse and compile time:
    CPU time = 0 ms, elapsed time = 0 ms.

    (0 row(s) affected)

    SQL Server Execution Times:
    CPU time = 15219 ms, elapsed time = 15214 ms.

    Going to bed now - almost 2am here (UK) - thanks again for all your suggestions will get back to you once I have woken up in the morning
    Post #1369488
    Posted Sunday, October 07, 2012 6:15 AM
    Forum Newbie

    Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

    Group: General Forum Members
    Last Login: Monday, January 21, 2013 2:28 PM
    Points: 6, Visits: 21
    whist experimenting with the query noticed that by removing the TOP1 the query suddenly started running in parallel mode which was A LOT faster. Still needed the TOP1 though but have managed to force the query to run in parallel mode - will have to do more testing on Monday

    the command I used to force the query optimizer to treat the query to execute in parallel is

    OPTION (RECOMPILE, QUERYTRACEON 8649)
    Post #1369523
    Posted Sunday, October 07, 2012 12:06 PM


    SSCertifiable

    SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

    Group: General Forum Members
    Last Login: Today @ 9:41 AM
    Points: 6,748, Visits: 12,843
    nico 71726 (10/7/2012)
    whist experimenting with the query noticed that by removing the TOP1 the query suddenly started running in parallel mode which was A LOT faster. Still needed the TOP1 though but have managed to force the query to run in parallel mode - will have to do more testing on Monday

    the command I used to force the query optimizer to treat the query to execute in parallel is

    OPTION (RECOMPILE, QUERYTRACEON 8649)


    I think you need to take a look at the logic of the query before spending any more time on performance. With one or two rearrangements, it looks like this:

    SELECT TOP 1 
    cffd.FieldName

    FROM [Events] e
    INNER JOIN CustomFormFields cff
    ON e.RoomCustFrmDataId = cff.CustFrmDataId
    INNER JOIN CustomFormFieldDefinitions cffd
    ON cffd.FieldId = CustomFormFields.FieldId
    AND cffd.FieldName IN ('Security/Fireman/Facilities','Security/Firemen/Facilities')
    INNER JOIN CustomFormData cfd
    ON cff.CustFrmDataId = cfd.CustFrmDataId
    --LEFT JOIN Entity ON cff.EntityIdValue = Entity.EntityId -- redundant

    WHERE e.Number_SQL = 374376

    OR EXISTS -- SUBQUERY 1

    (SELECT 1 -- TOP not necessary, probably impedes performance
    FROM [Events] e
    INNER JOIN Resource_Bookings rb -- converted to IJ by next table reference
    ON e.Number_SQL = rb.Event_Number
    INNER JOIN Resources r
    ON rb.Resource_Number = r.Number_SQL
    AND r.Classification = 'Sec/Fire/Fac' -- referenced in WHERE clause
    AND r.[Type] = 4 -- referenced in WHERE clause -->> INNER JOIN
    --LEFT JOIN Resource_Types rt ON r.Type = rt.Number_SQL -- redundant
    --LEFT JOIN Rooms ON e.Room_Number = Rooms.Number_SQL -- redundant
    WHERE e.Number_SQL = 374376)

    OR EXISTS -- SUBQUERY 2

    (SELECT 1
    FROM [Events] e
    INNER JOIN EventDocuments ed
    ON e.Number_SQL = ed.EventNumber
    INNER JOIN (
    SELECT CustFrmDataID = SUBSTRING(d.FullyQualifiedName,10,12) --
    FROM Documents d
    WHERE d.FullyQualifiedName LIKE 'Database:%'
    ) AS Docs
    ON ed.DocumentID = Docs.DocumentID
    INNER JOIN CustomFormData cfd
    ON cfd.CustFrmDataID = docs.CustFrmDataID
    INNER JOIN CustomFormFields cff
    ON cff.CustFrmDataID = cfd.CustFrmDataID --docs.CustFrmDataID
    INNER JOIN CustomFormFieldDefinitions cffd
    ON cffd.FieldID = cff.FieldID
    AND cffd.CustFrmDefinitionId IN (150,122,99)
    WHERE e.Number_SQL = 374376)

    - which means that, if the main query doesn't return a result for events.Number_SQL = 374376 but either subquery1 or subquery2 do return a result, then the whole query will return a value - but you won't know which events.Number_SQL it came from.


    “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 #1369550
    Posted Sunday, October 07, 2012 12:33 PM
    Forum Newbie

    Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

    Group: General Forum Members
    Last Login: Monday, January 21, 2013 2:28 PM
    Points: 6, Visits: 21
    Wow thanks a lot for the revised script - I will test this with live data but the performance increase is amazing on test
    Post #1369557
    « Prev Topic | Next Topic »

    Add to briefcase

    Permissions Expand / Collapse