Skip to Main Content

Java Database Connectivity (JDBC)

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Slow performance of PreparedStatement

843859Apr 6 2007 — edited Apr 9 2007
I am having difficulty with extremely slow performance or a relatively simply Microsoft SQL Server 2000 call from my Java applet. Most of the application runs fine, but there are certain parts that are repeatedly giving me a long delay before completing their execution. The code is as follows:
// Create the try block for the execution of the SQL code
        try{
            // Create the command to be executed
            String command = new String( "select Description, Enabled " );
            command += "from tLineInfo where( Line = ? )";
            
            // Create the SQL text to be executed
            PreparedStatement get = _connection.prepareStatement( command );
            get.setInt( 1, lineNo );
            
            // Execute the SQL command
            ResultSet lineInfo = get.executeQuery();
       
            // Display the information accordingly
            if( lineInfo.next() ){
                // Populate the user data fields
                description.setText( lineInfo.getString( "Description" ) );

                
                      <! more display code here >
      

}   // End of if statment
            else{
                // Clear the user data fields
                description.setText( " " );
               
        
                       <! more display code here >
         


            }   // End of else statements
            
            // Close the result set in preparation for the next query
            lineInfo.close();
            
        }   // End of try block
        catch( Exception e ){
            // Display a dialog box informing the user of the problem
            Object[] options = { "     OK     " };
            JOptionPane.showOptionDialog( null, e.getMessage(), 
                    "Error",
                    JOptionPane.OK_OPTION, JOptionPane.ERROR_MESSAGE,
                    null, options, options[ 0 ] );  
                
        }   // End of Exception catch
        
        // Get the related area information
        populateAreaCombo( lineNo );        

private void populateAreaCombo( int lineNo ){
        // ---------------------------------------------------------------------
        // Format the areaComboBox
         try{
            // Create a command to get the devices from the database
            String command = new String( "select Area, [Name], [Description] " );
            command += "from tAreas where( Line = ? )";
      
            // Create the SQL statement to grab the information, and
            // populate the search parameter
            PreparedStatement getAreas = 
                    _connection.prepareStatement( command );
            getAreas.setInt( 1, lineNo );
           
            // Execute the command
            ResultSet areas = getAreas.executeQuery();
          
            // Loop through the result set, and add collect the areas
            Vector< String > controlAreas = new Vector();
            while( areas.next() ){
                // Add the area to the comboBox
                controlAreas.add( Integer.toString( areas.getInt( "Area" ) ) +
                        " - " + areas.getString( "Name" ) + ": " + 
                        areas.getString( "Description" ) );
                
            }   // End of while loop

                 
                  <! more display code here >
                
The application always seem to pause at the second PrepraredStatement call:
PreparedStatement getAreas = 
                    _connection.prepareStatement( command );
This seems to be a very simple operation, and it is not even the execution of the query where the long delay is realized. Rather, it is in the actual creation of the object prior to the execution.

The delay is very repeatable at this exact statement each time.

Additionally, of interest, is that the delay is only realized on computers remotely connected to the database. If I run this code on the localhost, then there is no delay. As soon as I distribute it, then the delay is incurred. That being said, there is not a network related issue that I can identify here. I have even isolated the server to be on the network with just one other PC, and the delay still persisted.

Does anyone have any ideas?

Thanks
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on May 7 2007
Added on Apr 6 2007
6 comments
705 views