Why SQL select takes more CPU time in java?

Question

Hi,

I have a java web application that selects one column from table (with 6 million rows) and it takes a lot of CPU time. This select (SELECT id FROM mytable WHERE filename = 'unique_filename') takes significantly less time when executed in query browser.

What can cause this?
Where should I start to look for bottlenecks?

Database is MSSQL 2005 Standard
Java container is Tomcat 5.5 (with sqljdbc 1.2)

More details:
1.Java code

 ResultSet rs = null;    
 PreparedStatement stmt = null;
 Connection conn = null;
 Integer myId=null;
 String myVeryUniqueFileName = strFromSomeWhere;
 try
 {
    conn = Database.getConnection();
    stmt = conn.prepareStatement("SELECT id FROM mytable WHERE filename = ?");
    stmt.setString(1, myVeryUniqueFileName);

    rs = stmt.executeQuery();
    if (rs.next())
    {
       myId= new Integer(rs.getInt(1));
    }              }
    if (rs.next())
    {
       throw new DBException("Duplicate myId: " + myId);
    }
    return myId;
 } catch (Exception e) {
    // handle this
 }

The Database object uses DriverManager to receive connection object.

2.SQL table has about 30 columns.

 CREATE TABLE [dbo].[calls](    
    [id] [int] NOT NULL,     
    ...    
    [filename] [varchar](50) NOT NULL,
    ...     
 CONSTRAINT [PK_xxxxxxxxxxxx] PRIMARY KEY CLUSTERED     
 (    
    [id] ASC    
 )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY],     
 CONSTRAINT [UQ_xxxxxxxxxxxx] UNIQUE NONCLUSTERED       
 (      
    [filename] ASC     
 )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]     
 ) ON [PRIMARY]

filename column is unique so result set from is allways 1 or null.

Answer 1

The symptoms you describe are most often caused by an incorrectly cached query plan.

Rebuild your indexes or update your statistics.

Answer 2

You are using a statement probably and not a prepared statement. A statement does not get precompiled and cached so the query optimizer has to do the work everytime. If you use a prepared statement it will try and find the best way to execute your query and it will store that. The next time you use it it won't bother to try and work out a good way to get your results it will just the execution plan it already has.

Answer 3

Can you post your java code where you're executing this query and retrieving the results?

Possible factors causing Java code to appear to take significantly longer are:

1. Your query returns a large number of records and you're trying to retrieve them all in Java whereas query browser would only show the first 100 (whatever that number may be) and load others on demand.
2. You're comparing different times, for example "query took X ms" shown by your query browser with time it takes Java from obtaining the connection till closing it.
3. Your objects (holding the results) may be expensive to create or they may be doing some processing behind the scenes as they're populated.

Answer 4

I can't speak to MSSQL 2005 specifically, but there can be a difference in execution plan between a prepared statement where you're using bind variables and the equivalent statements where values are embedded.

To test this theory, drop the bind parameter, and instead concatenate the SQL query in Java with the actual filename (in quotes). This way you're comparing apples to apples.

Also, it would be useful with an indication of the difference in CPU time you're experiencing. Is it several orders of magnitude or less than 100%.

Answer 5

With help of way smarter developer I was able to solve this problem. Turns out I was misusing PreparedStatement (aricle).

Based on this I changed java code to:

ResultSet rs = null;    
Statement stmt = null;
Connection conn = null;
Integer myId=null;
String myVeryUniqueFileName = strFromSomeWhere;
try
{
  conn = Database.getConnection();
  stmt = conn.createStatement()
  //
  rs = stmt.executeQuery("SELECT id FROM mytable WHERE filename = '"
                         + myVeryUniqueFileName + "'");
  if (rs.next())
  {
    myId= new Integer(rs.getInt(1));
  }              
  if (rs.next())
  {
    throw new DBException("Duplicate myId: " + myId);
  }
  return myId;
} catch (Exception e) {
  // handle this
}

After this dababase load fell from average 70% to 13%