Link to home
Start Free TrialLog in
Avatar of netslt
netslt

asked on

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
SOLUTION
Avatar of CEHJ
CEHJ
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of netslt
netslt

ASKER

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!
It already looks as though a JOIN should be used for the two queries
Avatar of netslt

ASKER

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?
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of netslt

ASKER

Thanks, that helped a lot, everything is a lot faster now.
You're welcome!