?
Solved

Performance issues:JDBC or SQL Server

Posted on 2005-05-02
8
Medium Priority
?
501 Views
Last Modified: 2008-02-01
Hi Experts,

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?

Thanks
David
0
Comment
Question by:davidnaude
  • 4
  • 3
8 Comments
 
LVL 29

Expert Comment

by:bloodredsun
ID: 13915998
>>Multi threading within the process itself.
I don't know enough about your process to comment about this, but threading can help performance.

>>Changed the statement to rather use batching as opposed to inserting each item individually.
Using batching is a good idea especially if you have any network lag, otherwise you are perpetually wating for the reply for each insert. Are you using a Prepared Statement? We have found this to be the most efficient way of updating a database and managed to get from 8 hours to 50 minutes on our big update. Here's an example...

      PreparedStatement stmt = connection.prepareStatement( "insert into blahtable (blah1, blah2, blah3) values(?, ?, ?)" );  
      for ( //iterate over your records) {
      
            //use PreparedStatement            
          stmt.setBigDecimal(1, new BigDecimal(arg1) );
            stmt.setString( 2, arg2 ) ;
            stmt.setString( 3, artg3 ) ;
            //add to batch
          stmt.addBatch();
          //increment count
          recordCount++;
            //execute if limit is reached, recordExecuteLimit is typically 100
            if (recordCount % recordExecuteLimit == 0) {
                  stmt.executeBatch();
                  stmt.clearBatch();
            }
      }
      //execute leftovers
      stmt.executeBatch();
      //close statement
      stmt.close();  


>>Implemented Connection Pooling so a new connection is not created per query.
Connection creation of often a source of bottlenecks but if you use batching as above, it's not so important as you reuse the connection.

How are you reading the data? Are you using Standard SQL, PreparedStatements or Stored Procs, or is it somehting else?
0
 

Author Comment

by:davidnaude
ID: 13916055
For reading the data I am using a standard Statement which is calling a Stored Proc, what you mentioned above regarding using a prepared statement, would this help even if my batch contains a list of stored procedures to execute?
0
 
LVL 29

Expert Comment

by:bloodredsun
ID: 13916239
>> For reading the data I am using a standard Statement which is calling a Stored Proc

You mean a CallableStatement, yes?

If you could post some code it would be helpful
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:davidnaude
ID: 13916353
This is how I execute my batch, v is a vector containing strings of stored procedures to execute

            stateToEbo = connectionToEbo.createStatement();
                //stateToEbo.setEscapeProcessing(true);
              for(int i=0;i<v.size();i++){
                    stateToEbo.addBatch((String)v.get(i));      
              }
              stateToEbo.executeBatch();

For sections where I dont use batching I use the following

            state = connection.createStatement();
            rs = state.executeQuery(query);

________________________________________________

I was under the impression that a Prepared Statement compiles the query prior to executing it, but also that a stored procedure is a compiled query ready on the server. So this is not the case?

Thanks for your help, you seem to be the only one brave enough for the question.
0
 
LVL 4

Expert Comment

by:orhanbaba
ID: 13916466
may be changing driver may help you http://jtds.sourceforge.net
0
 
LVL 29

Accepted Solution

by:
bloodredsun earned 2000 total points
ID: 13916546
>>I was under the impression that a Prepared Statement compiles the query prior to executing it, but also that a stored procedure is a compiled query ready on the server. So this is not the case?

Yes it is. Stored procedures have numerous advantages over statements, namely speed and security but only if you call them from a CallableStatement not a standard Statement. Each time you execute a Statement, it checks against the table metadata which takes time. A PreparedStatement or a CallableStatement will only do this once. You need to change you code to something like this:

            stateToEbo = connectionToEbo.prepareCall ("{call proc1(?)}");
              //stateToEbo.setEscapeProcessing(true);
             for(int i=0;i<v.size();i++){
                  //set parameters in stored proc
                  stateToEbo.setString(1, (String)v.get(i) );
                  stateToEbo.addBatch();    
             }
             stateToEbo.executeBatch();

This means that you have a batch of stored procs, rather than a batch of SQL statements that call stored procs. Ifd you need multiple params then you will need to create a data object for them and store this in the vector.

>>Thanks for your help, you seem to be the only one brave enough for the question.
lol, it's only as I was faced with somehting similar a few months back. Compared to some of the guys here, I'm a real lightweight....:-)
0
 

Author Comment

by:davidnaude
ID: 13916818
Thanks for your help here. Makes a lot of sense and will help a hell of a lot in the future.

Regs
David
0
 
LVL 29

Expert Comment

by:bloodredsun
ID: 13916842
Cheers David, glad to have helped.
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Java contains several comparison operators (e.g., <, <=, >, >=, ==, !=) that allow you to compare primitive values. However, these operators cannot be used to compare the contents of objects. Interface Comparable is used to allow objects of a cl…
Go is an acronym of golang, is a programming language developed Google in 2007. Go is a new language that is mostly in the C family, with significant input from Pascal/Modula/Oberon family. Hence Go arisen as low-level language with fast compilation…
Video by: Michael
Viewers learn about how to reduce the potential repetitiveness of coding in main by developing methods to perform specific tasks for their program. Additionally, objects are introduced for the purpose of learning how to call methods in Java. Define …
This tutorial explains how to use the VisualVM tool for the Java platform application. This video goes into detail on the Threads, Sampler, and Profiler tabs.
Suggested Courses
Course of the Month9 days, 20 hours left to enroll

569 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question