InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 19 Jun 2013 02:36:05 GMT20http://www.sqlservercentral.com/Forums/Topic1369476-392-1.aspxWow thanks a lot for the revised script - I will test this with live data but the performance increase is amazing on test :-):-)Sun, 07 Oct 2012 12:33:23 GMTnico 71726http://www.sqlservercentral.com/Forums/Topic1369476-392-1.aspx[quote][b]nico 71726 (10/7/2012)[/b][hr]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)[/quote]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:[code="sql"]SELECT TOP 1 cffd.FieldNameFROM [Events] e INNER JOIN CustomFormFields cff ON e.RoomCustFrmDataId = cff.CustFrmDataIdINNER 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 -- redundantWHERE e.Number_SQL = 374376OR 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)[/code]- which means that, if the main query doesn't return a result for events.Number_SQL = 374376 but either subquery1 or subquery2 [i]do [/i]return a result, then the whole query will return a value - but you won't know which events.Number_SQL it came from.Sun, 07 Oct 2012 12:06:16 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic1369476-392-1.aspxwhist 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)Sun, 07 Oct 2012 06:15:01 GMTnico 71726http://www.sqlservercentral.com/Forums/Topic1369476-392-1.aspx1. I have re-build all the indexes but that has not made a difference unfortunately2. 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 much3. 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 areSQL 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 :-)Sat, 06 Oct 2012 18:59:32 GMTnico 71726http://www.sqlservercentral.com/Forums/Topic1369476-392-1.aspxCan 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?Sat, 06 Oct 2012 18:33:56 GMTmister.magoohttp://www.sqlservercentral.com/Forums/Topic1369476-392-1.aspxHave attached the execution plan - thank you so much for taking the time to look into this - it is driving me nuts at the moment....;-);-);-)Sat, 06 Oct 2012 17:57:36 GMTnico 71726http://www.sqlservercentral.com/Forums/Topic1369476-392-1.aspxYes sure - you OK with an XML?Sat, 06 Oct 2012 17:53:17 GMTnico 71726http://www.sqlservercentral.com/Forums/Topic1369476-392-1.aspx...Can you post an execution plan?Sat, 06 Oct 2012 17:47:59 GMTmister.magoohttp://www.sqlservercentral.com/Forums/Topic1369476-392-1.aspxOne 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 DocumentsSat, 06 Oct 2012 17:44:29 GMTmister.magoohttp://www.sqlservercentral.com/Forums/Topic1369476-392-1.aspxHello 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 1CustomFormFieldDefinitions.FieldNameFROMEvents INNER JOIN CustomFormFields ON Events.RoomCustFrmDataId = CustomFormFields.CustFrmDataIdINNER JOIN CustomFormFieldDefinitions ON CustomFormFieldDefinitions.FieldId = CustomFormFields.FieldIdINNER JOIN CustomFormData ON CustomFormFields.CustFrmDataId = CustomFormData.CustFrmDataIdLEFT JOIN Entity ON CustomFormFields.EntityIdValue = Entity.EntityIdWHEREEvents.Number_SQL = 374376AND (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))Sat, 06 Oct 2012 16:23:09 GMTnico 71726