SQL Server Performance Forum – Threads Archive

MSSQL Driver

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

---

consolidating individual update statements issued separately into a single call should significantly improve performance (3-4X)
have verified with Profiler that individual calls are indeed being consolidated?
10-30 rows should be sufficient for a single batch, no need to go to 500.
also, wrapping the batch with BEGIN / COMMIT TRAN can also improve performance because individual writes to the tran log are consolidated into a single write even though the batch does not really require an explicit TRAN

---

Make sure that you jdbc driver passes varchar/char values as ASCI and not UTF-8… In Profiler you should see where col = ‘value’
and there should be no col = N’value’, since this can’t use an index on col AND will result in SQL Server converting every value in col to UTF-8 before comparing it with N’value’ unless of course you want the column to contain unicode values… Cheers
Twan

---

]]>