Обсуждение: JDBC Overhead

It appears to me as if there is quite a bit of overhead with the Postgres JDBC driver when compared with other JDBC drivers. This simple piece of code shows me something very odd:

-------------------------------------------------------
ps = con.prepareStatement("SELECT 1");
long execStart = System.currentTimeMillis();
rs = ps.executeQuery();
System.err.println ("Query executed in " + (System.currentTimeMillis() - execStart) + "ms");
--------------------------------------------------------

Result of this code is:
Query executed in 57ms

But with duration logging enabled on the database side, I can see that the database side processing time was less than 1 ms.

LOG:  duration: 0.073 ms  statement: EXECUTE <unnamed>  [PREPARE:  SELECT 1]

When I run this same code against a mySQL or Sybase database, I get 1ms execution time. Am I missing some bit of tuning that I can do with the database or the JDBC driver to eliminate that 50+ms of latency?

I am running postgres 8.1.4 on solaris and I've tried all 3 JDBC drivers. The java program is running on the same server as the database so I don't believe there is any network latency involved.

Thanks!!

_____________________________________________________________________________________
Bob Damato                                                                         Cox Target Media    
Internet Technology Manager                                                                                                     Largo, Florida

Our greatest glory is not in never falling, but in rising every time we fall.  -- Confucius

> When I run this same code against a mySQL or Sybase database, I get
> 1ms execution time. Am I missing some bit of tuning that I can do with
> the database or the JDBC driver to eliminate that 50+ms of latency?
>


What do you get when you execute (from a shell prompt, assuming you're
on a unix type system),

    $ time psql -c "SELECT 1;"  dbname username

unluckily, this won't work if password authentication is required,
unless you setup a ~/.pgpass

I get about 12mS on a several year old x86 linux server

Bob Damato wrote:
> It appears to me as if there is quite a bit of overhead with the
> Postgres JDBC driver when compared with other JDBC drivers. This
> simple piece of code shows me something very odd:
> -------------------------------------------------------
> ps = con.prepareStatement("SELECT 1");
> long execStart = System.currentTimeMillis();
> rs = ps.executeQuery();
> System.err.println ("Query executed in " +
> (System.currentTimeMillis() - execStart) + "ms");
> --------------------------------------------------------
> Result of this code is:
> Query executed in 57ms
> But with duration logging enabled on the database side, I can see
> that the database side processing time was less than 1 ms.
> LOG:  duration: 0.073 ms  statement: EXECUTE <unnamed>  [PREPARE:
> SELECT 1]
> When I run this same code against a mySQL or Sybase database, I get
> 1ms execution time. Am I missing some bit of tuning that I can do
> with the database or the JDBC driver to eliminate that 50+ms of
> latency? I am running postgres 8.1.4 on solaris and I've tried all 3
> JDBC drivers. The java program is running on the same server as the
> database so I don't believe there is any network latency involved.

When you say you get 1 ms with mySQL and Sybase, is that just the query
or the JDBC code?  1 ms seems rather fantastic to be able traverse all
those execution paths.

If you are running all three in the same configuration - JDBC app and DB
engine on the same box - then check to make sure they are all either
using the network stack or all bypassing it.

--
Guy Rouillier

Bob Damato wrote:

> -------------------------------------------------------
> ps = con.prepareStatement("SELECT 1");
> long execStart = System.currentTimeMillis();
> rs = ps.executeQuery();
> System.err.println ("Query executed in " + (System.currentTimeMillis() -
> execStart) + "ms");
> --------------------------------------------------------
>
> Result of this code is:
> Query executed in 57ms

Try a better benchmark, what you are probably measuring there is mostly
classloading, JIT, and similar startup costs. How long does it take on
the 1000th execution?

Also, unless you are specifically measuring query time when reexecuting
the same PreparedStatement many times, you should include the cost of
prepareStatement() in your measurements.

-O