If your driver jdbc2.0 compliant, look at
batch processing. Choice between
one connection and many connections is not
obvious. Good driver is supposed to handle
many processes per one connection.
I would suggest starting with connection pool and 10 threads
with 10 connections doing batch update by chunks of
100 records. Than you can optimize parameters...
AlexV
"Raymond Chui" <raymond.chui@noaa.gov> wrote in message
news:3AEFECE4.C51252CC@noaa.gov...
>
>
> I have 4 columns in a table, id, sub_id, timestamp and value.
> The primary key is id, sub_id and timestamp combine.
> I need to insert many rows (may be 10 thousands every 4 minutes)
> as fast as I can to the same host, same port, same database, same table.
>
> A.
> Open only one JDBC (Java Database Connective) connection,
> have multiple threads (similar to UNIX child process) to do
> the insert.
> Note, too many threads will cause the system out of memory!
>
> B.
> Open only one JDBC connection, have only one single thread
> to do the insert.
>
> C.
> Open multiple JDBC connections threads, each one of them
> handle the data insert.
>
> D.
> Please tell me your way, the much better way.
>
> Currently I am doing A. I have to limited the number of threads that
> won't cause the system out of memory. But there is big bottleneck there.
>
> The UNIX system scheduling can only do one insert a time (I think).
> I am wonder choose C will be better? Tell me the D.
> Thank you very much in advance!
>
> P.S.
> The database server is PostgreSQL 7.x. and the UNIX box is Redhat Linux
> 6.5.
>
> --
> Why we want to teach our babies to talk and walk,
> then later we tell them "sit down!", "be quiet!" ?
>
> Democracy is not a better way for a solution,
> it is just another way to spread the blames.
>
> --Raymond
>
>