Post reply

this query is taking time for execution how to trouble shoot

  • April 13, 2013 at 12:40 am

    #271056

    HI every one application team informed that this query is taking more time than normal so from my end what should i do?

    select xfrq.bay_user_id,xfrq.fr_acid,cusr.c_m_phone_no,cusr.c_email_id,xfrq.txn_amt,xfrq.end_date

    from xfrq,cusr where xfrq.bay_user_id = cusr.bay_user_id

    and xfrq.r_mod_id = 'FTONLDISP'and

    datepart(dd,xfrq.r_mod_time)=13

    and

    datepart(mm,xfrq.r_mod_time)=04

    and

    datepart(yyyy,xfrq.r_mod_time)=2013

    and

    xfrq.res_code=000

    Thanks
    Naga.Rohitkumar

  • April 13, 2013 at 12:57 pm

    #1606259

    The biggest problem with the current query is that it's not possible for an INDEX SEEK (and subsequent range scan) to occur on the r_mod_time column because of the formulas you have wrapped around the column. This is how to fix that as well as impart what most people would be some "best practices". Of course, you have to have the correct indexes, as well.

    SELECT x.bay_user_id

    , x.fr_acid

    , c.c_m_phone_no

    , c.c_email_id

    , x.txn_amt,

    , x.end_date

    FROM dbo.cusr c

    JOIN dbo.xfrq x ON x.bay_user_id = c.bay_user_id

    WHERE x.r_mod_id = 'FTONLDISP'

    AND x.res_code = 0

    AND x.r_mod_time >= '20130413' AND x.r_mod_time < '20130414'

    ;

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply