March 1, 2011 at 2:26 pm
Very fortunate to work with normalized data in the past. Not so lucky here. The developers obviously didn't understand the use of JOINS and have used subqueries everywhere. Starting with this one. Horrible performance.
Select @rundate, pm.companyid, pm.policyid, pm.inceptiondate, pm.postdate,
pt.effectivedate, pt.accountingdate, pm.transactiontype, Pm.originaltranstype,
0, 3
from plmaster pm, pltran pt
where pm.originaltranstype = 'rn'
and pm.transactiontype in ('RN', 'CN', 'RI')
and pm.inceptiondate between @begindate and @enddate
and pm.policyid = pt.policyid
and pm.inceptiondate = pt.inceptiondate
and pm.postdate = pt.postdate
and pm.policyid + ' ' + convert(varchar, pm.inceptiondate, 101) NOT IN
(select policyid + ' ' + convert(varchar, inceptiondate, 101)
from artran
where batchdate <= @enddate
and (paymenttype = 1
or (paymenttype = 2 or paymentamt > 20)))
IF I change to the following I get the same number of results. I can't wrap my head around what happens by leaving of the WHERE clause that is within that orignal query. Can anyone explain.
Select @rundate, pm.companyid, pm.policyid, pm.inceptiondate, pm.postdate,
pt.effectivedate, pt.accountingdate, pm.transactiontype, Pm.originaltranstype,
0, 2
from plmaster pm
INNER JOIN pltran pt on pm.PolicyID = pt.PolicyID AND pm.InceptionDate = pt.InceptionDate AND pm.PostDate = pt.PostDate
LEFT JOIN artran ar ON pm.policyid = ar.PolicyID AND pm.inceptiondate = ar.inceptiondate
where pm.originaltranstype = 'rn'
and pm.transactiontype in ('RN', 'CN', 'RI')
and pm.inceptiondate between @begindate and @enddate
and pm.policyid = pt.policyid
and pm.inceptiondate = pt.inceptiondate
and pm.postdate = pt.postdate
and ar.PolicyID IS NULL AND ar.InceptionDate IS NULL
March 1, 2011 at 3:02 pm
Wow. That is some plain ugly looking TSQL.
Having said that, I would have done exactly what you did, if for no other reason than to sustain my own sanity, but mainly in hopes that it would provide better performance. (there are are those willing to debate my level of sanity... or lack thereof... but I digress.)
I can not see anything obvious as to why it should not work as expected, but I don't know what the data looks like or what is or isn't null-able so there could be some issues that don't work out (like those concatenations in the where clause).
The probability of survival is inversely proportional to the angle of arrival.
March 1, 2011 at 3:03 pm
I didn't dig through the entirety of your problem, but you cured the most painful component which seems to be your concern, which is this:
and pm.policyid + ' ' + convert(varchar, pm.inceptiondate, 101) NOT IN
(select policyid + ' ' + convert(varchar, inceptiondate, 101)
from artran
where batchdate <= @enddate
and (paymenttype = 1
or (paymenttype = 2 or paymentamt > 20)))
Notice the concatonation. In your query, you didn't concatonate these, but used the column joins directly. It's an attribute nicknamed SARGability, for Search Agumentability. The concatonation blew away any hope of index usage and forced a scan of both tables. By removing that and connecting on the two columns properly, you've increased your ability to seek the data instead of scan, reducing what needed to be processed.
If you look at the two execution plans of the query, my guess is you'll notice a major difference in some of the table data lookups.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
March 1, 2011 at 3:03 pm
Looks like you probably have a bunch of inceptiondate values that are null.
It also looks like your policyids are null in quite a few instances thus making the queries work about the same. It seems like it boils down to data to me.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 1, 2011 at 3:11 pm
Imagine working with T-sql that looks like this everywhere I turn.....Aaaarrrggghhh
Yes what I changed it to does work. Without playing with indexes I go from 25 minutes to seconds, but what I can't figure out is how the following enters into the statement. If you look at my original re-worked SQL I took it out. If I throw it back in there I get 0 records.
select policyid + ' ' + convert(varchar, inceptiondate, 101)
from artran
where batchdate <= @enddate
and (paymenttype = 1
or (paymenttype = 2 or paymentamt > 20)))
March 2, 2011 at 7:16 am
Just because the original query was written that way (sheesh .. and used for how long?) doesn't mean it was ever correct.
The probability of survival is inversely proportional to the angle of arrival.
March 2, 2011 at 7:29 am
how about:
Select @rundate
, pm.companyid
, pm.policyid
, pm.inceptiondate
, pm.postdate
, pt.effectivedate
, pt.accountingdate
, pm.transactiontype
, Pm.originaltranstype
, 0
, 2
from plmaster pm
INNER JOIN pltran pt
on pm.PolicyID = pt.PolicyID
AND pm.InceptionDate = pt.InceptionDate
AND pm.PostDate = pt.PostDate
LEFT JOIN artran ar
ON pm.policyid = ar.PolicyID
AND pm.inceptiondate = ar.inceptiondate
/* extra conditions that were used in the NOT IN Query */
and batchdate <= @enddate
and ( paymenttype in (1, 2)
or paymentamt > 20
)
where pm.originaltranstype = 'rn'
and pm.transactiontype in ( 'RN', 'CN', 'RI' )
and pm.inceptiondate >= @begindate
and pm.inceptiondate <= @enddate
and ar.PolicyID IS NULL
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply