I have developed a Tender management System sort of code for one of my client. This is the piece of code :
try {
Connection con = Mycon.getConnection();
PreparedStatement ps = con.prepareStatement("TRUNCATE tempcalcplan");
ps.executeUpdate();
ps.clearBatch();
ps = con.prepareStatement("INSERT INTO tempcalcplan SELECT v.conid,c.conname, v.rate, v.venid FROM venprices v LEFT JOIN country c ON c.conid = v.conid WHERE (v.conid, v.rate) IN ( SELECT v.conid, MIN(v.rate) FROM venprices v GROUP BY v.conid) GROUP BY v.conid");
ps.executeUpdate();
ps.clearBatch();
ps = con.prepareStatement("select * from tempcalcplan");
ResultSet rs = ps.executeQuery();
jTable1.setModel(DbUtils.resultSetToTableModel(rs));
}catch(Exception e){
e.printStackTrace();
}
This piece of code is designed to function as :
- Empty the table
tempcalcplan
(Assuming that previous results are stored.) - Generating desired data with the help of 2 different tables(Tables are shown below) and storing it in the
tempcalcplan
table - Displaying the results of
tempcalcplan
table on a jTable.
Here Are the country
and tempcalcplan
table.
country
(Contains 30,000 records)+---------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+-------+ | conid | int(10) | NO | PRI | NULL | | | conname | varchar(50) | YES | | NULL | | +---------+-------------+------+-----+---------+-------+
tempcalcplan
(Contains 1,80,000+ Records)+---------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+-------+ | conid | int(10) | NO | PRI | NULL | | | conname | varchar(50) | YES | | NULL | | | rate | double | YES | | NULL | | | venid | varchar(50) | YES | | NULL | | +---------+-------------+------+-----+---------+-------+
venprices
+-------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+-------+ | conid | int(10) | NO | PRI | NULL | | | rate | double | YES | | NULL | | | venid | varchar(50) | NO | PRI | | | +-------+--------------+------+-----+---------+-------+`
The results take up to 12-15 minutes to show in the jTable. I want reduce it to 1-2 minutes.