SQL Server Performance Forum – Threads Archive

Query taking more than 40 minutes

I have query written to extract data and takes more than 40 minutes…and its getting really frustrated. Query: SELECT TmbR.BusnPartTmMbrRoleId, Tmb.BusnPartTmId, Tmb.BusnPartEmpId, TmbR.rolecd, TmbR.BusnPartTmRoleId, TmbR.BusnPartTmMbrRoleStatusCd, TmbR.BusnPartTmMbrRoleEffDt, TmbR.UpdateDt, TmbR.UpdateId, TmbR.AppId, TmbR.FunctionId FROM [Edge].[dbo].[tblBusnPartTmMbr] As Tmb INNER JOIN [Edge].[dbo].[tblBusnPartTmMbrRole] As TmbR ON Tmb.BusnPartTmMBRId=TmbR.BusnPartTmMBRId INNER JOIN (SELECT B.BusnPartTmId,C.BusnPartTmRoleId,MAX(C.BusnPartTmMbrRoleEffDt) MaxData FROM [Edge].[dbo].[tblBusnPartTmMbr] B INNER JOIN [Edge].[dbo].[tblBusnPartTmMbrRole] C ON B.BusnPartTmMBRId=C.BusnPartTmMBRId GROUP BY B.BusnPartTmId,C.BusnPartTmRoleId) B ON TMB.BusnPartTmId=B.BusnPartTmId AND TmbR.BusnPartTmRoleId=B.BusnPartTmRoleId AND ((TmbR.BusnPartTmMbrRoleEffDt=B.MaxData AND TmbR.BusnPartTmMbrRoleStatusCd=2) OR (TmbR.BusnPartTmMbrRoleEffDt <> B.MaxData AND TmbR.BusnPartTmMbrRoleStatusCd=1)) Execution plan: |–Parallelism(Gather Streams) |–Filter(WHERE<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Tmb].[BusnPartTmId]=.[BusnPartTmId])) |–Bookmark Lookup(BOOKMARK<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Bmk1003]), OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[EDGE].[dbo].[tblBusnPartTmMbr] AS [Tmb])) |–Nested Loops(Inner Join, OUTER REFERENCES<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[TmbR].[BusnPartTmMbrId]) WITH PREFETCH) |–Filter(WHERE<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />([TmbR].[BusnPartTmMbrRoleEffDt]=[Expr1002] AND [TmbR].[BusnPartTmMbrRoleStatusCd]=2) OR ([TmbR].[BusnPartTmMbrRoleEffDt]<>[Expr1002] AND [TmbR].[BusnPartTmMbrRoleStatusCd]=1))) | |–Hash Match(Aggregate, HASH<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[TmbR].[BusnPartTmMbrRoleId], .[BusnPartTmId]), RESIDUAL<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[TmbR].[BusnPartTmMbrRoleId]=[TmbR].[BusnPartTmMbrRoleId] AND .[BusnPartTmId]=.[BusnPartTmId]) DEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Expr1002]=MAX([C].[BusnPart | |–Parallelism(Repartition Streams, PARTITION COLUMNS<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[TmbR].[BusnPartTmMbrRoleId], .[BusnPartTmId])) | |–Hash Match(Inner Join, HASH<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[C].[BusnPartTmMbrId])=(.[BusnPartTmMbrId])) | |–Bitmap(HASH<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[C].[BusnPartTmMbrId]), DEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Bitmap1007])) | | |–Parallelism(Repartition Streams, PARTITION COLUMNS<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[C].[BusnPartTmMbrId])) | | |–Hash Match(Inner Join, HASH<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[TmbR].[BusnPartTmRoleId])=([C].[BusnPartTmRoleId])) | | |–Bitmap(HASH<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[TmbR].[BusnPartTmRoleId]), DEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Bitmap1006])) | | | |–Parallelism(Repartition Streams, PARTITION COLUMNS<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[TmbR].[BusnPartTmRoleId])) | | | |–Table Scan(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[EDGE].[dbo].[tblBusnPartTmMbrRole] AS [TmbR]), WHERE<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[TmbR].[BusnPartTmMbrRoleStatusCd]=2 OR [TmbR].[BusnPartTmMbrRoleStatusCd]=1)) | | |–Parallelism(Repartition Streams, PARTITION COLUMNS<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[C].[BusnPartTmRoleId]), WHERE<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />PROBE([Bitmap1006])=TRUE)) | | |–Table Scan(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[EDGE].[dbo].[tblBusnPartTmMbrRole] AS [C])) | |–Parallelism(Repartition Streams, PARTITION COLUMNS<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />.[BusnPartTmMbrId]), WHERE<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />PROBE([Bitmap1007])=TRUE)) | |–Table Scan(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[EDGE].[dbo].[tblBusnPartTmMbr] AS )) |–Index Seek(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[EDGE].[dbo].[tblBusnPartTmMbr].[X PKBusnPartTmMbr] AS [Tmb]), SEEK<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Tmb].[BusnPartTmMbrId]=[TmbR].[BusnPartTmMbrId]) ORDERED FORWARD) I need help from you all gurus, to find out what is really causing the query to run for long. I want to tweak the above query to get an optimized plan. Please help me in this…! RoyalSher. ********* The world is the great gymnasium where we come to make ourselves strong.

Try adding this at the very end, after the GROUP BY clause: OPTION (MAXDOP 1) Might or might not improve execution time.

Plus the various Table Scans suggest that the indexes may not be ideal. Try feeding the query to the Index Tuning Wizard, see if it brings any workable suggestions.

did you check the query execution paln —————————————-
http://dineshasanka.blogspot.com/

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by dineshasanka did you check the query execution paln<hr height="1" noshade id="quote"></blockquote id="quote">Well, he was smart enough to generate one.[<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />]

from the lack a where clause, i am assuming RoyalSher meant to do table scans, large hash joins should benefit from parallel plans, assuming there actually are more than 1 physical cores.
if so, compare MAXDOP 1 & 2, but no more than the number of physical procs
on these queries, the WITH(NOLOCK) helps, since i doubt you are concerned with transactions in progress for this. also, take another look at the execution plan details, put the mouse over each of the hash joins, is there a non-zero estimated IO cost for the hash operations?
if so, the tempdb data files needs to be place properly I suspect your query is probably going to disk, is your CPU maxed out during this query?
if not, you may need a better disk system, see my post on the hardware section

]]>