March 15, 2011 at 10:16 am
This particular query runs less than optimal in our development environments, but at least runs without pegging the CPU; roughly 16 seconds. We just upgraded our production environment to SQL2008 and this query when running pegs all CPUs at 100% until completed; roughly 1 minutes 30 seconds.
1. It is the hash join that pegs the CPU. Any suggestions?
1. Any idea how this query could be written more efficient. I am struggling with the NOT IN statements. I know I could possibly do a LEFT JOIN with IS NULLS, but not sure how to handle the WHERE clause within the NOT IN statements
select distinct hm.* FROM
HOMaster hm with (nolock)
INNER JOIN PLMaster pl on pl.PolicyID = hm.PolicyID and pl.inceptiondate = hm.inceptiondate
INNER join PLHO41 plh on plh.policyid = hm.policyid and plh.postdate = pl.postdate AND plh.inceptiondate = hm.inceptiondate
WHERE Left(hm.PolicyId, 1) <> 'Q'
And hm.PostDate < '3/15/2011'
And pl.CompanyID = '000200'
and hm.CodeDesc In ('DNOC','NONRENEW','RENEWALDNOC','UNDERWRITING', 'Dec Page')
And hm.Code = 'CN'
And hm.Copy2PrintDate = '1/1/1900'
And hm.Copy2Name <> ' '
and hm.policyid
not in
(select hm.policyid from homaster
inner join PLNotice pln on pln.PolicyID = hm.PolicyID and pln.InceptionDate = hm.InceptionDate
WHERE hm.CodeDesc ='UNDERWRITING' and pln.noticetype = 'UW')
and hm.policyid
not in (select hm.policyid from homaster inner join PLNotice pln on pln.PolicyID = hm.PolicyID and pln.InceptionDate = hm.InceptionDate
WHERE hm.CodeDesc = 'NONRENEW' and pln.noticetype = 'NR') and hm.policyid
not in (select hm.policyid from homaster inner join PLNotice pln on pln.PolicyID = hm.PolicyID and pln.InceptionDate = hm.InceptionDate
WHERE hm.CodeDesc In ('RENEWALDNOC','DNOC') and pln.noticetype = 'D')
March 15, 2011 at 10:56 am
you try this...
[/code]
left join PLNotice pln on pln.PolicyID = hm.PolicyID and pln.InceptionDate = hm.InceptionDate
and (
(hm.CodeDesc ='UNDERWRITING' and pln.noticetype = 'UW')
or
(hm.CodeDesc = 'NONRENEW' and pln.noticetype = 'NR')
or
(hm.CodeDesc In ('RENEWALDNOC','DNOC') and pln.noticetype = 'D')
)
[/code]
It is a pretty nasty join and it might actually end up being slower.
Another option would be to create a temp table with the exceptions so you could join to that instead. My guess is the inserts to the temp table and then joining to that would be better.
It is pretty hard to tell what will and what wont help without any ddl or data.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 15, 2011 at 11:13 am
Please post table definitions, index definitions and execution plan, as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
If there are nulls around (or the column is nullable), consider not exists rather than not in.
http://sqlinthewild.co.za/index.php/2010/02/18/not-exists-vs-not-in/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 15, 2011 at 11:22 am
I have rewritten the query to perform better, but the next question I am being required to answer is why it performs OK (I wouldn't call 23 seconds ago) in our development environments, but completely pegs the CPU in our production environment.
Development environments are all 4GB, Windows Server 2008 x64, SQL Server 2008 64 bit running on VMWare ESX3.5
Production environment is 8GB, same OS, same SQL version, Quad Core
March 15, 2011 at 11:32 am
Smaller data volume?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 15, 2011 at 11:38 am
try to avoid "where not in..." and "where <> X.." that stuff can really result in inefficient queries especially on large tables. That stuff indicates problematic database and/or logic design in my opinion.
Also try to avoid functions in your where clause. Instead of :
WHERE Left(hm.PolicyId, 1) <> 'Q'
Use:
WHERE hm.PolicyID not like 'Q%'
The probability of survival is inversely proportional to the angle of arrival.
March 15, 2011 at 12:07 pm
Can you post both execution plans ? (show actual execution plan and save the .sqlplan)
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