7 Replies Latest reply on Jun 17, 2008 3:20 AM by Tom Baeyens

    id's longs or strings

    Tom Baeyens Master

      would it make sense to switch to strings for the ids ?

      up to now, i selected for longs because of memory performance reasons.

      probably supporting both long and string as id is going to be hard in the ProcessService interface

      this was the message i got today from a client

      We now explore ways to improve performance. Our database administrator recommended not to use DB2 ZOS native ID generator.

      One possible solution is to use UUID for IDs in jBPM tables (Hibernate has built in support for UUID generator). This solution reduces round trips to DB and avoids the use of DB2 ZOS native ID generator. We expect this to improve performance.

      We have a couple of situations where we need to make sure that db ids are initialized. This is when user code is called and when an new object might not yet be saved. Currently we just save the object to make sure it gets an id.

      Maybe using UUID might also speed up that part. Cause then we could address the hibernate UUID generator directly when we need to make sure that a new runtime gets an id.

      WDYT ?

        • 1. Re: id's longs or strings
          Ronald van Kuijk Master

          I have no problem with UUID's although I'm not sure how the size of the database will increase with these relatively long strings for each record compared to all other data

          • 2. Re: id's longs or strings
            Joram Barrez Master

            I don't see any advantages in using Strings or UUIDs:

            A DB id is internal, for Hibernate usage only. It should not matter if this is a long, String, decimal number, ... However, since current cpu's are numeric-based ints/longs simply are faster than Strings in almost any case.

            In my opinion, the problem with UUIDs is that they are random generated (if I'm correct). This means that, for the database, maintaining an index when inserting a new value is harder compared to inserting a numeric value that is higher than the previous one.
            Since jBPM does quite some inserts (new process instance, task instance, variable) I think this would not be beneficial for performance. This is however a feeling, I do not have hard numbers.

            More info: http://www.mysqlperformanceblog.com/2007/03/13/to-uuid-or-not-to-uuid/
            As you can read in the comments, UUIDs are not that great (not in MySQL anyway).

            So, in my opinion, the performance gain is doubtable and not worth the trouble.

            • 3. Re: id's longs or strings
              Tom Baeyens Master

              i'm also in favour of keeping longs.

              ...but, the big performance penalty could come from an extra db rountrip cause of the DB id generation. the optimisation would be that the application generates the id and not the DB. so one DB roundtrip per insert would be saved.

              on the other hand, you can also find HILO id generation in the application. but that is then not clusterable.

              so this leaves one question open: is there a long-based id generator that can be used in the application that is clusterable ? I believe that I have developed one for hibernate once, but never managed to contribute it :-( Maybe we could see if we could revive it.

              • 4. Re: id's longs or strings
                Bernd Ruecker Master

                Maybe you should talk to the hibernate guys for this? I think this is quite a common issue in every application and should be solved in the OR abstraction layer itself...

                And it should be also possible somehow with longs...

                • 5. Re: id's longs or strings
                  Joram Barrez Master

                  I suggest that the current approach is used.
                  Switching to another solution shouldn't be that hard (changing the mapping config)...

                  Getting the PVM on the market is priority nr 1, not getting it as performant as it should possibly be.

                  • 6. Re: id's longs or strings
                    Alejandro Guizar Master

                    <a href=http://www.ibm.com/developerworks/java/library/j-jdbcnew/">JDBC 3.0 addresses the need to obtain the value of an auto-generated key. A quick search revealed that JDBC 3.0 drivers are available for (at least) Oracle, PostgreSQL, SQL Server and Derby (yuck!). Hibernate tries to determine this driver capability using connection metadata.

                    Of course, the driver is not required to implement this feature in a single trip, but the door is open to optimization. I believe any reasonable driver would retrieve the keys in a single trip. This should not be the primary reason to abandon the simplicity and economy of numeric IDs.

                    • 7. Re: id's longs or strings
                      Tom Baeyens Master

                      @Alejandro: we would need to wait until hibernate leverages this feature and this version of hibernate is ubiquitously available.

                      But afaict, the main conclusion in this thread is that we should just stick with longs as ids.