Re: counting rows

From: Tony Rogerson <tonyrogerson_at_sqlserverfaq.com>
Date: Sat, 1 Apr 2006 20:03:05 +0100
Message-ID: <e0mip2$8i7$1$8300dec7_at_news.demon.co.uk>


I'll answer that question with another question - do you want it accurate? In which case you'll need to serialise anyway.

Locking will be there anyway because of the inserting of the picture row data.

The update of count is simply modifying 4 bytes on a single row which would be accessed via a unique index (very little IO), do you really think the contention will be with the update?

By doing COUNT(*) and serialising the query will cause considerable contention because the COUNT(*) query will be a lot longer.

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


"E. Lefty Kreouzis" <lefty_at_internet.gr> wrote in message 
news:slrne2td4n.5n3.lefty_at_goedel.home.rtfm.gr...

> On 2006-04-01, Tony Rogerson <tonyrogerson_at_sqlserverfaq.com> wrote:
>> When somebody asks this...
>> [snip]
>>
>> 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.
>>
>
> Lets extend this scenario a bit you have 100 concurrent users, some
> adding pictures (and thus inserting into the picture tables), some
> deleting pictures and some doing other unrelated stuff. All in
> transactions running at an appropriate isolation level ( at least
> repeatable read if not serializable). How is the trigger solution
> going to handle that without horrid locking issues?
>
> This is by the way the reason that select count(*) in PostgreSQL
> scans the table rather than reading some magic stored count. Just because
> different tuples might be visible to different transactions.
>
> Simple example an initially empty table with 3 open transactions t1,
> t2 & t3. t1 inserts 2 rows,
> t2 inserts 5 rows and
> t2 doesn't insert any.
>
> what is count(*) for t1? t2? t3?
>
> E. Lefty Kreouzis
Received on Sat Apr 01 2006 - 21:03:05 CEST

Original text of this message