Strategies to use Database Sequences?

Question

Hello all,

I have a high-end architecture which receives many requests every second (in fact, it can receive many requests every millisecond). The architecture is designed so that some controls rely on a certain unique id assigned to each request.

To create such UID we use a DB2 Sequence. Right now I already understand that this approach is flawed, since using the database is costly, but it makes sense to do so because this value will also be used to log information on the database.

My team has just found out an increase of almost 1000% in elapsed time for each transaction, which we are assuming happened because of the sequence. Now I wonder, using sequences will serialize access to my application? Since they have to guarantee that increments works the way they should, they have to, right?

So, are there better strategies when using sequences? Please assume that I have no other way of obtaining a unique id other than relying on the database.

Answer 1

One possible approach to get IDs that might be relevant is to use a UUID/GUID. While I don't know how demanding it is generate these values, it most definitely does not suffer from the serialization problem you are worried about.

Answer 2

Using a sequence will necessarily serialize your application. However, these things are optimized to have a minimla impact. Of course, we can always muck things up ourselves by declaring things in an unhelpful fashion. So, how is this sequence defined? Does it have a large CACHE? Have you specified NO ORDER?

Having said which....

The thing that leaps out from your question is not the phrase in bold, it is the sentence before it:

"My team has just found out an increase of almost 1000% in elapsed time for each transaction, which we are assuming happened because of the sequence."

We all know what ASSUME makes (well not in this case, because I'm assuming nothing). Has there been a recent change affecting this sequence? If not, why do you all think it is responsible for a sudden 1000% downturn in performance? Instead of assuming (i.e. guessing) perhaps it would be better to gather some evidence. That time is going somewhere, and you need to discover where. There's no point in tweakling your sequence if you have a race condition somewhere in your code, or you are burning CPU waiting for a lock, or you have a bad interconnect which is slowing down writes to the SAN, etc, etc.

Do you have any logging or trace switched on, or that you could switch on? Can you reproduce this slowdown in another environment, such as development or system test? It may be that the sequence is to blame. At least then you can approach the re-engineering task confident in the knowledge that you are addressing the real problem.