JDBC performance tunning
I am just not sure which one will fetch me the best result in terms of performance.
I have to execute multiple preparedstatements having different SQL queries which include some insert and update statements in different tables. Again in each preparedstatment I have to update or insert multiple records which I am doing with batch (addBatch and executeBatch).
My concern here is whether I should execute all the preparedstatements with one Connection or will create multiple connections (One connection for each preparedstatement.).
Please advise.
It will take longer to open many connections than it will to open one connection.
If you plan to do all the updating in a single thread, then it makes no difference whether the statements use the same connection or different connections. If you plan to have several threads doing simultaneous updates on different connections, that might be faster than if they all shared the same connection. (Besides, simultaneous updates on a single connection might not work.)
However the short answer is "Try it and find out."
I agree with Dr. Clap that if you are stumped, do some benchmarking and see.
I will add one point. Unless you have an absurdly large table with dozens of indexes, performing an insert or update should not have as high a latency as, say, a search query. So, in general, I would advise using a single connection here, as opening a connection is an expensive operation. If you were instead talking about a report generation or other large select query, I would consider performing that query in its own thread and putting other queries (be they select, insert, update or delete) in another thread. But I would be surprised if it pays off to spin multiple threads to open multiple connections for inserts and updates.
- Saish
> I am just not sure which one will fetch me the best
> result in terms of performance.
>
> I have to execute multiple preparedstatements having
> different SQL queries which include some insert and
> update statements in different tables. Again in each
> preparedstatment I have to update or insert multiple
> records which I am doing with batch (addBatch and
> executeBatch).
>
> My concern here is whether I should execute all the
> preparedstatements with one Connection or will create
> multiple connections (One connection for each
> preparedstatement.).
Given that you are doing many of these are once, what is the total volume?
If it is large then it would probably be better (faster, less impact to the database) to write the data to a text file and then use the database import tools to load it.
Thanks for the all responses. Can you please elaborate more how to use the database import tool to load it. I am not aware of it. I am using Oracle 9i and Sun App server . My stateless session bean does the database operation.Thanks
> Thanks for the all responses.
>
> Can you please elaborate more how to use the database
> import tool to load it. I am not aware of it. I am
> using Oracle 9i and Sun App server . My stateless
> session bean does the database operation.
>
I believe it is called sqlloader or sqlldr. You can also use sqlplus which might be faster as well.
