Re: counting rows

Tony Rogerson schrieb:
> I think you've fallen into the usual developer trap in thinking what works
> small will scale.
Nothing scales indefinitely. Neither do triggers. OTOH, with the current data warehousing hype, databases have become good at aggragating.

> If there are only 5 or 6 rows per user then fair enough, but what about a
> few thousand, 10'000's? Do you really think the IO will scale....
Yes. Just tried it with 10000 users and 10000 picture entries each. After taking an eternity to insert (this is a very small machine and everything is on one disk), create the index and gather the optimizer statistics, oracle reported a total read of 39824 bytes in the execution plan of "select count(*) from tmp_pictures where user_id=1000". Executing the query didn't take any noticeable time. I think there's plenty of room to scale for a real database server with a real disk layout.

> The trigger is a simple look up via a unique index, probably a few KBytes
> and then an update on those same pages - changing a 4 byte value.
The killer scenario here is a bunch of applications registering pictures for a single user.
a) each trigger firing is a context switch between sql and the trigger language b) every time two triggers fire, they will have to lock, the second will get a

    "not serializable" error as soon as the first one commits and then has to     try again until he's lucky enough to be fastest.

Look what your database offers for large scale stuff. Oracle has materialized views (create materialized view as select x, count(*) from y group by x) with fast incremental update and they do exactly what you want without all that hassle. I'm sure, sqlserver has something comparable.

And *please* OT, what's your load estimate? How many user, how many inserts and how many queries?

Lots of Greetings!
Volker Received on Mon Apr 03 2006 - 15:21:29 CEST