SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Query Performance
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

stwp86
Starting Member

USA
42 Posts

Posted - 10/10/2012 :  10:57:05  Show Profile  Reply with Quote
Any thoughts on how to improve the performance of the two following queries? I have already worked through the different types of table indexing and I saw pretty significant gains, so I figured this was the next step. Any thoughts are greatly appreciated, Thanks!


Select t.client_mnemonic, t.[Total Time], t.[Work Week], t.Group_Type, i.BarType, t.Transactions
FROM
(
SELECT n.Client_mnemonic,n.Transactions, round((([Total Elapsed Time (sec)]/WeeklyUserCount)/60.0),2) AS "Total Time" ,m.WeekMinDate as "Work Week", n.Group_Type
FROM
(
select sum([Distinct User Count by Day]) AS WeeklyUserCount,DATEADD(wk,DATEDIFF(wk,0,[Date]),0) AS weekMinDate, Group_Type 
from 
(
select count(distinct w.user_name) as "Distinct User Count by Day", convert(varchar(11), w.start_time) as "Date", Group_Type 
from workflow_data as w
WHERE
Client_mnemonic = (@ClientMnemonicParameter) and (Group_type = (@GroupSlicerOne)) and 
(
convert(date, start_time) between (@BeginDate) and (@EndDate)
) and duration <=1800 and
(
w.definition = 'Patient Search - Patient Search to Open Chart' and datepart(dw, w.Start_Time) not in (1,7) or
w.definition = 'Patient Lists - Patient List to Open Chart' and datepart(dw, w.Start_Time) not in (1,7) or
w.definition = 'Home View - Schedule Viewer to Open Chart' and datepart(dw, w.Start_Time) not in (1,7) or
w.definition = 'Message Center - Sign Document' and datepart(dw, w.Start_Time) not in (1,7) or
w.definition = 'Message Center - Refuse Document' and datepart(dw, w.Start_Time) not in (1,7) or
w.definition = 'Message Center - Modify Document' and datepart(dw, w.Start_Time) not in (1,7) or
w.definition = 'Message Center - Accept Proposal-Refill Orders' and datepart(dw, w.Start_Time) not in (1,7) or
w.definition = 'Message Center - Reject Proposal-Refill Orders' and datepart(dw, w.Start_Time) not in (1,7) or
w.definition = 'Message Center - Accept Cosign Orders' and datepart(dw, w.Start_Time) not in (1,7) or
w.definition = 'Message Center - Reject Cosign Orders' and datepart(dw, w.Start_Time) not in (1,7) or
w.definition = 'Message Center - Endorse Results FYI' and datepart(dw, w.Start_Time) not in (1,7) or
w.definition = 'Message Center - Refuse Results FYI' and datepart(dw, w.Start_Time) not in (1,7) or
w.definition = 'Message Center - Endorse Results' and datepart(dw, w.Start_Time) not in (1,7) or
w.definition = 'Message Center - Refuse Results' and datepart(dw, w.Start_Time) not in (1,7) or
w.definition = 'Message Center - Respond to Message' and datepart(dw, w.Start_Time) not in (1,7) or
w.definition = 'Message Center - Delete Message' and datepart(dw, w.Start_Time) not in (1,7) or
w.definition = 'Message Center - View Sent Items' and datepart(dw, w.Start_Time) not in (1,7) or
w.definition = 'Message Center - Create New Message-Reminder' and datepart(dw, w.Start_Time) not in (1,7)
)group by CONVERT(varchar(11), w.start_time), Group_Type 
)t
GROUP BY DATEADD(wk,DATEDIFF(wk,0,[Date]),0), Group_Type 
)m
INNER JOIN
(
SELECT 
Client_Mnemonic, CONVERT(varchar(50), (DATEADD(d, @@DATEFIRST - DATEPART(dw, w.Start_Time) - 5, w.Start_Time)),101) as WeekDate, 
(count(w.User_Name)*avg(w.duration)) as "Total Elapsed Time (sec)", Group_Type,count(workflow_id) as "Transactions"  
FROM workflow_data as w
WHERE
Client_mnemonic = (@ClientMnemonicParameter) and (Group_type = (@GroupSlicerOne)) and 
(
convert(date, start_time) between (@BeginDate) and (@EndDate)
) and duration <=1800 and
(
w.definition = 'Patient Search - Patient Search to Open Chart' and datepart(dw, w.Start_Time) not in (1,7) or
w.definition = 'Patient Lists - Patient List to Open Chart' and datepart(dw, w.Start_Time) not in (1,7) or
w.definition = 'Home View - Schedule Viewer to Open Chart' and datepart(dw, w.Start_Time) not in (1,7) or
w.definition = 'Message Center - Sign Document' and datepart(dw, w.Start_Time) not in (1,7) or
w.definition = 'Message Center - Refuse Document' and datepart(dw, w.Start_Time) not in (1,7) or
w.definition = 'Message Center - Modify Document' and datepart(dw, w.Start_Time) not in (1,7) or
w.definition = 'Message Center - Accept Proposal-Refill Orders' and datepart(dw, w.Start_Time) not in (1,7) or
w.definition = 'Message Center - Reject Proposal-Refill Orders' and datepart(dw, w.Start_Time) not in (1,7) or
w.definition = 'Message Center - Accept Cosign Orders' and datepart(dw, w.Start_Time) not in (1,7) or
w.definition = 'Message Center - Reject Cosign Orders' and datepart(dw, w.Start_Time) not in (1,7) or
w.definition = 'Message Center - Endorse Results FYI' and datepart(dw, w.Start_Time) not in (1,7) or
w.definition = 'Message Center - Refuse Results FYI' and datepart(dw, w.Start_Time) not in (1,7) or
w.definition = 'Message Center - Endorse Results' and datepart(dw, w.Start_Time) not in (1,7) or
w.definition = 'Message Center - Refuse Results' and datepart(dw, w.Start_Time) not in (1,7) or
w.definition = 'Message Center - Respond to Message' and datepart(dw, w.Start_Time) not in (1,7) or
w.definition = 'Message Center - Delete Message' and datepart(dw, w.Start_Time) not in (1,7) or
w.definition = 'Message Center - View Sent Items' and datepart(dw, w.Start_Time) not in (1,7) or
w.definition = 'Message Center - Create New Message-Reminder' and datepart(dw, w.Start_Time) not in (1,7)
)GROUP BY client_mnemonic, CONVERT(varchar(50), (DATEADD(d, @@DATEFIRST - DATEPART(dw, Start_Time) - 5, Start_Time)),101), Group_Type 
)n
ON n.WeekDate = m.WeekMinDate and n.group_type = m.group_type 
)t
inner join
(
Select [Work_Week], 
CASE WHEN Seq1 <= @BaselineDuration THEN 'Baseline' 
             ELSE 'Trending'
           END as "BarType"
FROM
(
SELECT CONVERT(varchar(50), (DATEADD(d, @@DATEFIRST - DATEPART(dw, Start_Time) - 5, Start_Time)),101) as "Work_Week",
ROW_NUMBER() OVER (ORDER BY CONVERT(varchar(50), (DATEADD(d, @@DATEFIRST - DATEPART(dw, Start_Time) - 5, Start_Time)),101)) AS Seq1
FROM workflow_data
where client_mnemonic = (@ClientMnemonicParameter) and group_type = @GroupSlicerOne
group by CONVERT(varchar(50), (DATEADD(d, @@DATEFIRST - DATEPART(dw, Start_Time) - 5, Start_Time)),101) 
)m
)i
ON t.[Work Week] = i.[Work_Week]
ORDER BY group_type,client_mnemonic,t.[Work Week]


This one is probably more important than the first, as I use this one more, but I assume that the query above is slower than this one is, of course that is just me guessing


Select t.client_mnemonic, t.[Average Time], t.[Work Week], t.Group_Type, i.BarType, t.Transactions
FROM
(
select q.Client_Mnemonic, q.[Average Time], q.Group_Type, q.[Work Week], q.Transactions
from
(
SELECT Client_Mnemonic, count(workflow_id) as "Transactions", round((avg(duration) *count(duration)/count(duration)),0) As "Average Time",
CONVERT(varchar(50), (DATEADD(d, @@DATEFIRST - DATEPART(dw, Start_Time) - 5, Start_Time)), 101) as "Work Week", Group_Type 
FROM workflow_data
WHERE
Client_mnemonic = (@ClientMnemonicParameter) and (Group_type = (@GroupSlicerOne)) and --or Group_Type = (@GroupSlicerTwo)) and 
(
convert(date, start_time) between (@BeginDate) and (@EndDate)
)and
duration <= 1200 and
(
definition = 'Meds Rec - Admission'  and datepart(dw, workflow_data.Start_Time) not in (1,7) or
definition = 'Meds Rec - Transfer'  and datepart(dw, workflow_data.Start_Time) not in (1,7) or
definition = 'Meds Rec - Discharge'  and datepart(dw, workflow_data.Start_Time) not in (1,7)
)
GROUP BY datepart(ww,workflow_data.start_time), client_mnemonic, 
CONVERT(varchar(50), (DATEADD(d, @@DATEFIRST - DATEPART(dw, Start_Time) - 5, Start_Time)), 101), Group_Type
)Q
)t
inner join 
(
Select [Work_Week], 
CASE WHEN Seq1 <= @BaselineDuration THEN 'Baseline' 
             ELSE 'Trending'
           END as "BarType"
FROM
(
SELECT CONVERT(varchar(50), (DATEADD(d, @@DATEFIRST - DATEPART(dw, Start_Time) - 5, Start_Time)),101) as "Work_Week",
ROW_NUMBER() OVER (ORDER BY CONVERT(varchar(50), (DATEADD(d, @@DATEFIRST - DATEPART(dw, Start_Time) - 5, Start_Time)),101)) AS Seq1
FROM workflow_data
where client_mnemonic = (@ClientMnemonicParameter) and group_type = @GroupSlicerOne
group by CONVERT(varchar(50), (DATEADD(d, @@DATEFIRST - DATEPART(dw, Start_Time) - 5, Start_Time)),101) 
)m
)i
ON t.[Work Week] = i.[Work_Week]
Order by group_type desc, client_mnemonic, t.[Work Week]



As always, thanks for the help!

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/11/2012 :  00:00:45  Show Profile  Reply with Quote
one of the first things to consider would be to avoid those set of OR conditions and put those values onto a mapping table and then use it in a join

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000