Forums Register Login

Retrive 4000 more records

+Pie Number of slices to send: Send
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
+Pie Number of slices to send: Send
What takes too much time, the retrieving from the database, or the building of the JTree?
+Pie Number of slices to send: Send
Retriving data from data bse take too much time.
+Pie Number of slices to send: Send
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.
+Pie Number of slices to send: Send
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?
+Pie Number of slices to send: Send
 

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.
+Pie Number of slices to send: Send
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
Can't .... do .... plaid .... So I did this tiny ad instead:
a bit of art, as a gift, the permaculture playing cards
https://gardener-gift.com


reply
reply
This thread has been viewed 1302 times.
Similar Threads
Error in Remote Server (LinkedList)
How To Solve : java.sql.SQLException: Exhausted Resultset
Retrive 4000 more records
java.sql.SQLException: Closed Connection
multiple processes... only want one
More...

All times above are in ranch (not your local) time.
The current ranch time is
Mar 28, 2024 19:10:56.