SELECT Sum() on big tables

Question

SELECT Sum() on big tables

dani_17

Newbie

Points: 7
More actions
June 4, 2003 at 10:39 am

#159161

I have made I site in asp.net of used cars in the dominican republic. The site is quite successfull and in the 3rd month I have about 1,500 visitors per day and at least I cover expenses 😛
I normally have 3000-3600 cars at a time. The cars I delete are moved to another table to keep it as historial and also to avoid making each query to the cars table to pass also through the deleted cars.
Each time a person views a car, I update or insert a row in a statistics table.
That table has 3 columns
id(int) - id of the car.
hits(decimal)- the number of visits the car had.
hitdate(smalldatetime) - the date of the hits.
So when a user views a car, if there is a row with that car and that date, it only updates the hits + 1. If in that day nobody visited the car, then I insert a new row with that date and hits=1.
In each page I also show the visits. then I call a stored procedure that gives me:
totalhits = the sum of the visits
totalhitstoday = the sum of the visits for today.
Well, getting that 2 hit counters is getting me up to 5-6 seconds lag in the page processing. I already figured that changing the hits column from numeric to decimal makes the SUM() aggregate function to be a lot faster. I don't know how indexes work or what else I can do to improve the speed of the sum select, it's the only thing making my site slow.
The table were I have this problem has 309,100 rows right now. Is that too much?
Thank you very much!! and I hope someone can help me a little bit!

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

Hi dani_17

quote:
I have made I site in asp.net of used cars in the dominican republic. The site is quite successfull and in the 3rd month I have about 1,500 visitors per day and at least I cover expenses 😛

Congratulations!

quote:
id(int) - id of the car.
hits(decimal)- the number of visits the car had.
hitdate(smalldatetime) - the date of the hits.
So when a user views a car, if there is a row with that car and that date, it only updates the hits + 1. If in that day nobody visited the car, then I insert a new row with that date and hits=1.

because nobody can hit a page 1.5 times a day, it might be worth considering changing hits to an integer field. Would also save some disk space.

In each page I also show the visits. then I call a stored procedure that gives me:

quote:
Well, getting that 2 hit counters is getting me up to 5-6 seconds lag in the page processing. I already figured that changing the hits column from numeric to decimal makes the SUM() aggregate function to be a lot faster. I don't know how indexes work or what else I can do to improve the speed of the sum select, it's the only thing making my site slow.
The table were I have this problem has 309,100 rows right now. Is that too much?

This is definitely not too much records. A good starting point might be to run your query or stored procedure VIA QA with and use Query -> Index tuning wizard. Normally the wizard is doing a good job.

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

j.o.s. Ten Centuries Points: 1242 More actions · Answer 2

Try creating a non - clustered index on the hitdate column, because that should improve performance for the totalhitstoday report. Covered queries (queries where all the columns listed have an index), usually out perform those queries without the underlying indexes.