Re: drop in performance using jdbc driver version 9

Hello MARCHAND,

As you have confirmed with the test case the time differential is present.
After reviewing the differences between the 8.4-703.jdbc & 9.1-903 source
code it is clear as you indicated there is a considerable number of changes
to the code for getIndexInfo() & getPrimaryKeys(). This appears to be the
result of providing the correct information for those calls to collect
the required information for newer versions of the database, 8,9.

To try and give some answer to your questions, 1-3.

Please review the source code for those different versions of the jdbc that
you highlight to understand the limited respond that I may provide to your
questions. Perhaps others may also help where I may be incorrect in nature.

1. If you review that code for later versions of the jdbc, 9.0xxx, it is
    clear that a conditional check is made to create a sql query that
    corresponds to the 8.3++ database and then older versions.

    if (connection.haveMinimumServerVersion("8.3"))

    A closer look at that query as compared to older database sql queries
    indicates it is condsiderably more complicated. Comments as such even.

    The time differential between 8.4-701.jdbc & 9.0-801.jdbc is most
    probable cause by the more complicated query, to your upgraded 9
    database. The most likely reason for the 9.0dev-800.jdbc is because
    it does not contain the new conditional check for 8.33++ database.
    This I can not confirm though.

2. Though the sql query has become more complex, I do not understand where
    that translates to more requests. Perhaps this is a backend aspect at
    the database as a result of the more complex query.

3. It appears that in my opinion the more complex queries for the metadata
    getPrimaryKeys() & getIndexInfo() probably are the reason for the drop
    in performance you are seeing. You should do additional testing has
    indicated to confirm or others may answer more definitely.

    Clearly if you decide to use the 9.0dev-800jdbc or 8.4-701jdbc you may
    not be collecting the correct information for certain aspects of metadata
    for getPrimaryKeys() & getIndexInfo() in a PostgreSQL 9 database.

danap.


MARCHAND Franck wrote:
 > Hi,
 >
 > I made several tests:
 >
 > Red, the time is multiplied by 10.
 > ~
 > ~
 > ~
 >
 > You may reproduce these tests with the attached java class.
 >
 > Juste change library jdbc in your project to compare :
 >
 > - postgresql-8.4-701.jdbc4.jar
 > - postgresql-9.1-902.jdbc4.jar
 >
 > Regards,
 >
 > Franck Marchand
 >
 >
 > Dave,
 >
 > We are using the following methods:
 >
 > dbmd.getColumns(null, schemaName, tableName, null);
 > dbmd.getIndexInfo(null, schemaName, tableName, false, false);
 > dbmd.getPrimaryKeys(null, schemaName, tableName);
 > dbmd.getExportedKeys(null, schemaName, tableName);
 > dbmd.getImportedKeys(null, schemaName, tableName);
 >
 > where dbmd is an instance of AbstractJdbc2DatabaseMetaData (or a subclass).
 >
 > Mathieu Benhamou
 >
 > Can you be specific about which meta data calls you are making ?
 >
 > Test case ?
 > Dave Cramer
 > dave.cramer(at)credativ(dot)ca
 >
 > http://www.credativ.ca
 >
 > On Tue, Oct 9, 2012 at 3:44 AM, BENHAMOU Mathieu
 > <mathieu.benhamou@cegedim-srh.com
 > <mailto:mathieu.benhamou@cegedim-srh.com>> wrote:
 >
 >  > Hello,

 >  > We have been using postgreSQL for several years and we have just
 >  > upgraded the jdbc driver (along with the db server) to version 9. As a
 >  > result, we are getting poor performance with the new configuration:
 >  > operations that took about 10 seconds using
 >  > postgresql-8.4-701.jdbc4.jar take more than 2 minutes now.
 >
 >  > To be more accurate, our application relies on some kind of init
 >  > function that uses select queries involving metadata and ordinary data.

 >  > 1.  This operation takes about 12 seconds when our application is
 >  > running with postgresql-8.4-701.jdbc4.jar.  It also takes 12 or 15
 >  > seconds with postgresql-9.0dev-800.jdbc3.jar.
 >  > However, it takes more than 120(!) seconds using
 >  > postgresql-9.0-801.jdbc3.jar  or later versions.
 >
 >  > 2. Our guess is that metadata functions have become much slower since
 >  > 9.0-801. We also found that many more requests are generated by the
 >  > metadata functions. About 1200 request are sent to the database server
 >  > when running postgresql-8.4-701.jdbc4.jar, more than 2000 when running
 >  > postgresql-9.1-902.jdbc4.jar.
 >  > Methods such as get getIndexInfo() from AbstractJdbc2DatabaseMetaData
 >  > seem to have changed a lot, but there are probably other major changes.
 >
 >  > 3. Can you confirm that changes in the metadata functions of the JDBC
 >  > driver might explain the drop in performance ?
 >  > Is there any workaround that would save us from major surgery in our
 > code ?

 >  > Can we use postgresql-9.0dev-800.jdbc3.jar instead of later versions ?
 >  > Is it safe to use postgresql-8.4-701.jdbc4.jar against a 9.0 server ?
 >
 >  > Thanks for your help.
 >  > Best regards,
 >  > Mathieu Benhamou
 >  > CEGEDIM-SRH