SQL Server Performance Forum – Threads Archive
Is anything wrong with these two querys?
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)
Did you execute those queries?
Did you get any error? Madhivanan Failing to plan is Planning to fail
CDAYS(C.orig_advance_date, TODAY) <= 150 That doesn’t look like valid T-SQL code to me. SQL Server doesn’t know a build-in CDAYS functions. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
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
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.
For missing brackets, simple: the number of ( must be the same as the number of ).
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.
Check your NOLOCK syntax; you may need to add a WITH keyword in them
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
]]>