Sql Server Programming :: SQL Query taking 4 Mins to run


	SQL Query taking 4 Mins to run I have this query: SELECT (SELECT MAX(AsOf) FROM dbo.t_CCY_Positions) AS AsOf, S.System, P.SEI_Account_ID, C.CCY_Cd, C.CCY_Name, IsNull(TranBal.TranAmt,0) AS TranAmt, P.Shares AS SEIBalance, Cast(P.Shares - IsNull(TranBal.TranAmt,0) AS Money) AS Diff FROM dbo.t_CCY_Positions AS P INNER JOIN dbo.t_CCY_Systems AS S ON P.SystemID = S.System_ID INNER JOIN dbo.t_CCY_CCYCodes AS C ON P.ISO_CCY_Cd = C.ISO_Cd LEFT JOIN (SELECT System_ID, SEIAcctNumber, ISO_CCY_Cd, SUM(Amount) AS TranAmt FROM dbo.t_CCY_Trans WHERE (Suppressed = 0) AND (Match_ID IS NULL) AND (System_ID IN (2,4)) GROUP BY System_ID, SEIAcctNumber, ISO_CCY_Cd) AS TranBal ON (P.SEI_Account_ID = TranBal.SEIAcctNumber AND P.ISO_CCY_Cd = TranBal.ISO_CCY_Cd) WHERE (P.AsOf IN (SELECT MAX(AsOf) FROM dbo.t_CCY_Positions)) AND (P.SystemID IN (2,4)) AND --Eliminate rounding diffs ((C.CCY_Cd = 'JPY' AND (P.Shares - IsNull(TranBal.TranAmt,0) > 1 OR P.Shares - IsNull(TranBal.TranAmt,0) < -1)) OR (C.CCY_Cd <> 'JPY' AND (P.Shares - IsNull(TranBal.TranAmt,0) > .1 OR P.Shares - IsNull(TranBal.TranAmt,0) < -.1))) Any suggestions as to why this is running so long? Some times it will take 30 Secs, other times it will take 4 Mins. Could be a million things....check your explain plan....are you doing a lot of index seeks or index scans?...That left join on that derived table could also be slowing things down...Also try to avoid <> ... Jim -----------------------------------------------Reply----------------------------------------------- On Jun 5, 2:37 pm, Jimbo <jamesfer@hotmail.com> wrote: > Could be a million things....check your explain plan....are you doing > a lot of index seeks or index scans?...That left join on that derived > table could also be slowing things down...Also try to avoid <> ... > Jim The execution plan show 52% on Bookmark lookup, and 20% on Hash Match. Also I changed the <> to NOT C.CCY_Cd = 'JPY' -----------------------------------------------Reply----------------------------------------------- That bookmark lookup seems to be the culprit...as a rule of thumb youre gonna want to avoid these...look at the plan and see what column(s) is causing this...look at the index and see if you need to create a covering index or create a clustered index for these fields...especially if this query is being used alot. -Jim -----------------------------------------------Reply----------------------------------------------- Try re-casting as a UNION: select * from MyTable where MyCol < 'JPY' union select * from MyTable where MyCol > 'JPY' -- Tom ---------------------------------------------------- Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS SQL Server MVP Toronto, ON Canada https://mvp.support.microsoft.com/profile/Tom.Moreau "Bill Schanks" <wscha@gmail.com> wrote in message news:1181072908.637189.173280@p47g2000hsd.googlegroups.com... On Jun 5, 2:37 pm, Jimbo <jamesfer@hotmail.com> wrote: > Could be a million things....check your explain plan....are you doing > a lot of index seeks or index scans?...That left join on that derived > table could also be slowing things down...Also try to avoid <> ... > Jim The execution plan show 52% on Bookmark lookup, and 20% on Hash Match. Also I changed the <> to NOT C.CCY_Cd = 'JPY' -----------------------------------------------Reply----------------------------------------------- Thanks for all the advice ... I created some add'l indexes and it's running in 10-15 Seconds now. That is acceptable, as it on a report that will be run once a day. -----------------------------------------------Reply----------------------------------------------- One additional point to try. You have SELECT MAX(AsOf) FROM dbo.t_CCY_Positions in the query twice. Consider declaring a variable and putting this max into it and using the variable in the code instead of the actual statement. -- TheSQLGuru President Indicium Resources, Inc. "Bill Schanks" <wscha@gmail.com> wrote in message news:1181071701.709900.272700@m36g2000hse.googlegroups.com... - Hide quoted text - >I have this query: > SELECT > (SELECT MAX(AsOf) FROM dbo.t_CCY_Positions) AS AsOf, > S.System, > P.SEI_Account_ID, > C.CCY_Cd, > C.CCY_Name, > IsNull(TranBal.TranAmt,0) AS TranAmt, > P.Shares AS SEIBalance, > Cast(P.Shares - IsNull(TranBal.TranAmt,0) AS Money) AS Diff > FROM > dbo.t_CCY_Positions AS P > INNER JOIN dbo.t_CCY_Systems AS S ON P.SystemID = S.System_ID > INNER JOIN dbo.t_CCY_CCYCodes AS C ON P.ISO_CCY_Cd = C.ISO_Cd > LEFT JOIN (SELECT System_ID, SEIAcctNumber, ISO_CCY_Cd, SUM(Amount) > AS TranAmt > FROM dbo.t_CCY_Trans WHERE (Suppressed = 0) AND (Match_ID IS NULL) > AND (System_ID IN (2,4)) GROUP BY System_ID, SEIAcctNumber, > ISO_CCY_Cd) > AS TranBal ON (P.SEI_Account_ID = TranBal.SEIAcctNumber AND > P.ISO_CCY_Cd = TranBal.ISO_CCY_Cd) > WHERE > (P.AsOf IN (SELECT MAX(AsOf) FROM dbo.t_CCY_Positions)) AND > (P.SystemID IN (2,4)) AND > --Eliminate rounding diffs > ((C.CCY_Cd = 'JPY' AND (P.Shares - IsNull(TranBal.TranAmt,0) > 1 OR > P.Shares - IsNull(TranBal.TranAmt,0) < -1)) > OR > (C.CCY_Cd <> 'JPY' AND (P.Shares - IsNull(TranBal.TranAmt,0) > .1 OR > P.Shares - IsNull(TranBal.TranAmt,0) < -.1))) > Any suggestions as to why this is running so long? Some times it will > take 30 Secs, other times it will take 4 Mins.