October 30, 2003 at 8:54 pm
Hi,
Usually using batch updates in jdbc improves performance. But in our case, we trying to update 5 fields of table containing around 600 columns and around 1 lac records. I am using prepared statements and doing preparedstatement.addbatch() and calling preparedstatement.executeUpdate() everytime after 500 records. Still i am not getting much of performance improvement.
The driver we are using is type 4 mssql driver from Microsoft.
Are there other ways of improving performance
October 31, 2003 at 2:41 am
I am pretty sure updating records in batches, in stead of the whole table at once will not improve performance overall.
It does give you other advantages like locks being released, fewer locks, ... Additionally, the query will take up less time, so in a GUI, you could do some other stuff meanwhile.
You might want to optimize indexes on the table. Are there triggers involved? Is the table referenced by or does it reference another table?
October 31, 2003 at 2:57 am
Thanks for response Here is some more details about the problem Is there any way to improve this ??
my update query is
update ACCOUNT SET RAM_SCR=?, CR_LMT=? where ACCT_NBR=?
My table ACCOUNT contains around 600 columns and there also some triggers associated with this table.
My project is a thread based one. We are implementing Producer Consumer pattern for first fetching the records from the database, then each consumer will do some business logic with the each record it picks up from the shared buffer and finally updates back into the table.
I am doing a static increment counter check stating that only executeBatch() method should be called once in every 500 value in the counter.
Still i am not getting the impressive performance numbers.
October 31, 2003 at 6:56 am
How big are your log files? Are the log files and data files on the same disk?
This could be indexing, server or network related. Something you might try would be to set up SQL Profiler (if you or someone you know has SA access) and then run your program. This might give you a better idea of what to improve.
Dr. Peter Venkman: Generally you don't see that kind of behavior in a major appliance.
Patrick
Quand on parle du loup, on en voit la queue
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply