Performance issues:JDBC or SQL Server
Posted on 2005-05-02
I am using SQL Server 2000 and the inet OPTA driver for inserting data. My application is doing a lot of work however the performance is a bit laggy and I am just not sure if the bottle-neck lies in the code or in SQL itself.
Let me explain the component I am refering to.
The application will recieve and order and store it locally to one database. A thread will execute and have to copy the order to a variable database depending on whom the customer is. The issue comes in when trying to copy the data from one DB to another. Both databases are stored on the same SQL server.
As a bench mark I have 200 orders with 200 items each. The process to copy from one database to another take approximatly 1 hour. I need to try and bring this figure down to 15 minutes.
In trying to achieve this I have implemented the following changes
Multi threading within the process itself.
Changed the statement to rather use batching as opposed to inserting each item individually.
Implemented Connection Pooling so a new connection is not created per query.
The application is now quicker but not quick enough and I would like to know if the bottle-neck is SQL itself or JDBC or my implementation.
When running this application the CPU utilization is roughly 30% for the application and 65 - 70% for SQL Server. The CPU utilization only really shot up to 100% once I implemented batching, is this because SQL has now got a huge back log?