Low performance when execute more then 100 inserts

[Fabricio Pedroso Jorge <fp...@gmail.com>]

Hello,

I have a Java application that reads a .txt file, validates the informations
and then executes several inserts into de Derby 10.5 database. The problem
is that when i have a lot of inserts (more than 1000), the execution of
these inserts gets slower and slower until it gets unsupportable for the
user to wait.

I made a test, inserting the same lines of the file into an Oracle 10g
database, and it worked pretty well.

So, is there any configuration, or otimization on Derby that i can perform
to make it work???

Thanks for the attention

-- 
Atenciosamente,

Fabrício Pedroso Jorge.

Administrador de Banco de Dados Trainee - SEFA-PA
Bacharel em Ciência da Computação - CESUPA

Resumo Profissional (Professional Skills):
http://br.linkedin.com/pub/fabricio-jorge/19/554/58b

Re: Low performance when execute more then 100 inserts

[Kristian Waagan <kr...@oracle.com>]

On Tue, Aug 03, 2010 at 04:01:56PM -0300, Fabricio Pedroso Jorge wrote:
> Hello,
> 
> I have a Java application that reads a .txt file, validates the informations
> and then executes several inserts into de Derby 10.5 database. The problem is
> that when i have a lot of inserts (more than 1000), the execution of these
> inserts gets slower and slower until it gets unsupportable for the user to
> wait.
> 
> I made a test, inserting the same lines of the file into an Oracle 10g
> database, and it worked pretty well.
> 
> So, is there any configuration, or otimization on Derby that i can perform to
> make it work???

Hi Fabrício,

It sounds strange that inserting a few thousands lines should be such of
a problem that the database grinds to a halt.

I only have a few general questions, hopefully the answers can give us a
clue of what's going on.

 1. Are you using the embedded or the client/server configuration?
 2. How big is the file being imported?
 3. Is autocommit on, or are you committing at intervals?
 4. Is there a primary/unique key on the table? Any other indexes?
 5. If possible, can you run your application until it slows down badly,
    and then run jstack on the process?
    (could also use visualvm or similar to obtain the stack trace)
 6. Are you running with default Derby and JVM settings?
    (and, can you confirm that it isn't garbage collection that slows
     down the application)


Regards,
-- 
Kristian

> 
> Thanks for the attention
> 
> --
> Atenciosamente,
> 
> Fabrício Pedroso Jorge.
> 
> Administrador de Banco de Dados Trainee - SEFA-PA
> Bacharel em Ciência da Computação - CESUPA
> 
> Resumo Profissional (Professional Skills):
> http://br.linkedin.com/pub/fabricio-jorge/19/554/58b