UPDATE: Optimization Help!? stuck 2 days now...

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

UPDATE: Optimization Help!? stuck 2 days now...

Author

Topic

andrewz00
Starting Member

15 Posts

Posted - 2009-10-23 : 08:55:02

Im not sure exactly whats going wrong with this.. but i let it run for over an hour and a half with no results before i shut it down...
can someone see if they can tell me where its getting hung up?

Select
D.DonorCode,
[date] = min(D.DateDOnatioNStart)
Into #tempFTD
from btDonation D with (nolock)
where D.Deleted=0
Group By D.Donorcode

SELECT distinct
dm.datedrive,
Dm.DonationSiteCode,
[HS] = (case when Tc.Industry like '%high school%' then 'HS' else 'Non-HS' end),
[Region] = left(DM.DonationSiteCode,1)
INto #TempDrive
from dbextract.dbo.HSDriveDetails DM with (nolock)
INNER JOIN dbextract.dbo.HSTradeCode TC with (nolock)
ON Dm.TradeCode = TC.sic
AND DM.DateDRive between '5/4/2009' and '10/13/2009'
AND Dm.StatusTExt ='Complete'
and TC.Industry like '%high School%'

SELECT
Drive.Region,
Demo.Age,
Demo.Ethnicity,
Demo.Gender,
(case when First.DOnorCode is not null then 'FTD' else '' end),
count(*),
count(case when D.PhlebotomyCode <> '*' then D.DonorCode else null end),
sum(cast(isnull(R.Mild,0)as int)),
sum(cast(isnull(R.Moderate,0)as int)),
sum(cast(isnull(R.Severe,0)as int))

from btDonation D with (nolock)
INNER JOIN drmDemoGraphics Demo with (nolock)
ON D.KeyDOn = Demo.KeyDOn
AND D.Deleted=0
AND Demo.Age >=16 -- between 16 and 19
AND D.PhlebotomyCode in('A','D','*')

LEFT JOIN ctReaction R with (nolock)
ON D.KeyDon = R.KeyDon
and R.Pre = 0

/*Drive Dates*/
INNER JOIN #tempDRive Drive with (nolock)
on D.DonationSiteCode = Drive.DOnationSiteCode
ANd cast(convert(varchar(12),D.dateDonationStart,101)as datetime) = Drive.DateDRive

/*First Time Donor Dates*/
LEFT JOIN #TempFTD First with (nolock)
ON D.DonorCode = First.DOnorCode
and D.DateDonationStart = First.Date

GROUP BY
Drive.Region,
Demo.Age,
Demo.Ethnicity,
Demo.Gender,
(case when First.DOnorCode is not null then 'FTD' else '' end)

UPDATE: so i took out ..

LEFT JOIN #TempFTD First with (nolock)
ON D.DonorCode = First.DOnorCode
and D.DateDonationStart = First.Date

this created a table with over 3.4MM records... which i NEED but without it it runs in about a min.... any idea on how to fix this? can you index a temp table? i really do need this in here...

asgast
Posting Yak Master

149 Posts

Posted - 2009-10-23 : 09:55:04

yes you can index #temp tables just as standard ones
you can't index @temp tables

Can't fall asleep, the ghost of sql server will get me for writing bad queries. Have to stay awake ...

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-10-23 : 11:15:53

Yes use index on #temp tables as asgast says!
do you need 3.4M records?
why are you using nolock on #temp tables
can you possibly use views instead of hitting the tables directly. If you created views you will not have to do all those on the conversions of fields such as
cast(convert(varchar(12),D.dateDonationStart,101)as datetime)
count()
case etc. handle all those in a view then user the view
those will kill you I believe especially for 3.4M records

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources