SQL "get next sequence" performance

Posted by sedge on 20-Mar-2012 23:57

We use the following SQL command (JDBC and Hibernate) to do a "get next sequence" operation.

       select pub.rq_DataExchange_seq.nextval from sysprogress.syscalctable

      (syscalctable has exactly one record)

Our application does mostly Insert operations so we do a lot of these "get next sequence" selects.

The application can also work with a PostgreSQL 9 DB, which runs the SQL like this:

      select nextval ('public.rq_DataExchange_seq')

Our performance tests show that OpenEdge takes about 65% longer to do the "get next sequence".

Otherwise, the SQL performance on the two databases is comparable.

Is there a better way to to perform the "get next sequence" operation in SQL with OpenEdge?

For background:

We took an old example Hibernate Dialect for OpenEdge and have adapred it for 10.2b so we can modify the code. (happy to share).

Thanks

Steve

OpenEdge General - Forum

All Replies

Posted by durkadurka005 on 21-Mar-2012 09:14

I figured out the same syscalctable trick as you guys for getting the next values of sequences, however I pass TOP 1 just in case, as apparently there is nothing preventing someone from inserting additional records into that table:

SELECT TOP 1 pub.rq_DataExchange_seq.NEXTVAL FROM SYSPROGRESS.SYSCALCTABLE

I doubt that adding TOP 1 to the query would make it perform better, but who knows (this is Progress we're dealing with here after all).

I would love to see whatever code you have, as I'm working on an adapter for another ORM framework and am running into some snags with insertions at the moment.

Posted by asthomas on 21-Mar-2012 09:16

Jeg er væk fra kontoret indtil den 26. marts 2012, og checker ikke altid mail og telefon regelmæssigt. Jeg vil dog svare på din mail så snart som muligt.

Hvis du har behov for support, kontakt venligst support@app-solutions.com, så vender vi tilbage så snart det er mnuligt.

Administrative henvendelser kan ske til : admin@app-solutions.com.

I am not in the office until 26th March 2012. I will not be checking my mail and phone on a regular basis, but will reply to your mail as soon as I can.

If you need support from appSolutions, please send your mails to support@app-solutions.com.

If you need Roundtable support, please send mails to :

support-europe@roundtable-tsms.com.

Med venlig hilsen / Regards

Thomas Hansen

appSolutions a/s

This thread is closed