October 6, 2012 at 4:23 pm
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))
October 6, 2012 at 5:44 pm
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
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);October 6, 2012 at 5:48 pm
...Can you post an execution plan?
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);October 6, 2012 at 5:53 pm
Yes sure - you OK with an XML?
October 6, 2012 at 5:57 pm
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....;-);-);-)
October 6, 2012 at 6:33 pm
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
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);October 6, 2012 at 6:59 pm
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 🙂
October 7, 2012 at 6:15 am
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)
October 7, 2012 at 12:06 pm
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 Mondaythe 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.
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
October 7, 2012 at 12:33 pm
Wow thanks a lot for the revised script - I will test this with live data but the performance increase is amazing on test :-):-)
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply