SQL Query to display minimum prices per country

Question

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 :

1. Empty the table tempcalcplan(Assuming that previous results are stored.)
2. Generating desired data with the help of 2 different tables(Tables are shown below) and storing it in the tempcalcplan table
3. Displaying the results of tempcalcplan table on a jTable.

Here Are the country and tempcalcplan table.

1. country (Contains 30,000 records)

   +---------+-------------+------+-----+---------+-------+
   | Field   | Type        | Null | Key | Default | Extra |
   +---------+-------------+------+-----+---------+-------+
   | conid   | int(10)     | NO   | PRI | NULL    |       |
   | conname | varchar(50) | YES  |     | NULL    |       |
   +---------+-------------+------+-----+---------+-------+
   

2. 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    |       |
   +---------+-------------+------+-----+---------+-------+
   

3. 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.

Answer 1

    try {
            Connection con = Mycon.getConnection();

This probably could be

    try (Connection con = Mycon.getConnection()) {

Assuming that this is the Java.sql.Connection interface.

Then the try-with-resources would be able to close the connection for you--exception or no exception.

But that won't help with your speed issues.

You need to determine where the problem lies. Is it the query at the end? Is it the population of the table? The creation of the jTable? Where?

Are there any conid values in venprices that aren't in country? It would seem that there would have to be, as there are only 30,000 records in country and 180,000 in tempcalcplan. Both have conid as their primary key, so no duplicates.

Is there an index on conid and rate in venprices?

Answer 2

As MySQL is known for a weak optimizer you should try to rewrite your query to aggregate before join:

SELECT v.conid, c.conname, v.rate, v.venid
FROM
 (
   SELECT v.conid, v.rate, v.venid 
   FROM venprices v 
   JOIN
    ( 
      SELECT conid, Min(rate) AS minRate
      FROM venprices
      GROUP BY conid
    ) AS minPrices
     ON v.conid = minPrices.conid
    AND v.rate  = minPrices.minRate
   GROUP BY v.conid
 ) AS v
LEFT JOIN country AS c 
  ON c.conid = v.conid

You can run the Derived Tables individually to see which performs slow.