Hi,
I have a log table that saves web visits. And I have this query which is pretty slow :
select top 500 count(1) as nbCount, currentPage as item,
dateadd(dd, DATEDIFF(dd, 0, dateadd(hh, 0, date)), 0) as sDate from tbl_logs
where fk_tbl_websites_Id = 10203 and date between '2008-08-28 00:00:00' and '2008-09-29 23:59:00'
group by currentPage, dateadd(dd, DATEDIFF(dd, 0, dateadd(hh, 0, date)), 0) order by nbCount desc
Having analyzed the execution plan, I know why this is slow. I think this is because the set of data is too huge. So, for the last month and for the website #10203, the set has around 2 000 000 rows. The Order By clause and the TOP clause show that they account for 44% each of the execution time. That's almost 90% of the total execution time.
The primary key is fk_tbl_websites_id and date. Without the order by
clause, it executes in a flash. With it, the clustered index seek
account for 13% of the execution time. There is also a non-clustered
index on currentPage, but it's not used according to the query plan.
Here the table plus a sample of data
CREATE TABLE [dbo].[tbl_logs](
[fk_tbl_websites_id] [int] NOT NULL,
[date] [datetime] NOT NULL CONSTRAINT [DF_tbl_logs_tmp_date] DEFAULT (getdate()),
[id] [bigint] IDENTITY(1,1) NOT NULL,
[refererDomain] [varchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[refererUrl] [varchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[refererQueryString] [varchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[currentDomain] [varchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[currentPage] [varchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[currentQueryString] [varchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[docTitle] [varchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[timeSpent] [int] NULL CONSTRAINT [DF_tbl_logs_tmp_timeSpent] DEFAULT ((0)),
[fk_tbl_visits_id] [bigint] NULL,
CONSTRAINT [PK_tbl_logs_tmp] PRIMARY KEY CLUSTERED
(
[fk_tbl_websites_id] ASC,
[date] ASC,
[id] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
INSERT INTO [tbl_logs] ([fk_tbl_websites_id],[date],[id],[refererDomain],[refererUrl],[refererQueryString],[currentDomain],[currentPage],[currentQueryString],[timeSpent],[fk_tbl_visits_id])VALUES(10203,'Aug 28 2008 12:00:04:190AM',139959293,'http://www.jpr.com','/videos/show/4950-scarecrow-hidden-camera-pranks','','http://www.jprs.com','/videos/show/4836-free-kids-hidden-camera-pranks','',109,40705532)
INSERT INTO [tbl_logs] ([fk_tbl_websites_id],[date],[id],[refererDomain],[refererUrl],[refererQueryString],[currentDomain],[currentPage],[currentQueryString],[timeSpent],[fk_tbl_visits_id])VALUES(10203,'Aug 28 2008 12:00:09:813AM',139959296,'http://www.jpr.com','/search','q=france$t=videos','http://www.jpr.com','/search','q=rachid$t=videos',241,40706115)
INSERT INTO [tbl_logs] ([fk_tbl_websites_id],[date],[id],[refererDomain],[refererUrl],[refererQueryString],[currentDomain],[currentPage],[currentQueryString],[timeSpent],[fk_tbl_visits_id])VALUES(10203,'Aug 28 2008 12:00:16:190AM',139682279,'http://www.jpr.com','/videos/top','cat=gags','http://www.jprs.com','/channels','',22,40705317)
INSERT INTO [tbl_logs] ([fk_tbl_websites_id],[date],[id],[refererDomain],[refererUrl],[refererQueryString],[currentDomain],[currentPage],[currentQueryString],[timeSpent],[fk_tbl_visits_id])VALUES(10203,'Aug 28 2008 12:00:28:703AM',139682287,'http://www.jpr.com','/videos/show/4755-sandwich-matress-hidden-camera-pranks','','http://www.jprs.com','/videos/show/4986-blindman-has-to-go-hidden-camera-pranks','',96,40706111)
INSERT INTO [tbl_logs] ([fk_tbl_websites_id],[date],[id],[refererDomain],[refererUrl],[refererQueryString],[currentDomain],[currentPage],[currentQueryString],[timeSpent],[fk_tbl_visits_id])VALUES(10203,'Aug 28 2008 12:00:31:847AM',139682289,'','','','http://www.jprs.com','/home','',189,40706306)
INSERT INTO [tbl_logs] ([fk_tbl_websites_id],[date],[id],[refererDomain],[refererUrl],[refererQueryString],[currentDomain],[currentPage],[currentQueryString],[timeSpent],[fk_tbl_visits_id])VALUES(10203,'Aug 28 2008 12:00:33:143AM',139682290,'http://www.jprs.com','/videos/show/4979-wrong-side-hidden-camera-pranks','','http://www.jprs.com','/videos/show/3966-cabin-cleaning-hidden-camera-pranks','',21,40705691)
INSERT INTO [tbl_logs] ([fk_tbl_websites_id],[date],[id],[refererDomain],[refererUrl],[refererQueryString],[currentDomain],[currentPage],[currentQueryString],[timeSpent],[fk_tbl_visits_id])VALUES(10203,'Aug 28 2008 12:00:38:830AM',139682293,'http://www.jprs.com','/channels','','http://www.jprs.com','/community','',5,40705317)
INSERT INTO [tbl_logs] ([fk_tbl_websites_id],[date],[id],[refererDomain],[refererUrl],[refererQueryString],[currentDomain],[currentPage],[currentQueryString],[timeSpent],[fk_tbl_visits_id])VALUES(10203,'Aug 28 2008 12:00:43:220AM',139682296,'http://www.jprs.com','/videos/show/472-lovers-mistake-hidden-camera-pranks','','http://www.jprs.com','/videos/show/491-impossible-to-attach-box-hidden-camera-pranks','',128,40704574)
INSERT INTO [tbl_logs] ([fk_tbl_websites_id],[date],[id],[refererDomain],[refererUrl],[refererQueryString],[currentDomain],[currentPage],[currentQueryString],[timeSpent],[fk_tbl_visits_id])VALUES(10203,'Aug 28 2008 12:00:43:593AM',139682297,'http://www.jprs.com','/community','','http://www.jprs.com','/comedians','',7,40705317)
INSERT INTO [tbl_logs] ([fk_tbl_websites_id],[date],[id],[refererDomain],[refererUrl],[refererQueryString],[currentDomain],[currentPage],[currentQueryString],[timeSpent],[fk_tbl_visits_id])VALUES(10203,'Aug 28 2008 12:00:45:017AM',139682298,'http://www.jprs.com','/videos/show/4757-that-s-not-my-dog-hidden-camera-pranks','','http://www.jprs.com','/videos/show/4986-blindman-has-to-go-hidden-camera-pranks','',90,40706109)
So, my question is how to speed up that kind of query? Any idea?
Thanks
Stephane