SQL Server Performance

Is anything wrong with these two querys?

Discussion in 'T-SQL Performance Tuning for Developers' started by rdifolco, Feb 17, 2006.

  1. rdifolco New Member

    All,

    Is there anything wrong with these 2 querys at all? Syntex , ()....




    c.is_active = 1
    and
    (
    C.wh_loan_option = 1 and CDAYS(C.orig_advance_date, TODAY) <= 120
    or
    C.wh_loan_option = 0 and CDAYS(C.orig_pledge_date, TODAY) <= 120
    or
    C.product_code in
    (select c.product_code
    from collateral c(nolock)
    join product_type pt(nolock)
    on c.product_code = pt.product_code
    where pt.product_group_code = 'sec'
    and
    (CDAYS(C.orig_advance_date, TODAY) <= 150
    or
    CDAYS(C.orig_pledge_date, TODAY) <= 150)
    )

    )
    and exists
    (select 1 from loan L where c.loan_sak = L.loan_sak and L.curr_prin_balance > c.collateral_value)




    ---------------------------------------------------------------------------------------




    c.is_active = 1
    and
    (
    C.wh_loan_option = 1 and CDAYS(C.orig_advance_date, TODAY) <= 120
    or
    C.wh_loan_option = 0 and CDAYS(C.orig_pledge_date, TODAY) <= 120
    or
    (CDAYS(C.orig_advance_date, TODAY) <= 150
    or
    CDAYS(C.orig_pledge_date, TODAY) <= 150)
    and
    C.product_code in
    (select c.product_code
    from collateral c(nolock)
    join product_type pt(nolock)
    on c.product_code = pt.product_code
    where pt.product_group_code = 'sec'
    )
    )
    and exists
    (select 1 from loan L where c.loan_sak = L.loan_sak and L.curr_prin_balance > c.collateral_value)


  2. Madhivanan Moderator

    Did you execute those queries?
    Did you get any error?

    Madhivanan

    Failing to plan is Planning to fail
  3. FrankKalis Moderator

  4. Madhivanan Moderator

    Also TODAY should be replaced by Getdate()
    I think you need to make use of DateDiff

    Replace
    CDAYS(C.orig_advance_date, TODAY) <= 120

    by
    DateDiff(day,C.orig_advance_date, getdate()) <= 120

    Madhivanan

    Failing to plan is Planning to fail
  5. rdifolco New Member

    CDAYS is an internal macro function in the software that is translating the tsql.

    I am more or less looking to see if you can spot any syntax errors. Like missing () or the like.

    Thanks in advance.

    ps - Both work but I am being challenged by anotehr employee about this and want to make sure it is right on that aspect.
  6. Adriaan New Member

    For missing brackets, simple: the number of ( must be the same as the number of ).
  7. Adriaan New Member

    Other than that, we don't know the first thing about your macro language, so we have no idea what specific features of that macro syntax might be missing.
  8. Tahsin New Member

    Check your NOLOCK syntax; you may need to add a WITH keyword in them
  9. joechang New Member

    on performance matters
    1. the OR condition with search args on different columns can lead to a bad plan

    always compare wiht
    SELECT xx FROM Table WHERE ColA = X
    UNION ALL
    SELECT xx FROM Table WHERE ColB = Y
    etc

    use UNION ALL if there no duplicates from each of the search conditons, this is cheaper
    otherwise use UNION to eliminate duplicates

    2. the Search Arg inside a function also can cause poor performance

    WHERE C.orig_advance_date < DATEADD(dd,120,getdate())
    plus you need to take into account whether you only want to take into account whole days or the exact time

Share This Page