Optimize Speed for MySQL Queries in Java

Hi

I have a small Java program that loops a million times and during each loop it makes about 8 mysql select queries and 8 mysql insert queries.

I do the queries like this:


This is the select query:

==
    public static int resultcount(String query) {
        int result;
        int rowcount = 0;


        try {
            System.out.println("pre instance resultcount");
            java.lang.Class.forName("com.mysql.jdbc.Driver").newInstance();

            //new com.mysql.jdbc.Driver();


            Connection con = DriverManager.getConnection(url, userName, password);
            //String sql = "select `domain` from domaintest limit 0,1";
            String sql = query;
            PreparedStatement ps = con.prepareStatement(sql);
            ResultSet rs = ps.executeQuery();

            try {
                if (rs.last()) {
                    rowcount = rs.getRow();
                    //rs.beforeFirst(); // not rs.first() because the rs.next() below will move on, missing the first element
                    rs.first();
                    returnedid = rs.getInt("id");
                }
            } catch (Exception ex) {

                System.out.println("error resultcount rs:" + ex.getMessage() + " " + ex.getStackTrace() + " --SQL-- " + query);
                returnedid = 0;
            }



            System.out.println("found " + rowcount + " rows & id = " + returnedid + " -- SQL " + query);
            int i = 0;

            con.close();


        } catch (Exception ex) {
            //NewJFrame.showMessageDialog(null, ex.getMessage(),"Error");
            System.out.println("error resultcount :" + ex.getMessage() + " " + ex.getStackTrace() + " --SQL-- " + query);
        }

==



This is the insert query:

==
   public static int mysqlinsert(String query) {

        int result = 0;
        int number2 = 0;

        try {
            System.out.println("pre insert " + query);
            java.lang.Class.forName("com.mysql.jdbc.Driver").newInstance();

            //new com.mysql.jdbc.Driver();
            Connection con = DriverManager.getConnection(url, userName, password);
            //String sql = "select `domain` from domaintest limit 0,1";
            String sql = query;
            PreparedStatement ps = con.prepareStatement(sql);
            Statement st = (Statement) con.createStatement();

            st.executeUpdate(query, Statement.RETURN_GENERATED_KEYS);

            ResultSet rs = st.getGeneratedKeys();

            if (rs.next()) {
                result = rs.getInt(1);
            }

            System.out.println("InsertID:" + result);

            con.close();

        } catch (Exception ex) {
            //NewJFrame.showMessageDialog(null, ex.getMessage(),"Error");
            System.out.println("error mysqlinsert :" + ex.getMessage() + " " + ex.getStackTrace() + " -- SQL -- " + query);
        }


==

Is there a way to seed things up - maybe by leaving the connection open, or doing other things that bring more speed?

The way it is now it will take ages to process all date even if i run 10 instances of the program....

Thanks
netsltAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

CEHJCommented:
Certainly set up Connection intances and other db objects will have an overhead so if you're looping frequently then you could leave as many as possible open. What are the queries? That could make a significant difference too
0
netsltAuthor Commented:
If I could leave the connection open for all the queries during one loop that would probably help.

But how do you do that?

==

The queries look like this:

For the selects:

SQL: select * from table1 where domain='tumblr.com'

SQL:  select * from table2 where fullhostname ='blabla.tumblr.com'

Example for the inserts:

insert into table2 (hostname,fullhostname,domainid,ipid) values ('supersandys-space','blabla.tumblr.com',459,1340)


Thanks for some help!
0
CEHJCommented:
It already looks as though a JOIN should be used for the two queries
0
Build an E-Commerce Site with Angular 5

Learn how to build an E-Commerce site with Angular 5, a JavaScript framework used by developers to build web, desktop, and mobile applications.

netsltAuthor Commented:
hi

these 2 queries are independent, these are just 2 samples of the type of queries i make with the resultcount(String query) method.

So you see these are basic queries which probably can not be optimized.

Thats why I ask about how to leave the connection open for all queries happening through one loop.

Any idea on that?
0
mccarlIT Business Systems Analyst / Software DeveloperCommented:
@netslt,

With all due respect, I realise what you have given us is just "samples" of what you are trying to do, but it still looks as though this is a problem that can be solved by running just the one sql statement rather than moving all this data back and forth between your Java app and the DB. If you could elaborate on exactly WHAT you are trying to achieve (rather than getting stuck on the HOW) we could be able to help you make huge improvements in the performance of your task.


However, just for your information (because I really encourage to follow the advice of the above first), to leave the connection open, just move the code that creates the connection from the two methods that you posted above, out to the method that calls those methods inside the loop, and then just pass the "con" object in as an argument to those two methods. Something like this, in pseudo code...
runTask() {
    Connection con = DriverManager.getConnection();

     for (loop that goes through a million entries) {
         
          resultCount("select ...", con);

          insertMysql("insert ...", con);

     }

     con.close();
}

Open in new window

The second thing that you should probably look at, is using the PreparedStatement fully/properly. Again it is hard to say 100% since you have only provided "samples" but can I assume that all the selects queries that you do are built up something like this...
String query = "select * from table1 where domain = '" + domainName + "'";

Open in new window

If that is the case, then you are forcing the DB to re-parse that SQL text EVERY time you perform the query which is very inefficient. What you need to do is just pass the domain name value into your "resultCount" method, and then within that method you would do something like...
PreparedStatement ps = con.prepareStatement("select * from table1 where domain = ?");
ps.setString(1, domainNameValue);   // Where domainNameValue is a String that you pass in as an argument to resultCount()

Open in new window

You have shown 2 different structures of SQL in the post above, ie. different table names and column names, but I am assuming that there can only be a limited number of different "structures" needed, and in such a case you can either create a different method for each structure, or at least just pass that SQL string above with the placeholders into the resultCount() method as an argument aswell.

The reason why this can improve performance quite a lot, is because the DB will "see" the same SQL statement each time and be able to re-use a cached version of that query, and just substitute the right domainNameValue each time. The DB doesn't have to parse a "different" query each and every time and also doesn't have to create execution plans, etc all the setup stuff that a DB does prior to actually retrieving your data.

Along the same lines, you can improve the insert query as well by using...
PreparedStatement ps = con.prepareStatement("insert into table2 (hostname,fullhostname,domainid,ipid) values (?, ?, ?, ?)");
ps.setString(1, hostnameString);         // where these variables are passed in to this method as arguments
ps.setString(2, fullHostnameString);
ps.setInt(3, domainId);
ps.setInt(4, ipid);

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
netsltAuthor Commented:
Thanks, that helped a lot, everything is a lot faster now.
0
mccarlIT Business Systems Analyst / Software DeveloperCommented:
You're welcome!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Java

From novice to tech pro — start learning today.