Re: counting rows

When somebody asks this...

>>Consider the tables USERS and PICTURES. When retrieving the number of
>>pictures from a user there are two ways:

>>1) I count them from pictures table.
>>2) I pick it directly from a field "numPictures" in table users.

>>How are you used to do this? Which is better?

I give an answer to THAT question and not add in other stuff, if the OP wanted min, max, sum etc... then I'd answer differently.

Given 10 concurrent users all wanting the number of pictures and the number of rows is 10,000, which in your opinion is better?

• The engine may need to read 20MBytes of memory per user to realise the result. SELECT COUNT(*) FROM yourtable WHERE user = 'x'

or

• The engine only needs to read a single row per user, a couple of KBytes at most. SELECT num_pict FROM user_details WHERE user = 'x'

num_pict is kept up-to-date using a before (instead of) trigger

The trigger simply does a +1 or -1 each time a row is added/removed.

-- 
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials


"Tony Andrews" <andrewst_at_onetel.com> wrote in message 
news:1143891453.767287.295100_at_i39g2000cwa.googlegroups.com...


> Tony Rogerson wrote:

>> Number 2 will be best because you are effectively storing the aggregate

>> instead of the server having to count the number of rows in the table 

>> which

>> would require IO.

>

> That is wildly simplistic.  Based on that advice, it would be "best" to


> always store the count, sum, min, max, avg, etc. of everything in its

> parent table(s).  Please tell me you don't!?

>