Home     |     .Net Programming    |     cSharp Home    |     Sql Server Home    |     Javascript / Client Side Development     |     Ajax Programming

Ruby on Rails Development     |     Perl Programming     |     C Programming Language     |     C++ Programming     |     IT Jobs

Python Programming Language     |     Laptop Suggestions?    |     TCL Scripting     |     Fortran Programming     |     Scheme Programming Language


 
 
Cervo Technologies
The Right Source to Outsource

MS Dynamics CRM 3.0

Sql Server Programming

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...

Add to del.icio.us | Digg this | Stumble it | Powered by Megasolutions Inc