PreparedStatement performance issue about pgjdbc-ng HOT 8 CLOSED

When I run pgjdbc-ng under jProfile using sampling, I get this performance profile...

This indicates that the created Cleanup task is responsible for the majority of the overhead, followed closely by the regular expressions used in the SQLText.parse() code.

from pgjdbc-ng.

This stems first and foremost from a principal difference between the 'classic' driver and 'ng'. In 'ng' a prepared statement is just that, prepared on the server. In 'classic' this doesn't happen. Prepare it once and execute multiple times would be the proper way to do it in 'ng'.

The regex time is something to look at as running a regex (no matter how complicated) against an empty string should be nearly free.

from pgjdbc-ng.

Given that the Cleanup was showing up high, I tried disabling the housekeeper completely... this is the profile now:

The expensive part of Cleanup is creating the Exception here:

this.allocationStackTrace = new Exception().getStackTrace();

Turns out this is only used if logLeaks is true. I made a local change that only captures the stack trace if logLeaks is true. Unfortunately, there is no way to disable logging leaks from the "outside", so I couldn't actually test it easily ... I just turned off the housekeeper for my test. But it seems logging leaks should be optional (and false by default) with a setter on PGDataSource.

from pgjdbc-ng.

Just by not capturing the stack trace (turning off the housekeeper), and with the trivial change to getNextStatementName() (see pull request), the original test is down from 11463ms to 7868ms.

from pgjdbc-ng.

"Prepare it once and execute multiple times would be the proper way to do it in 'ng'."

Just to add a little color to that statement. While I understand that ideally that is the most efficient way to use a prepared statement, the reality is that ORM frameworks often prepare the same statement many thousands of times, assuming that the driver will "do the right thing". Hibernate is one framework that does this a lot. I am not currently a Hibernate user, but I have been in the past and ran into this behavior. A few years back, I was working at a company that used Hibernate and SQL Server with the jTDS driver, and was actually the motivating factor to contribute SQL parse-tree caching to the jTDS project. Obviously, the whole issue of prepare-time performance can be largely side-stepped with a prepared statement cache, but simple changes to the existing prepare code paths can also provide across the board speedups.

from pgjdbc-ng.

I would be ok with having the default value of 'housekeeper' be false.

from pgjdbc-ng.

I have some thoughts/questions jelling around the housekeeper. This issue is largely resolved by the prepared statement cache, but I'd like to leave it open a bit longer...

from pgjdbc-ng.

I consider this issue closed with the merge from @kdubb.

from pgjdbc-ng.