Re: counting rows

> SELECT COUNT(user_picture_id) FROM yourTable WHERE user = 'x'
>
> ensuring of course that
> - user_picture_id is the unique key of the picture table; and
> - there is an index on the table for the user code

What if there where 1000's, 10000's of pictures, thats a lot of rows to count, it will be quick, but its the other stuff around it - how do you protect yourself from phantoms? How do you make sure nobody remove a row while you are counting?

Unless you serialise then as you are counting the 10000 rows, 5 might get deleted, you'll incorrectly give 10000 as the total when its actually 9995 - hence you need to use the isolation of serialisable which then starts to have an effect because of the number of pages that need to be read to satisfy the query.

> We actually do that with several types of information: # of transactions
> in a batch; # of members
> in a fund; running account balance, etc. However, we do it with code, and
> not with database-level
> triggers.

That's dangerous, with a trigger its consistent, you can't have people mangle the numbers so the aggregates get out of date to the facts in the table - you are relying on too much there I'm afraid.

A trigger will give a consistent value, the trigger fire whenever an insert/update/delete occurrs.

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


"Murdoc" <murdoc_0_at_hotmail.com> wrote in message 
news:xn0ekhvdk10e6x003_at_news-south.connect.com.au...


> Tony Rogerson wrote:

>

>> 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'

>

> Wouldn't it be more efficient to do:

>

> SELECT COUNT(user_picture_id) FROM yourTable WHERE user = 'x'

>

> ensuring of course that

> - user_picture_id is the unique key of the picture table; and

> - there is an index on the table for the user code

>

>>

>> 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.

>

> We actually do that with several types of information: # of transactions 

> in a batch; # of members

> in a fund; running account balance, etc. However, we do it with code, and 

> not with database-level

> triggers.

>

> -- 

>