September 17, 2013 at 2:42 pm
Dear Experts,
I understand that without test data and actual DDL it is hard to get anyone to help with. I am just looking for some one to give me an idea on how to fine tune this query. Currently it runs in about 20 seconds and that is too slow. I have feeling there should be a better way to write this query, but I just couldn't get it straight. most of the time spent on the 2 inner query that union together.
Thanks!
SELECT * FROM (
SELECT D2.DataOwner AS [Defined in Asset Model],D1.DataOwner, OT.ObjectType, R1.ID as RefDef, R1.OUID AS [RefDef Name]
FROM Table0 REF
JOIN Table1 R1 ON REF.RefDef = R1.ID
JOIN Table1 R2 ON REF.InputID = R2.ID
JOIN Table2 OT ON R2.ObjectTypeID = OT.ID
JOIN Table3 D1 ON R2.DataOwnerID = D1.ID
JOIN Table3 D2 ON R1.DataOwnerID = D2.ID
WHERE D1.DataOwner = N'SomeValue'
GROUP BY D2.DataOwner, D1.DataOwner, OT.ObjectType, R1.ID, R1.OUID
UNION
SELECT D2.DataOwner AS [Defined in Asset Model],D1.DataOwner, OT.ObjectType, R1.ID as RefDef, R1.OUID AS [RefDef Name]
FROM Table0 REF
JOIN Table1 R1 ON REF.RefDef = R1.ID
JOIN Table1 R2 ON REF.OutputID = R2.ID
JOIN Table2 OT ON R2.ObjectTypeID = OT.ID
JOIN Table3 D1 ON R2.DataOwnerID = D1.ID
JOIN Table3 D2 ON R1.DataOwnerID = D2.ID
WHERE D1.DataOwner = N'SomeValue'
GROUP BY D2.DataOwner, D1.DataOwner, OT.ObjectType, R1.ID, R1.OUID
) as Ref
WHERE NOT EXISTS (SELECT * FROM Table4
WHERE Table4.[Defined in Asset Model] = Ref.[Defined in Asset Model] AND
Table4.DataOwner = Ref.DataOwner AND
Table4.ObjectType = Ref.ObjectType AND
Table4.RefDef = Ref.RefDef AND
Table4.[RefDef Name] = Ref.[RefDef Name])
September 17, 2013 at 2:54 pm
Please post table definition, index definitions and execution plan as per http://qa.sqlservercentral.com/articles/SQLServerCentral/66909/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply