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>
Date: Sat, 1 Apr 2006 20:03:05 +0100
Message-ID: <e0mip2$8i7$1$8300dec7_at_news.demon.co.uk>
-- 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...Received on Sat Apr 01 2006 - 21:03:05 CEST
> 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