2
\$\begingroup\$

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.

\$\endgroup\$
  • \$\begingroup\$ Try executing your MySQL query directly in the console. How long does that take? \$\endgroup\$ – MTCoster Sep 22 '16 at 17:01
  • \$\begingroup\$ @MTCoster, the MySQL Command Line takes almost same time \$\endgroup\$ – Arpit Porwal Sep 22 '16 at 18:56
1
\$\begingroup\$
    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?

\$\endgroup\$
  • \$\begingroup\$ The code shown is written in the default constructor of the class. There is a jTable already with object jTable1. that is populated on a JInternalFrame after selecting an option from menubar. Yes theere are some` conid` values that are in venprices and not in country. the country table have conid as primary key and venprices have conid and venid combined as primary key. so there would be no duplicates for set (conid,venid) but there may be duplicates of conid as well as venid in venprices. There is no index on conid. \$\endgroup\$ – Arpit Porwal Sep 23 '16 at 6:33
0
\$\begingroup\$

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.

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.