• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

access database with java

 
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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) {}



}
 
author & internet detective
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Gaurav,
Welcome to JavaRanch! We have a whole forum dedicated to databases. I'll move this for you.
 
Jeanne Boyarsky
author & internet detective
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 4
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Posts: 4
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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("jdbc:odbc: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) {}
 
You had your fun. Now it's time to go to jail. Thanks for your help tiny ad.
a bit of art, as a gift, that will fit in a stocking
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic