Re: select 10000 rows from Oracle in shortest way

Thomas Hawtin wrote:
> Thomas Kellerer wrote:
>>
>> But I have noted, that insert speed is extremely slow when
>> autocommit is turned on. So maybe that is your problem?
>
> A commit would require waiting for a write to disc to complete. How
> slow that is depends upon what you are logging to. Solid state memory
> will be significantly faster than rotating media. When you switch
> auto commit off (which should be a matter of course) make sure to
> commit (or rollback) when you've finished.
>
> If your driver supports it, then bulk updates should be very much
> faster with a networked driver. If they don't perhaps you could
> generate an SQL statement which takes a number of rows at once. Or
> perhaps moving the code writing to the database onto the same
> physical machine. If you're really, really, really desperate, write
> stored procedure to decode a long argument and insert multiple rows.

Another (additional) option for more efficient resource usage could be to
use two threads (a reader and a writer thread) connected by a thread safe
queue. That way the inserter can insert data while the reader waits for
the next packet to be sent from the server.

Yet another option might be to use SSL with compression for the slow link.
Never did this myself with a JDBC connection though.

HTH

robert

.