Re: Best approach for a "gap-less" sequence

Berend Tober <btober(at)seaworthysys(dot)com> writes:

> A business requirement is to generate table rows that have uniformly
> increasing, whole number sequences, i.e., the "gap-less" sequence. In this
> particular case the situation requires multiple such sequences within the same
> table -- for each employee, there is a uniformly-sequenced set of expense
> reports. I use the term "compound sequence" for this situation because the
> expense reports are sequenced independently on a per-employee basis.

This is something that I'll also have to code ;-) But the sequence for
"employees" would also be a sequential and gapless sequence here (yep, it's
composed of two gapless sequences, being one "fixed" and the other varying for
each new record inside the first sequence).

Is the performance degradation here too big? I think that it might be lower
than with approaches that used some kind of locking...

I'm not sure, on this approach of yours, how's the probability of having
several records inserted on different transactions for one employee. In my
cases I see that when I have one unique "filter" (employee, invoice, etc.) the
operation is done only by one person and consequently in one transaction
only, what makes it possible to adopt more complex -- and badly performant --
solutions (not that I want them, it's just that it wouldn't be noticeable in
the application as far as the user is concerned). Do you have this
concurrence on the real application?

--
Jorge Godoy <jgodoy(at)gmail(dot)com>