Win a copy of Think Java: How to Think Like a Computer Scientist this week in the Java in General forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Retrive 4000 more records

 
Harmind kumar
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi
i want to retrive data from 20 table ,with more than 5000 records in fast way.The database i'm using is MySql 4.0(i m using jconnector to conect to databse and statment and result set is use to retrive data) & i want to display records as a JTree but it can take too much time.


Some one plz help me how we retrive data in fast way.

Thanks

Harmimd
 
Anonymous
Ranch Hand
Posts: 18944
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I guess, you are intending to get data from 20 tables USING "JOIN"s.
I dont think there is a fast way to get that many data and format into a form that will be nourished into a JTree.

 
Scott Selikoff
author
Saloon Keeper
Posts: 4014
18
Eclipse IDE Flex Google Web Toolkit
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Try denormalizing your data to reduce the number of joins.
 
Paul Clapham
Sheriff
Posts: 21107
32
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
We're all guessing at what you are doing here. Hard to suggest improvements to something you know nothing about.

In my case I found it was much faster to read all the data from the tables in one SQL statement and build the tree from that, rather than building the tree recursively with one SQL statement per node. That may or may not have anything to do with your problem.
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 34392
346
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Harmimd,
Welcome to JavaRanch!

The first thing is to find out whether the majority of time is being spent in JDBC (querying/retrieving data) or building the JTree. Try commenting out the JTree part to compare the times. Then you will know where to focus your energy on optimizing.
 
Harmind kumar
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I write the code how we building the JTree .Plz on that basis plz some one help me :


public TreeNode makeTree(){
try {
//Connection con1=(Connection)parentframe.cdb;
stmt = (PreparedStatement) parentframe.cdb.con.prepareStatement("Select CDNO,Destination from cdmaster");
rs = stmt.executeQuery();
root=new DefaultMutableTreeNode("D");

//Adding the root node to tree
while(rs.next()) {
cdno = rs.getString("CDNO");
dest = rs.getString("Destination");
top = new DefaultMutableTreeNode(new FolderInfo(cdno, dest));
root.add(top);
PreparedStatement stmt1 = (PreparedStatement) parentframe.cdb.con.prepareStatement("Select FolderName,FLID_1 FROM FolderLevel_1 where CDNO='"+cdno+"'");
ResultSet rs1 = stmt1.executeQuery();

//adding first level node of tree to root tree
while(rs1.next()) {
flid1 = rs1.getString("FLID_1");
flevel1 = rs1.getString("FolderName");
top1 = new DefaultMutableTreeNode(new FolderInfo(cdno, flevel1, flid1));
top.add(top1);
PreparedStatement stmt2 = (PreparedStatement) parentframe.cdb.con.prepareStatement("Select FolderName,FLID_2 FROM FolderLevel_2 where CDNO='"+cdno+"'AND FLID_1='"+flid1+"'");
ResultSet rs2 = stmt2.executeQuery();

//adding second level node of tree to first level tree node
while(rs2.next()) {
flid2 = rs2.getString("FLID_2");
flevel2 = rs2.getString("FolderName");
top2 = new DefaultMutableTreeNode(new FolderInfo(cdno, flevel2, flid2));
top1.add(top2);
PreparedStatement stmt3 =(PreparedStatement) parentframe.cdb.con.prepareStatement("Select FolderName,FLID_3 FROM FolderLevel_3 where CDNO='"+cdno+"'AND FLID_2='"+flid2+"'");
ResultSet rs3 = stmt3.executeQuery();

//adding third level node of tree to second level tree node
while(rs3.next()) {
flid3 = rs3.getString("FLID_3");
flevel3 = rs3.getString("FolderName");
top3 = new DefaultMutableTreeNode(new FolderInfo(cdno, flevel3, flid3));
top2.add(top3);
PreparedStatement stmt4 = (PreparedStatement) parentframe.cdb.con.prepareStatement("Select FolderName,FLID_4 FROM FolderLevel_4 where CDNO='"+cdno+"'AND FLID_3='"+flid3+"'");
ResultSet rs4 = stmt4.executeQuery();

//adding fourth level node of tree to thired level tree node
while(rs4.next()) {
flid4 = rs4.getString("FLID_4");
flevel4 = rs4.getString("FolderName");
top4 = new DefaultMutableTreeNode(new FolderInfo(cdno, flevel4, flid4));
top3.add(top4);
PreparedStatement stmt5 = (PreparedStatement) parentframe.cdb.con.prepareStatement("Select FolderName,FLID_5 FROM FolderLevel_5 where CDNO='"+cdno+"'AND FLID_4='"+flid4+"'");
ResultSet rs5 = stmt5.executeQuery();

//adding fifth level node of tree to forth level tree node
while(rs5.next()) {
flid5 = rs5.getString("FLID_5");
flevel5 = rs5.getString("FolderName");
top5 = new DefaultMutableTreeNode(new FolderInfo(cdno, flevel5, flid5));
top4.add(top5);
PreparedStatement stmt6 = (PreparedStatement) parentframe.cdb.con.prepareStatement("Select FolderName,FLID_6 FROM FolderLevel_6 where CDNO='"+cdno+"'AND FLID_5='"+flid5+"'");
ResultSet rs6 = stmt6.executeQuery();

//adding six level node of tree to fifth level tree node
while(rs6.next()) {
flid6 = rs6.getString("FLID_6");
flevel6 = rs6.getString("FolderName");
top6 = new DefaultMutableTreeNode(new FolderInfo(cdno, flevel6, flid6));
top5.add(top6);
PreparedStatement stmt7 = (PreparedStatement) parentframe.cdb.con.prepareStatement("Select FolderName,FLID_7 FROM FolderLevel_7 where CDNO='"+cdno+"'AND FLID_6='"+flid6+"'");
ResultSet rs7 = stmt7.executeQuery();

//adding seventh level node of tree to sixth level tree node
while(rs7.next()) {
flid7 = rs7.getString("FLID_7");
flevel7 = rs7.getString("FolderName");
top7 = new DefaultMutableTreeNode(new FolderInfo(cdno, flevel7, flid7));
top6.add(top7);
PreparedStatement stmt8 = (PreparedStatement) parentframe.cdb.con.prepareStatement("Select FolderName,FLID_8 FROM FolderLevel_8 where CDNO='"+cdno+"'AND FLID_7='"+flid7+"'");
ResultSet rs8 = stmt8.executeQuery();

//adding eight level node of tree to seventh level tree node
while(rs8.next()) {
flid8 = rs8.getString("FLID_8");
flevel8 = rs7.getString("FolderName");
top8 = new DefaultMutableTreeNode(new FolderInfo(cdno, flevel8, flid8));
top7.add(top8);
}
}
}
}
}
}
}
}
}
}
catch(Exception e) {
JOptionPane.showMessageDialog(null, e.getMessage());
}
return root;
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 34392
346
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Harmind,
There are currently a large number of queries since they are getting run in loops many levels in. Yet it looks like you are getting all the data from FolderX tables across the queries. I suspect it would be faster to get all the data in less queries up front and then transform it in Java.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic