How can I speed this sp up? - SQL Server Forums

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)

How can I speed this sp up?

Author

Topic

00kevin
Yak Posting Veteran

78 Posts

Posted - 2007-09-28 : 15:37:01

Can someone here make a suggestion?

Do you think it would be faster if I made a temp table and just updated it for each day?


ALTER procedure [dbo].[Report_WeekSummary]
(
	@Monday as datetime=null
)
as
if(@Monday is null)	
begin
	set @Monday = dbo.F_START_OF_Week(getDate(),2) 
end

select distinct 
	a.PageUrl,	
	(select count(distinct UserName) from vPageVisits as b where a.PageUrl = b.PageURL and b.StartOfDay = dbo.F_START_OF_Day(@Monday)) as MonUsers,
	(select count(*) from vPageVisits as b where a.PageUrl = b.PageURL and b.StartOfDay = dbo.F_START_OF_Day(@Monday)) as MonPageViews,

	(select count(distinct UserName) from vPageVisits as b where a.PageUrl = b.PageURL and b.StartOfDay = dbo.F_START_OF_Day(DateAdd(d,1,@Monday))  ) as TueUsers,
	(select count(*) from vPageVisits as b where a.PageUrl = b.PageURL and b.StartOfDay = dbo.F_START_OF_Day(DateAdd(d,1,@Monday)))  as TuePageViews,

	(select count(distinct UserName) from vPageVisits as b where a.PageUrl = b.PageURL and b.StartOfDay = dbo.F_START_OF_Day(DateAdd(d,2,@Monday))  ) as WedUsers,
	(select count(*) from vPageVisits as b where a.PageUrl = b.PageURL and b.StartOfDay = dbo.F_START_OF_Day(DateAdd(d,2,@Monday)))  as WedPageViews,

	(select count(distinct UserName) from vPageVisits as b where a.PageUrl = b.PageURL and b.StartOfDay = dbo.F_START_OF_Day(DateAdd(d,3,@Monday))  ) as ThuUsers,
	(select count(*) from vPageVisits as b where a.PageUrl = b.PageURL and b.StartOfDay = dbo.F_START_OF_Day(DateAdd(d,3,@Monday)))  as ThuPageViews,

	(select count(distinct UserName) from vPageVisits as b where a.PageUrl = b.PageURL and b.StartOfDay = dbo.F_START_OF_Day(DateAdd(d,4,@Monday))  ) as FriUsers,
	(select count(*) from vPageVisits as b where a.PageUrl = b.PageURL and b.StartOfDay = dbo.F_START_OF_Day(DateAdd(d,4,@Monday)))  as FriPageViews,

	(select count(distinct UserName) from vPageVisits as b where a.PageUrl = b.PageURL and b.StartOfDay = dbo.F_START_OF_Day(DateAdd(d,5,@Monday))  ) as SatUsers,
	(select count(*) from vPageVisits as b where a.PageUrl = b.PageURL and b.StartOfDay = dbo.F_START_OF_Day(DateAdd(d,5,@Monday)))  as SatPageViews,

	(select count(distinct UserName) from vPageVisits as b where a.PageUrl = b.PageURL and b.StartOfDay = dbo.F_START_OF_Day(DateAdd(d,6,@Monday))  ) as SunUsers,
	(select count(*) from vPageVisits as b where a.PageUrl = b.PageURL and b.StartOfDay = dbo.F_START_OF_Day(DateAdd(d,6,@Monday)))  as SunPageViews
from vPageVisits as a
where 
a.StartOfDay >= dbo.F_START_OF_Week(@Monday,2) 
and 
a.StartOfDay  <= DateAdd(d,6,@Monday)

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-09-28 : 15:44:14

change those
select count ...
into case statements:
select ..., sum(case when (yourConditionInTheWherePart) then 1 else 0 end), ... other cases
from...

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

tm
Posting Yak Master

160 Posts

Posted - 2007-10-01 : 11:36:46

You could try Group By .. Something like ..

select a.PageUrl,
UserName,
case datepart(F_START_OF_Day)
when 1 then 'Sunday'
when 2 then 'monday'
when 3 then 'tuesday'
when 4 then 'wednesday'
when 5 then 'thursday'
when 6 then 'friday'
when 7 then 'saturday'
end as [Weekday] ,
count(F_START_OF_Day) as PageUrlCountPerUserName
from vPageVisits as a
where
a.StartOfDay >= dbo.F_START_OF_Week(@Monday,2)
and
a.StartOfDay <= DateAdd(d,6,@Monday)
group by PageUrl, UserName, DATEPART(dw, F_START_OF_Day)

quote:

Originally posted by 00kevin

Can someone here make a suggestion?

Do you think it would be faster if I made a temp table and just updated it for each day?


ALTER procedure [dbo].[Report_WeekSummary]
(
	@Monday as datetime=null
)
as
if(@Monday is null)	
begin
	set @Monday = dbo.F_START_OF_Week(getDate(),2) 
end

select distinct 
	a.PageUrl,	
	(select count(distinct UserName) from vPageVisits as b where a.PageUrl = b.PageURL and b.StartOfDay = dbo.F_START_OF_Day(@Monday)) as MonUsers,
	(select count(*) from vPageVisits as b where a.PageUrl = b.PageURL and b.StartOfDay = dbo.F_START_OF_Day(@Monday)) as MonPageViews,

	(select count(distinct UserName) from vPageVisits as b where a.PageUrl = b.PageURL and b.StartOfDay = dbo.F_START_OF_Day(DateAdd(d,1,@Monday))  ) as TueUsers,
	(select count(*) from vPageVisits as b where a.PageUrl = b.PageURL and b.StartOfDay = dbo.F_START_OF_Day(DateAdd(d,1,@Monday)))  as TuePageViews,

	(select count(distinct UserName) from vPageVisits as b where a.PageUrl = b.PageURL and b.StartOfDay = dbo.F_START_OF_Day(DateAdd(d,2,@Monday))  ) as WedUsers,
	(select count(*) from vPageVisits as b where a.PageUrl = b.PageURL and b.StartOfDay = dbo.F_START_OF_Day(DateAdd(d,2,@Monday)))  as WedPageViews,

	(select count(distinct UserName) from vPageVisits as b where a.PageUrl = b.PageURL and b.StartOfDay = dbo.F_START_OF_Day(DateAdd(d,3,@Monday))  ) as ThuUsers,
	(select count(*) from vPageVisits as b where a.PageUrl = b.PageURL and b.StartOfDay = dbo.F_START_OF_Day(DateAdd(d,3,@Monday)))  as ThuPageViews,

	(select count(distinct UserName) from vPageVisits as b where a.PageUrl = b.PageURL and b.StartOfDay = dbo.F_START_OF_Day(DateAdd(d,4,@Monday))  ) as FriUsers,
	(select count(*) from vPageVisits as b where a.PageUrl = b.PageURL and b.StartOfDay = dbo.F_START_OF_Day(DateAdd(d,4,@Monday)))  as FriPageViews,

	(select count(distinct UserName) from vPageVisits as b where a.PageUrl = b.PageURL and b.StartOfDay = dbo.F_START_OF_Day(DateAdd(d,5,@Monday))  ) as SatUsers,
	(select count(*) from vPageVisits as b where a.PageUrl = b.PageURL and b.StartOfDay = dbo.F_START_OF_Day(DateAdd(d,5,@Monday)))  as SatPageViews,

	(select count(distinct UserName) from vPageVisits as b where a.PageUrl = b.PageURL and b.StartOfDay = dbo.F_START_OF_Day(DateAdd(d,6,@Monday))  ) as SunUsers,
	(select count(*) from vPageVisits as b where a.PageUrl = b.PageURL and b.StartOfDay = dbo.F_START_OF_Day(DateAdd(d,6,@Monday)))  as SunPageViews
from vPageVisits as a
where 
a.StartOfDay >= dbo.F_START_OF_Week(@Monday,2) 
and 
a.StartOfDay  <= DateAdd(d,6,@Monday)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources