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