Re: counting rows

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 - 19:12:51 CEST