Re: [QUESTIONS] JDBC updates are slow - Mailing list pgsql-interfaces

On Fri, 9 Jan 1998, Justin Wells wrote:
> Hi,
>
> I'm using JDBC to store persistent data. It seems to be fast enough
> loading data from the database, but it is too slow writing it back.
>
> The difference seems to be that I can get all the data from the DB in
> one or two result sets when I access it, but I am required to make a
> separate connection to the DB for each tuple I want to add or update.

You shouldn't be opening a new connection just to update a tuple. As long
as you dont transfer more than one thing at a time between your app and
the backend, all should be fine.

> I'm using PreparedStatement, but that doesn't change things.

> What I would really like to do is submit a whole batch of updates in a
> single request, to cut the database access time. I'm adding them all to
> the same table, so I'm hoping to something like "insert into ... from mytable"
> where mytable is a set of tuples local to my application.
>
> Is there any way to do this? I didn't see anything like this in the JDBC
> documentation. I'm really suffering for performance over this, does anyone
> have any ideas?

In JDBC, there is no real way of batch updates.

If your table has indices, you could try wrapping the updates within a
transaction:

  myconnection.setAutoCommit(false);    // Start using transactions
  ... all updates here
  myconnection.commit();        // Commit the updates

  ... later, when transactions are nolonger required
  myconnection.setAutoCommit(true);    // Stop using transactions

> For the time being my workaround is to cache all the data in the application
> and have a separate thread post the updates. I am unhappy about this because
> it defeats proper locking and makes for really dirty reads.

Thread safety is on the cards for the driver, but it won't be there for
6.3 (I'm trying to get as much of the api implemented as possible).

Saying that, there are very few JDBC drivers out there (for any database)
that are Thread safe. On the JDBC list, it was recomended that for any
program that wants to use multiple drivers, to use them from one Thread
only.

> Any ideas would be appreaciated!

As a non JDBC extension to the driver, I'm looking at providing a way to
use postgresql's copy method for large updates to tables.

Also, you say you are using the database to store Persistent data. Is this
data that could be stores as a Serializable object? If so, you could have
a few columns in the table hold key data for searching, and an oid to
point to a Large Object holding the Serialized object. This now works for
6.3

--
Peter T Mount  petermount@earthling.net or pmount@maidast.demon.co.uk
Main Homepage: http://www.demon.co.uk/finder
Work Homepage: http://www.maidstone.gov.uk Work EMail: peter@maidstone.gov.uk