• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Is Table sequencing the best sequence strategy?

 
Nakataa Kokuyo
Ranch Hand
Posts: 189
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Bartender
Posts: 3508
16
Android Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
If I'm not mistaken, sequencing here means auto-increment right? Not knowing what your data is, this question is irrelevant.
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Likes 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic