wood burning stoves 2.0*
The moose likes Swing / AWT / SWT and the fly likes Retrive 4000 more records Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Java » Swing / AWT / SWT
Bookmark "Retrive 4000 more records " Watch "Retrive 4000 more records " New topic
Author

Retrive 4000 more records

Harmind kumar
Greenhorn

Joined: Nov 21, 2005
Posts: 4
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.

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;

Thanks
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 42291
    
  64
What takes too much time, the retrieving from the database, or the building of the JTree?


Ping & DNS - my free Android networking tools app
Harmind kumar
Greenhorn

Joined: Nov 21, 2005
Posts: 4
Retriving data from data bse take too much time.
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 42291
    
  64
So it's not a Swing/... problem at all.

In that case you should time the individual data statements to see which one is particularly slow. Maybe you're missing an index somewhere that could speed up retrieval.
Jeff Albertson
Ranch Hand

Joined: Sep 16, 2005
Posts: 1780
And call me near-sighted, but I don't want to see 4 or 5 thousand records
at a time! Why not retrive data lazily as nodes are being opened in the tree?


There is no emoticon for what I am feeling!
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18669
    
    8

Why not retrive data lazily as nodes are being opened in the tree?
That's what I did initially. Then I decided I needed a panel at the bottom that told me (something a bit more complex than) how many nodes were in the tree. So that meant I had to pre-load the tree. No problem, I just called the "load node" method recursively.

But that meant I was doing 9,000 calls to the database. This took a few minutes to run. So I refactored the whole thing to do 1 call to the database and build the tree from the results. That wasn't easy because you can't get the nodes in any natural order, but when I got it working it cut the time down to a few seconds.

Looks like Harmind has the "Many Small Hits to the Database" problem too. I would say it could be replaced by code that read and created the root, then read all of level 1 and added it to the root, then read all of level 2 and added it to level 1, and so on. This would cut the number of DB accesses down from 5,000 to 8.
Mathias Nilsson
Ranch Hand

Joined: Aug 21, 2004
Posts: 367
Try joining it in one query like this



Try join all the folderlevels and group it or order it so that you can
get it in the order you like!

// Mathias


SCJP1.4
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: Retrive 4000 more records