aspose file tools*
The moose likes JDBC 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 » Databases » JDBC
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.

Thanks

Harmimd
Anonymous
Ranch Hand

Joined: Nov 22, 2008
Posts: 18944
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
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3704
    
    5

Try denormalizing your data to reduce the number of joins.


My Blog: Down Home Country Coding with Scott Selikoff
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18541
    
    8

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
internet detective
Marshal

Joined: May 26, 2003
Posts: 30299
    
150

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.


[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
Harmind kumar
Greenhorn

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

Joined: May 26, 2003
Posts: 30299
    
150

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.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Retrive 4000 more records