What is faster in getting data from database: Statement or PreparedStatement in Java application ?

What is better to use Statement or PreparedStatement. I test both objects to connect by JDBC to database and get data.

The first case will be select query.  I use for it Microsoft example database: AdventureWorks2012. I create New Project in Netbeans and add to Library node in project, JDBC driver to MSSQL Server.

SELECT query for Statement object.

I get all records from Person( schema Person )  table from AdventureWorks2012 database. This table contains data in 19 972 rows. I check how much time take using Statement object.

package doraprojects;

import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Date;
import java.util.logging.Logger;

public class TestSelect {

    public static void main(String[] args) 
    {
        long time_input = System.currentTimeMillis();
        System.out.println(new Date(time_input));
            new DataFromMSSQL(); 
        long time_output = System.currentTimeMillis();
        System.out.println(new Date(time_output));
        System.out.println("start:"+time_input+", stop:"+time_output+", diffrence:"+(time_output-time_input)+" ms");  
    }    
}

class DataFromMSSQL
{
    private String url;
    private Driver driver;
    private Connection connection;  
    private Statement statement;
    private ResultSet rs;
    
public DataFromMSSQL()
   {
       url = "jdbc:sqlserver://localhost:1433; databaseName=AdventureWorks2012; user=sa; password=YourPasswordToDB" ;  
       
       try {
             driver = new com.microsoft.sqlserver.jdbc.SQLServerDriver();
	     DriverManager.registerDriver(driver );      
          } catch (Exception e)  { 
                                 System.out.println("Problem with download driver for MSSQL: " + e.getMessage());
                                 }
        
        
       try {   
             connection= DriverManager.getConnection(url);          
           } catch (Exception e) { 
                                 System.out.println("Problem with connection to MSSQL: " + e.getMessage() );
                                 }
    
       try {  
             statement = connection.createStatement();
           } catch (Exception e) { 
                                  System.out.println("Problem with Statement of MSSQL" + e.getMessage()  );
                                 }
       
       try {   
             rs = statement.executeQuery("select * from Person.Person");
             
             while (rs.next()) 
              {
                int id = rs.getInt(1); 
                String type = rs.getString(2);
                byte nameStyle = rs.getByte(3);
                String title = rs.getString(4);
                String fName = rs.getString(5);
                String mName = rs.getString(6);
                String lName = rs.getString(7);
                String emailP = rs.getString(9);
                String rowGuid = rs.getString(12);
                String dateModified = rs.getString(13);
                
System.out.println("id="+id+", type="+type+", nameStyle="+nameStyle+", title="+title+", fName="+fName+", mName="+mName+", lName="+lName+", emailP="+emailP+", rowGuid="+rowGuid+", dateModified="+dateModified );
              } 

            rs.close();
            statement.close();     
       
           } catch (Exception e) { 
                                  System.out.println("Problem with ResultSet of MSSQL" + e.getMessage()  );
                                 }                
       try{
            if(!connection.isClosed()) { connection.close(); System.out.println("Connection to MSSQL closed." );  }
          } catch (Exception e) {  System.out.println("Problem with close connection of MSSQL" );  }
      
   }
}

Result time:

 start:1470412254811, stop:1470412261535, diffrence: 6724 ms

After run application You see this time. And what is with PreparedStatement ?

SELECT query for PreparedStatement object.

As in above example I get all records from Person table from AdventureWorks2012 database and check how much time take using PreparedStatement object.

package doraprojects;

import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Date;

public class TestSelect {

    public static void main(String[] args) 
    {
        long time_input = System.currentTimeMillis();
        System.out.println(new Date(time_input));
            new DataFromMSSQL(); 
        long time_output = System.currentTimeMillis();
        System.out.println(new Date(time_output));
        System.out.println("start:"+time_input+", stop:"+time_output+", diffrence:"+(time_output-

time_input)+" ms");  
    }    
}

class DataFromMSSQL
{
    private String url;
    private Driver driver;
    private Connection connection;  
    private PreparedStatement ps;
    private ResultSet rs;
    
public DataFromMSSQL()
   {
       url = "jdbc:sqlserver://localhost:1433; databaseName=AdventureWorks2012; user=sa; password=YourPassToDB" ;      
       try {
             driver = new com.microsoft.sqlserver.jdbc.SQLServerDriver();
         DriverManager.registerDriver(driver );      
          } catch (Exception e)  { 
                                 System.out.println("Problem with download driver for MSSQL: " + e.getMessage());
                                 }
               
       try {   
             connection= DriverManager.getConnection(url);          
           } catch (Exception e) { 
                                 System.out.println("Problem with connection to MSSQL: " + e.getMessage() );
                                 }
    
       try {   
             ps = connection.prepareStatement("select * from Person.Person");  
             rs = ps.executeQuery();
             while (rs.next()) 
              {
                int id = rs.getInt(1); 
                String type = rs.getString(2);
                byte nameStyle = rs.getByte(3);
                String title = rs.getString(4);
                String fName = rs.getString(5);
                String mName = rs.getString(6);
                String lName = rs.getString(7);
                String emailP = rs.getString(9);
                String rowGuid = rs.getString(12);
                String dateModified = rs.getString(13);
               
System.out.println("id="+id+", type="+type+", nameStyle="+nameStyle+", title="+title+", fName="+fName+", mName="+mName+", lName="+lName+", emailP="+emailP+", rowGuid="+rowGuid+", dateModified="+dateModified );
             
              }  
   rs.close();
   ps.close();
           } catch (Exception e) { 
                                  System.out.println("Problem with ResultSet of MSSQL" + e.getMessage()  );
                                 }        
       try{
            if(!connection.isClosed()) { connection.close(); System.out.println("Connection to MSSQL closed." );  }
          } catch (Exception e) {  System.out.println("Problem with close connection of MSSQL" );  }
    
   }

}

Result:

 start:1470414287545, stop:1470414293766, diffrence: 6221 ms

In this test better findings achives PreparedStatement object. Difference between them is 503 miliseconds.

Leave a Reply

Your email address will not be published.

Time limit is exhausted. Please reload CAPTCHA.