Slow Query

[google]

I have a log file with about 180K records.
I'm looking to create a ranking of most frequently visited pages.
This is the query I'm currently using:

Select top 10 * from
( SELECT HitPage, URL, count(HitPage) as f1, PageTitle from
tblSession
where LogDate >=39142
GROUP BY HitPage, url, PageTitle ) order by f1 desc

I have tried indexes on:
LogDate, HitPage, URL, PageTItle
HitPage, URL, PageTitle, LogDate

When the LogDate is less than a month ago, its very quick, but when
looking at a year's worth of data is very slow

Any advice would be appreciated!

 

[John Spencer]

I'm not sure if you mean you set individual indexes on each field or you set
up a compound index.

In this set up, I would try having an index on each individual field
instead of a compound index.

Select top 10 *
FROM
( SELECT HitPage, URL, count(HitPage) as f1, PageTitle
from tblSession
WHERE LogDate >=39142
GROUP BY HitPage, url, PageTitle )
order by f1 desc

LogDate, HitPage, URL, PageTItle


--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[google]

Thanks for the idea. I tried it but it didn't improve the query
processing time.

Any other thoughts?

 

[Jerry Whittle]

Hi,

I ran the same query on a table with 750K records and no indexes. It took
about 10 seconds to return records the second time I ran it.

I put an index on LogDate and it ran in about 7 seconds. I then added a
second index on the other three fields. It was slower therfore I removed the
second index.

I did simplify things somewhat but it didn't seem to help any:

SELECT TOP 10 HitPage,
URL,
PageTitle,
Count(HitPage) as f1
FROM tblSession
WHERE LogDate >=39142
GROUP BY HitPage, url, PageTitle
ORDER BY 4 desc ;

I only had three different LogDate's so YMMV.

 

[Michel Walsh]

Can you try:


SELECT TOP 10 HitPage, URL, COUNT(HitPage) as f1, PageTitle
FROM tblSession
WHERE LogDate >=39142
GROUP BY HitPage, url, PageTitle
ORDER BY COUNT(HitPage) DESC


ie, without using a virtual table.


Vanderghast, Access MVP