Re: counting rows

Tony Rogerson wrote:

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

That's true, although another factor that has been mentioned, is how often is the count required? Is it required on a frequent or seldom basis? And yes, I do know that these are very subjective, but the time it takes to count the rows needs to be weighed against the relative number of times that count is required. For example, if a count of 1,000,000 rows was required every two weeks, then it would probably not require an additional field, whereas a count of 50,000 rows every 15 seconds will definitely.

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

First, we aren't. Since all of our business logic in implement in 4GL, it is the only thing that can access the database. Clients only have read-only access outside of our software. Secondly, by putting though into the transaction scoping, and making decisions about what is critically necessary to be up-to-date and what isn't, it can be extremely easy to avoid using database triggers.

The other advantage in not using database triggers, is that if a bug is found in the business logic, no schema has to be delivered to client sites to resolve the issue - it is all purely done in code. We only use triggers for system auditing (who changed what, when, from what to what, etc).

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

And yet ours also gives a consistent values (for those that it needs to).

--