3
\$\begingroup\$

I have created a program in java for entering 1000 random numbers into two different database one for odd numbers and one for even. The code is executing fine but it takes nearly a minute to execute. How can I minimize the execution time?

The source code:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.text.DecimalFormat;
import java.text.NumberFormat;
import java.util.Random;

public class Test1 extends Thread {

public static void main(String[] args) throws ClassNotFoundException, SQLException {
long start = System.currentTimeMillis();

int evencount = 0;
int oddcount = 0;
int breakcon = 0;
int breakcon1 = 0;

Class.forName("com.mysql.jdbc.Driver");
Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/db1" + "?useSSL=false", "root",
        "1234");
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/db2" + "?useSSL=false", "root",
        "1234");

try {

    Class.forName("com.mysql.jdbc.Driver");

    for (int n = 1; n <= 1000; n++) {
        Random r = new Random();
        int val = r.nextInt(100000);

        if (val % 2 == 0) {
            PreparedStatement ps = con.prepareStatement(" insert into try values (?)");
            ps.setInt(1, val);
            ps.executeUpdate();
            ps.addBatch();
            breakcon = breakcon + 1;
            if (breakcon % 500 == 0 || breakcon == val)
                ps.executeBatch();
            evencount++;

        } else {
            try {

                Class.forName("com.mysql.jdbc.Driver");
                PreparedStatement ps3 = conn.prepareStatement(" insert into try1 values (?)");
                ps3.setInt(1, val);
                ps3.executeUpdate();
                ps3.addBatch();
                breakcon1 = breakcon1 + 1;
                if (breakcon1 % 500 == 0 || breakcon1 == val)
                    ps3.executeBatch();

                oddcount++;

            }

            catch (Exception e2) {
                System.out.println(e2);
            }
        }
    }

}

catch (Exception e) {
    System.out.println(e);
}

long end = System.currentTimeMillis();
NumberFormat formatter = new DecimalFormat("#0.00000");
System.out.println("Execution time is " + formatter.format((end - start) / 1000d) + " seconds");
System.out.println(oddcount + evencount);

}
}
\$\endgroup\$
  • 3
    \$\begingroup\$ Why do you enter the numbers into two different databases, as opposed to entering them into different tables of the same database? \$\endgroup\$ – Bex Sep 5 '17 at 7:51
3
\$\begingroup\$

Structure your code

  • Name your variables appropriately! It is not a good idea to have the connections called con and conn. Likewise ps and ps3 are not good names for your prepared statements. It will confuse you when trying to debug this.
  • Make a method that inserts a value into a database.
  • Do not catch Exception. Make sure you know what exception you catch and why. Right now you declare your main to throw exceptions, but I don't think it really does.

Prepared statements are reusable

You prepare a statement to let the database optimize for it. It takes a bit of time, but it saves time when you use it. What you do, is you prepare the statement inside your loop, making it a lot slower. Move the preparation of your statement

PreparedStatement ps = con.prepareStatement("insert into try values (?)");

outside your loop. This will make your program run faster.

Random is reusable

You create a new instance of Random on every rotation of the loop. Move this outside the loop - it will make your code run faster.

Remove unnecessary things

Your code is littered with statements executing expensive methods to no use.

What does

Class.forName("com.mysql.jdbc.Driver");

do?

What is the purpose of

ps.addBatch();
breakcon = breakcon + 1;
if (breakcon % 500 == 0 || breakcon == val)
  ps.executeBatch();

What is the purpose of oddcount and evencount?

| improve this answer | |
\$\endgroup\$
  • \$\begingroup\$ Class.forName("com.mysql.jdbc.Driver") used to be necessary with old versions of JDBC (before 4.0), but is no longer necessary \$\endgroup\$ – jrtapsell Sep 5 '17 at 11:14
  • \$\begingroup\$ @jrtapsell Thank you, I didn't know. But how many times? \$\endgroup\$ – Bex Sep 5 '17 at 11:25
  • \$\begingroup\$ Only once, it registered the driver, now they use services instead \$\endgroup\$ – jrtapsell Sep 5 '17 at 11:30
  • \$\begingroup\$ I added more explanation in an answer \$\endgroup\$ – jrtapsell Sep 5 '17 at 18:46
3
\$\begingroup\$

Indentation

What is the problem?

Your code may have lost its indentation as it was uploaded, but as it shows above the indentation is missing.

Why is this a problem?

It makes reading and reasoning about how it works and what it does harder. It also makes it easier for bugs to hide in code.

How to fix

You want to indent your code, most IDEs and even some more basic text editors have the ability to do this automatically.

Class.forName

What is the problem?

This used to be needed before JDBC4.0 (before Java 6), but now drivers are automatically loaded as services, so they do not need to be explicitly forced to load by the classloader.

Why is this a problem?

It forces a particular driver to be reloaded at that point, which wastes time, and adds confusion, it also adds the risk it will throw ClassNotFoundException in the future if the driver changes class name.

How to fix it

Remove this line Class.forName("com.mysql.jdbc.Driver");

Unnecessary Extension

What is the problem?

The class extends Thread, but does not override the method run. You do not need to extend Thread for a single-threaded application, only if you need to make a thread that is extra to the main one.

Why is this a problem?

Even if an instance of this class was made and started, it would do nothing, so the class does not need to extend Thread

How to fix

Remove extends Thread

Connections are not closed

What is the problem?

Connections extend Autoclosable, but are not closed

Why is this a problem?

It causes resource leaks

How to fix

Create the connections using Try-With-Resources syntax to ensure they are closed after use

| improve this answer | |
\$\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.