June 10, 2011 at 9:15 am
I am looking for a performance boost from this query, as the client wants to pull a year's worth of data instead of a month's worth. Any help is appreciated. Thank you:
SELECTReasonCode,
SUM(CASE WHEN M2.MROResult LIKE 'POSITIVE%' AND (M2.SubstanceDescription LIKE '%AMPHETAMINE%') THEN Stats ELSE 0 END) AS [AMP],
SUM(CASE WHEN M2.MROResult LIKE 'POSITIVE%' AND (M2.SubstanceDescription LIKE '%COCAINE%' OR M2.SubstanceDescription LIKE '%BENZOYLECGONINE%' OR M2.SubstanceDescription LIKE '%COCAETHYLENE%') THEN Stats ELSE 0 END) AS [COC],
SUM(CASE WHEN M2.MROResult LIKE 'POSITIVE%' AND (M2.SubstanceDescription LIKE '%OPIATE%' OR M2.SubstanceDescription LIKE '%CODEINE%' OR M2.SubstanceDescription LIKE '%OXYCODONE%' OR M2.SubstanceDescription LIKE '%OXYMORPHONE%' OR M2.SubstanceDescription LIKE '%HYDROCODONE%' OR M2.SubstanceDescription LIKE '%HYDROMORPHONE%' OR M2.SubstanceDescription LIKE '%MORPHINE%') THEN Stats ELSE 0 END) AS [OPI],
SUM(CASE WHEN M2.MROResult LIKE 'POSITIVE%' AND (M2.SubstanceDescription LIKE 'PCP%' OR M2.SubstanceDescription LIKE 'PHENCYCLIDINE%') THEN Stats ELSE 0 END) AS [PCP],
SUM(CASE WHEN M2.MROResult LIKE 'POSITIVE%' AND (M2.SubstanceDescription LIKE 'THC%' OR M2.SubstanceDescription LIKE '%MARIJUANA%' OR M2.SubstanceDescription LIKE 'CANNABINOID%') THEN Stats ELSE 0 END) AS [THC],
SUM(CASE WHEN M2.MROResult LIKE 'POSITIVE%' AND (M2.SubstanceDescription LIKE 'BENZODIAZEPINE%' OR M2.SubstanceDescription LIKE '%ALPRAZOLAM%' OR M2.SubstanceDescription LIKE '%NORDIAZEPINE%' OR M2.SubstanceDescription LIKE '%OXAZEPAM%' OR M2.SubstanceDescription LIKE '%TEMAZEPAM%' OR M2.SubstanceDescription LIKE '%DESMETHYLDIAZEPAM%' OR M2.SubstanceDescription LIKE '%NORDIAZEPAM%') THEN Stats ELSE 0 END) AS [BEZ],
SUM(CASE WHEN M2.MROResult LIKE 'POSITIVE%' AND (M2.SubstanceDescription LIKE '%BARBITURATE%' OR M2.SubstanceDescription LIKE '%BUTALBITAL%') THEN Stats ELSE 0 END) AS [BRB],
SUM(CASE WHEN M2.MROResult LIKE 'POSITIVE%' AND (M2.SubstanceDescription LIKE '%METHADONE%') THEN Stats ELSE 0 END) AS [MTD],
SUM(CASE WHEN M2.MROResult LIKE 'POSITIVE%' AND (M2.SubstanceDescription LIKE '%PROPOXYPHENE%') THEN Stats ELSE 0 END) AS [PRO],
SUM(CASE WHEN M2.MROResult LIKE 'POSITIVE%' AND (M2.SubstanceDescription LIKE '%COTININE%') THEN Stats ELSE 0 END) AS [CTN],
SUM(CASE WHEN M2.MROResult LIKE 'POSITIVE%' AND (M2.SubstanceDescription LIKE '%ALCOHOL%' OR M2.SubstanceDescription LIKE '%ETHYL%') THEN Stats ELSE 0 END) AS [ETH],
SUM(CASE WHEN M2.MROResult LIKE 'POSITIVE%' AND (M2.SubstanceDescription LIKE '%MDMA%' OR M2.SubstanceDescription LIKE '%MDA%' OR M2.SubstanceDescription LIKE '%ECSTASY%') THEN Stats ELSE 0 END) AS [MDA],
SUM(CASE WHEN M2.MROResult LIKE 'POSITIVE%' AND (M2.SubstanceDescription LIKE '%METHADONE%') THEN Stats ELSE 0 END) AS [MTD],
SUM(CASE WHEN M2.MROResult LIKE 'POSITIVE%' AND (M2.SubstanceDescription IN ('THC%','%MARIJUANA%','CANNABINOID%','%COCAINE%','%BENZOYLECGONINE%','%COCAETHYLENE%','%AMPHETAMINE%','PCP%','PHENCYCLIDINE%','BENZODIAZEPINE%','%ALPRAZOLAM%','%NORDIAZEPINE%','%OXAZEPAM%','%TEMAZEPAM%','%DESMETHYLDIAZEPAM%','%NORDIAZEPAM%','%OPIATE%','%CODEINE%','%OXYCODONE%','%OXYMORPHONE%','%HYDROCODONE%','%HYDROMORPHONE%','%MORPHINE%','%BARBITURATE%','%BUTALBITAL%','%MDMA%','%MDA%','%ECSTASY%','%PROPOXYPHENE%','%METHADONE%','%ALCOHOL%','%ETHYL%','%COTININE%')) THEN Stats ELSE 0 END) AS [OTH],
SUM(CASE WHEN M2.MROResult LIKE 'POSITIVE%' THEN Stats ELSE 0 END) AS [TOT]
FROM MainTestResult M1 INNER JOIN IndividualDrugTestResult M2
ON M1.UniqueID = M2.UniqueID
WHERE M1.UniqueID <> ''
AND (ClientAccountNumber = 'abcdefghijklmnopqrstuvwxyz'
OR ClientAccountNumber IN (SELECT LabAccountID
FROM EmailLabLink
WHERE EmailID = 1563))
AND M2.ShowOnReport = 1
AND M1.MROStatus = 'Completed'
AND Active = 1 AND Stats = 1
AND CollectionDateTime BETWEEN '2011-05-01 00:00:00' AND '2011-06-01 00:00:00'
AND ReportLayoutID NOT IN (3,4,5,6,11,14,16,17,18)
GROUP BY ReasonCode
-CK
June 10, 2011 at 9:21 am
Not only would DDL scripts be good for table creation and sample data, it would help in determining what needs to be done to potentially increase performance.
Also, I have a feeling there is more to this than just the script. Is this in a procedure? are there variables passed to it? or do you just run this query and hardcode values (acct#, dates, etc.)
June 10, 2011 at 9:28 am
Technically SQL Server sees it as hard coded, though a few of the dates and ID's are passed in as variables via an application server. (Cold Fusion) This is not a stored procedure. The MainTestResults table has over 1MM records in it and good indexes. It just seems to take 2-3 minues to return a result set.
Thank you.
-CK
June 10, 2011 at 9:43 am
There are a lot of moving parts to this script. The case statements with in the sum will cause a heavier io cost but having 15 of them will deffinetly be costly. you might try breaking them down into individual peaces and them bring them together after the fact. write the values out to a temp table and then used a left outer join. you could eliminate the case statements for each sum and move the criteria to a where clause. Then you could use and is null since the case is either stats or 0. you would have to play with it but I suspect it would overall be faster even though the code would look a tad more bulcky.
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
June 10, 2011 at 9:51 am
Thanks Dan. Basically all these moving parts are to just create a crosstab result set similar to this:
ANNUAL DOT000000000000000
ANNUAL PHYSICAL000000000000000
BACK UP DRIVER000000000000000
BACK UP DRIVER APPLI000000000000000
BACK UP DRIVER REQUIRED000000000000000
BKUP DRIVER000000000000000
CHANGE OF JOB000000000000000
CHANGE OF POSITION000000000000000
DELIVERY DRIVER CDL000000000000000
DOT000000000000000
DOT CARD000000000000000
DOT CERT000000000000000
DOT DRIVER,PREEMPLOYMENT000000000000000
DOT DRUG SCREEN000000000000000
DOT LICENSE RECERTIFICATION000000000000000
DOT PHYSICAL000000000000000
DOT RECERT000000000000000
DOT RENEW000000000000000
DRIVER CERT000000000000000
DRIVER CERTIFICATION000000000000000
FOLLOW UP TEST000000000000000
MEDICAL CARD000000000000000
MEDICAL CERITIFICATION RENEWAL000000000000000
MISSING FROM FILE000000000000000
NEW CERT000000000000000
NEW CERTIFICATION000000000000000
NEW DRIVER000000000000000
NEW DRIVER POSITION000000000000000
NEW POSITION REQUIRES DOT DS000000000000000
NOT PROVIDED000000000000000
OFF ROAD INCIDENT000000000000000
PERIODIC000000000000000
PHYSICAL000000000000000
POST ACCIDENT000000000000000
PRE-EMPLOYMENT100020000000003
PRE-EMPLOYMENT,DOT-NEW DRIVER000000000000000
PREEMPLOYMENT,DOT NEW DRIVER000000000000000
PROMOTION000000000000000
PROMOTION TO DRIVIER000000000000000
PROPERTY DAMAGE000000000000000
RANDOM000010000000001
RE CERT000000000000000
RE CERTIFICATION000000000000000
RE-CERT000000000000000
RECERT000000000000000
RENEWAL000000000000000
TRANSFER000000000000000
UKNOWN000000000000000
VEHICLE CERTIFICATION000000000000000
YEARLY RX000000000000000
June 10, 2011 at 9:52 am
Please post the actual execution plan. Maybe we can spot something obvious.
I routinely run reports like this with 10 - 20 months of data straight from prod oltp db with tables in the GBs and it runs WELLLLLL under 1 minute.
June 10, 2011 at 9:54 am
Please post table definitions, index definitions and execution plan, as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
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
June 10, 2011 at 10:04 am
Thanks for the link on how to post. Here is the SQL Execution plan.
-CK
June 10, 2011 at 10:47 am
Table definitions and index definitions please.
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
June 10, 2011 at 10:56 am
Okay this is going to sound stupid, but how do I export the table and index info you want from SQL Server 2005? Thank you.
June 10, 2011 at 11:01 am
Object explorer. Right click the table or index, script-> create
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
June 10, 2011 at 11:11 am
Got it, thanks. File attached.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply