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 9 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply