Re: drop in performance using jdbc driver version 9

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

-----Message d'origine-----
De : BENHAMOU Mathieu
Envoyé : mardi 9 octobre 2012 18:13
À : Dave Cramer
Cc : pgsql-jdbc@postgresql.org; ROUX Christian; CREMIEUX Alain; FORGEARD Thierry; MARCHAND Franck
Objet : RE: [JDBC] drop in performance using jdbc driver version 9

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

Cegedim-SRH

-----Message d'origine-----

De : davecramer@gmail.com [mailto:davecramer@gmail.com] De la part de Dave Cramer Envoyé : mardi 9 octobre 2012 13:06 À : BENHAMOU Mathieu Cc : pgsql-jdbc@postgresql.org; ROUX Christian; CREMIEUX Alain; FORGEARD Thierry; MARCHAND Franck Objet : Re: [JDBC] drop in performance using jdbc driver version 9

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> 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

> 

>