SQL Server Performance Forum – Threads Archive

get average weekly active provider

Hello, The table is generated by IIS Log file. I am working on a web report project. I need to get average weekly avtive provider every month. first, I get unique active provider for every week, then use AVg function to get final number. But it takes 4 mins when I run the query on my local sql server. The table has 3M rows.
I don’t know how to change my query to improve the speed. Below is my query and function select date, avg(total)as average
from
(select convert(datetime,substring (convert(char(11),date,106),4,11)) as date,count(*) as total from
(SELECT dbo.getWeekDate(date)as date, substring(Parameters, 13, 6)AS ProviderID, COUNT(ClientHost) AS Hits
FROM iislog
WHERE (date>= ‘2/1/2005’ and date <= ‘5/31/2005’)and
((Target like ‘/C%’ OR
target like ‘/E%’ OR
target like ‘/P%’) ) GROUP BY dbo.getWeekDate(date),substring(Parameters, 13, 6)
) as mytable
group by date) as myweekly
group by date
dbo.getWeekDate function: CREATE FUNCTION getWeekDate
(@date1 datetime )
RETURNS datetime AS
BEGIN
set @date1=convert(datetime,convert(char(10),@date1,101)) if datename(weekday, @date1)=’Tuesday’
set @date1 = dateadd(day,-1,@date1) if datename(weekday, @date1)=’Wednesday’
set @date1 = dateadd(day,-2,@date1) if datename(weekday, @date1)=’Thursday’
set @date1 = dateadd(day,-3,@date1) if datename(weekday, @date1)=’Friday’
set @date1 = dateadd(day,-4,@date1) if datename(weekday, @date1)=’Saturday’
set @date1 = dateadd(day,-5,@date1) if datename(weekday, @date1)=’Sunday’
set @date1 = dateadd(day,-6,@date1) return @date1
END Thanks

---

You might want to apply your findings here from your other thread. Regarding the UDF:
It is *not* needed. Say, for example, we define Monday as first day of a week, the following will always adjust to this Monday:
SET DATEFIRST 1
DECLARE @dt DATETIME
SET @dt = ‘20050718’
SELECT DATEADD (dd, 1-DATEPART (dw, @dt), DATEDIFF(d,0,@dt)) Erster_Tag
SET @dt = GETDATE()
SELECT DATEADD (dd, 1-DATEPART (dw, GETDATE()), DATEDIFF(d,0,GETDATE())) Erster_Tag You might need to tweak the DATEFIRST setting to suit your needs. A scalar UDF like the one you use will always process one row at a time. Thus ot much better than a cursor and surely a performance killer on larger tables like yours. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)

---

Hello, this is my new query. it takes 38 seconds to get the result. select date, avg(total)as average
from
(select DATEADD(month,DATEDIFF(month,0,date),0) date,count(*) as total from
(SELECT DATEADD (dd, 1-DATEPART (dw, date), DATEDIFF(d,0,date)) date, substring(Parameters, 13, 6)AS ProviderID, COUNT(ClientHost) AS Hits
FROM iislog
WHERE (date>= ‘2/1/2005’ and date <= ‘6/1/2005’)and
((Target like ‘/C%’ OR
target like ‘/E%’ OR
target like ‘/P%’) ) GROUP BY DATEADD (dd, 1-DATEPART (dw, date), DATEDIFF(d,0,date)), substring(Parameters, 13, 6)
) as mytable
group by date) as myweekly
group by date thanks

---

Did you use Index for the Date Column?
Madhivanan Failing to plan is Planning to fail

---

No idea this query will affect the performance positively or negatively but you can check the execution plan for both and it seems more sargeble and as said by madhivanan keep indexes on proper columns like date,target
select date, avg(total)as average
from
(select DATEADD(month,DATEDIFF(month,0,date),0) date,count(*) as total from
(SELECT DATEADD (dd, 1-DATEPART (dw, date), DATEDIFF(d,0,date)) date, substring(Parameters, 13, 6)AS ProviderID, COUNT(ClientHost) AS Hits
FROM iislog
WHERE (date between ‘2/1/2005’ and ‘6/1/2005’)and
(left(Target,2) in(‘/C’,’/E’,’/P’))
GROUP BY DATEADD (dd, 1-DATEPART (dw, date), DATEDIFF(d,0,date)), substring(Parameters, 13, 6)
) as mytable
group by date) as myweekly
group by date

---

There are indexes on date and target column. Thanks

---

One more question. According to the query, every week start from Sunday. I need to start from every Monday. Thanks

---

Have you tried ITW suggestions

---

Have you
SET DATEFIRST 1 at the beginning of your proc? —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)

---

I’ve tried ITW but didn’t set datefirst 1 thanks

---

]]>