Performance while inserting in DB

[Leandro Oliveira]

I have an application that does an avarage of
1 insert/ 3 seconds,
1 update /2 seconds and
1 delete/ 50 seconds
I would like to optimize since things are getting too slow. I was thinking about doing it all with stored procedures and pl. What do you sugest me??? is it a good choice??? all the tables in the DB have more then 40 columns and each update and insert statement carries an update for at least 20 columns.

Thanks in advance. (Sorry for the poor english).

[Vinod John]

My 2 recomendations are,
1) You can use stored procedures if you are comfortable with installing a part of the application in the database and calling it form Java but I prefer prepared statement but when you prepared statement it is better to re-use the connection (by maintaining a pool) because (I think) prepared
statements are prepared the first time it is used and tied up to the connection used to call it. But in either case it is better to avoid recreating Connecting object every time.
2) Try executing Insert and updates as a batch it possible this would save save some network calls.

[Leandro Oliveira]

thanks... I already have a pool of connections that attempts to use the minimum number of connections... I would like to know (for sure) which is the fatest:
prepared statement or stored procedures???

[Vinod John]

Originally posted by leandro oliveira:
thanks... I already have a pool of connections that attempts to use the minimum number of connections... I would like to know (for sure) which is the fatest:
prepared statement or stored procedures???

For a simpler queries it prepared statement is better but for a complex queries stored procedures may perform better, but think there won't be a significant difference between them. But maintanence wise it is better to use prepared statements. Outside JDBC, since you are creating multiple connection, have you thought of multithreading your application, in case you haven't done it yet.

[Ilja Preuss]

Are you sure it's the DB access which is slowing you down?

[Leandro Oliveira]

can you tell me more about multithreading my application??? i did not get when multithreading could help me in performance..

[steve souza]

First you have to figure out why your app is performing so slow. Based on the fact that one delete is taking so long I would guess the optimizations you should make at this point have nothing to do with Java. Not sure what db you use, but I know Sybase has a tool called "show plan" that lets you see what the SQL is doing behind the scenes. The delete performance is so abysmal that I'm guessing you don't have a usable index on the table for the executed query. It is probably doing a table scan. Bad news with any sizable table. Usually sql optimization is an easy first step, so that is where I would focus initially.
Steve - http://www.jamonapi.com - a fast, free java performance tuning api

[Dana Hanna]

First off, a stored procedure is good if you are doing tons of database logic. If you can use 1 prepared statement to do the same thing, you're better off with that for maintainability.

Originally posted by leandro oliveira:
I have an application that does an avarage of
1 insert/ 3 seconds,
1 update /2 seconds and
1 delete/ 50 seconds

I believe that you are saying 1 insert every 3 seconds, 1 update every 2 seconds, and 1 delete every 50 seconds. As an enterprise developer, That doesn't sound like too much to me.
What leads you to believe that the database is the problem?
What flavor database are you using?