File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes Object Relational Mapping and the fly likes Is Table sequencing the best sequence strategy? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "Is Table sequencing the best sequence strategy?" Watch "Is Table sequencing the best sequence strategy?" New topic

Is Table sequencing the best sequence strategy?

Nakataa Kokuyo
Ranch Hand

Joined: Jul 24, 2011
Posts: 189
Good day,

May I know whether "Table sequencing" is best among other sequence strategy in term of portable and performance?

FYI, I'm using mySQL for test environment and Oracle as production DB.

Thanks for sharing!!
K. Tsang

Joined: Sep 13, 2007
Posts: 3131

If I'm not mistaken, sequencing here means auto-increment right? Not knowing what your data is, this question is irrelevant.

K. Tsang JavaRanch SCJP5 SCJD OCPJP7 OCPWCD5 OCPBCD5 OCPWSD5 OCMJEA5 part 1 part 2/3
Martin Vajsar

Joined: Aug 22, 2010
Posts: 3733

There probably isn't a single best strategy (the silver bullet), if it was, Hibernate wouldn't have any reason to support anything else.

For example, Oracle has sequences for this purpose and they perform and scale much better than selecting new values from table (at least when implemented and configured properly). But since some databases don't have sequences, they're obviously not the most portable solution. These two goals go somewhat against each other.

In the end, it depends on your needs. If you need the most scalable solution (say, thousands transactions per second), you'll probably have to lean towards the performance and forfeit the portability. If you'll be doing just a few transactions per second, portability might be more valuable to you than performance.

Scott's article about key generation might help you there, even if it doesn't concern Hibernate: Database Key Generation in Java Applications. The application key generators described there might be a good compromise (perform nearly as well as sequences), though they do need some setup first.
I agree. Here's the link:
subject: Is Table sequencing the best sequence strategy?
It's not a secret anymore!