• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Bear Bibeault
  • Devaka Cooray
  • Liutauras Vilda
  • Jeanne Boyarsky
Sheriffs:
  • Knute Snortum
  • Junilu Lacar
  • paul wheaton
Saloon Keepers:
  • Ganesh Patekar
  • Frits Walraven
  • Tim Moores
  • Ron McLeod
  • Carey Brown
Bartenders:
  • Stephan van Hulst
  • salvin francis
  • Tim Holloway

Performance while inserting in DB  RSS feed

 
Ranch Hand
Posts: 298
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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).
 
Ranch Hand
Posts: 162
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 298
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 162
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

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.
 
author
Posts: 14112
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Are you sure it's the DB access which is slowing you down?
 
Leandro Oliveira
Ranch Hand
Posts: 298
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
can you tell me more about multithreading my application??? i did not get when multithreading could help me in performance..
 
Ranch Hand
Posts: 862
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
Ranch Hand
Posts: 227
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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?
 
It is sorta covered in the JavaRanch Style Guide.
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!