How to improve the select query performance in Java?

I am using BerkeleyDB Database and I am performing select query that require 409 ms. How to improve the select query performance?

I am using the following code:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

开发者_开发百科public class ReadData {

Connection con=null;
ResultSet rs=null;
Statement smt = null;

public void readData() 
{
     try
     {
         Class.forName("SQLite.JDBCDriver");
         con = DriverManager.getConnection("jdbc:sqlite:/D:\\DB\\Mediation.db");
         smt = con.createStatement();

         long startTime = System.currentTimeMillis();
         rs = smt.executeQuery("select * from CDRData");             
         while(rs.next())
         {
             System.out.println(rs.getString(1)+" , "+rs.getString(2)+" , "+rs.getString(3)+" , "+rs.getString(4)+" , "+rs.getString(5)+" , "+rs.getString(6)+" , "+rs.getString(7)+" , "+rs.getString(8)+" , "+rs.getString(9)+" , "+rs.getString(10)+" , "+rs.getString(11)+" , "+rs.getString(12)+" , "+rs.getString(13)+" , "+rs.getString(14)+" , "+rs.getString(15)+" , "+rs.getString(16)+" , "+rs.getString(17)+" , "+rs.getString(18)+" , "+rs.getString(19)+" , "+rs.getString(20)+" , "+rs.getString(21)+" , "+rs.getString(22)+" , "+rs.getString(23));
         }
         long finishTime = System.currentTimeMillis();
         System.out.println("The time taken by select query : "+(finishTime-startTime)+ " ms");
     }
     catch(Exception e)
     {
         System.out.println("Error ---- "+e);
     }
}

public static void main(String[] args) {
    ReadData csvread = new ReadData();
    csvread.readData();
}
}

@Dhananjay Joshi my first recomendation it's that need the change the select from select * and put the fields that you really need when you do a select * you bring a lot of fields that maybe you don't need and requires more memory

In general terms when using JDBC you may see a performance increase by

• using a StoredProcedure - this elimentates some pre-processing by the database engine
• using a PreparedStatement - this can elimenate some pre-processing if you are using a query multiple times.

You can also improve time by optimising your query, perhaps by adding appropriate indexes. Using explain can help you understand the actions and cost that the query will ancounter.

However, in your example you are just executing a simple select with no predicate, so none of these will help you.

I don't think there is anything to improve with your code in terms of DB access. Selecting all rows is a trivial task for a database which basically just takes a certain amount of time. using a PreparedStatement would give you some improvement, if you repeatedly have the same/similar queries, but not in your test scenario.

You have a little overhead in your java part though which comes from allocating/concatenating string objects. You might replace your loop body by

 System.out.print(rs.getString(1));
 System.out.print(", ");
 System.out.print(rs.getString(2))
 // ...

or a StringBuilder...

Most of the time could be spent printing the results.

Try running the test without printing the data.

You may put a printed timestamp after the "executeQuery" statement. And trying to figure out what spends more time in your program (retrive data from database or java instructions).

Theoretically, retrieving data from database would be the bottleneck if the data is large enough.