This week's book giveaway is in the OCPJP forum.
We're giving away four copies of OCA/OCP Java SE 7 Programmer I & II Study Guide and have Kathy Sierra & Bert Bates on-line!
See this thread for details.
The moose likes JDBC and the fly likes access database with java Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "access database with java" Watch "access database with java" New topic
Author

access database with java

Gaurav Singh Rana
Greenhorn

Joined: Nov 21, 2008
Posts: 4
hi guys,
I am using a Java code to retrieve values from the Microsoft access database.My code is working fine but the thing is that code is taking 15 minutes to read 10000 rows of table which is really slow according to my professor.I have tried everything but i am not able to increase the speed of reading.So please help me in this regard ,I am posting the code below...



void dataread(String query)
{
int i=0,k=0;
try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
conn = DriverManager.getConnection("jdbc dbc:SPRdb");
PreparedStatement prepStmnt = conn.prepareStatement(query);
PreparedStatement prepStmnt1 = conn.prepareStatement(query);
ResultSet rSet=prepStmnt.executeQuery();
ResultSet rSet1=prepStmnt1.executeQuery();
while(rSet1.next()){k++;}
rSet1.close();
itemid=new String[k];
ul =new double[k];
uw =new double[k];
uh =new double[k];
vol =new double[k];


while(rSet.next())
{
vol[i]=Double.parseDouble(rSet.getString(1));
itemid[i]=rSet.getString(4);
ul[i]=Double.parseDouble(rSet.getString(5));
uw[i]=Double.parseDouble(rSet.getString(6));
uh[i]=Double.parseDouble(rSet.getString(7));

i++;
}
rSet.close();
conn.close();

}
catch (Exception e) {}



}
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30795
    
157

Gaurav,
Welcome to JavaRanch! We have a whole forum dedicated to databases. I'll move this for you.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30795
    
157

Now on to your question. Can you share the query contents? This will help us give more specific advice. That said, two things really jump out at me:
1) There is an unnecessary SQL call in here. You really only need to do one call - the query itself. If you put the contents in an ArrayList, you don't need to know the size in advance. In the event you are not allowed to use an ArrayList and then convert to an array (maybe this is a contrived academic exercise), it is more efficient to count up the records in the first query rather than by doing it in Java.
2) You are returning more columns in the query than you are using. When there are lots of rows, this extra traffic really adds up. Try removing the columns you are not reading from your select clause.
Gaurav Singh Rana
Greenhorn

Joined: Nov 21, 2008
Posts: 4
thanks for the reply ...but the thing is that I have database in which I am trying to find out the number of items for particular ordernumber.So I don't know what will be the number the number of times that's why I put two different resultSet.About the columns I have only 5 column now (I have changed it) so I am reading all of them except the first one.So this is my main function :

for(int i=0;i<db.ordern.length;i++)
{
System.out.println("Order Number : "+db.ordern[i]);
String query ="select * from Pitt where OrderNumber= " +db.ordern[i];
db.dataread(query);
db.cubingalgo();
}
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30795
    
157

Originally posted by Gaurav Singh Rana:
but the thing is that I have database in which I am trying to find out the number of items for particular ordernumber.So I don't know what will be the number the number of times that's why I put two different resultSet.



It looks like you are executing the same query twice. I understand you don't know the number of items when you start. However, you know them by the time you loop through the result set. Why do this twice?


About the columns I have only 5 column now (I have changed it) so I am reading all of them except the first one.

I still recommend listing the columns in the select statement. Even if there is only one unread column, that is still 10000 things (one per row) that are being read/transferred unnecessarily.

Also, I see one more optimization. Try using:
String query ="select * from Pitt where OrderNumber= ?";
and prepStmt.setInt(1, db.ordern[i]);

This allows the database to cache the execution plan rather than seeing each SQL statement as being different.
Gaurav Singh Rana
Greenhorn

Joined: Nov 21, 2008
Posts: 4
Hi, I have done the same thing as you told me but still the speed of reading is same.I have heard that in Eclipse,Java codes are faster.I am using Netbeans 6.1.I am giving the code below:

void dataread(String s)
{
int k=0;
try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
conn = DriverManager.getConnection("jdbcdbc:SPRdb");
String query ="select Item,UnitLength,UnitWidth,UnitHeight from Pitt where OrderNumber= ?";
PreparedStatement prepStmnt = conn.prepareStatement(query);
prepStmnt.setString(1,s);
ResultSet rSet=prepStmnt.executeQuery();
ArrayList t1=new ArrayList();
ArrayList t2=new ArrayList();
ArrayList t3=new ArrayList();
ArrayList t4=new ArrayList();


while(rSet.next())
{
t1.add(rSet.getString(1));
t2.add(rSet.getString(2));
t3.add(rSet.getString(3));
t4.add(rSet.getString(4));

System.out.println(t1);
}
k=t1.size();
itemid=new String[k];
ul =new String[k];
uw =new String[k];
uh =new String[k];
vol =new double[k];
t1.toArray(itemid);
t2.toArray(ul);
t3.toArray(uw);
t4.toArray(uh);

for(int j=0;j<k;j++)
{
vol[j]=Double.parseDouble(ul[j])*Double.parseDouble(uw[j])*Double.parseDouble(uh[j]);
System.out.println(vol[j]);
}


rSet.close();
conn.close();
}
catch (Exception e) {}
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: access database with java