Java SQL Optimization

Question

I am trying to use an SQL database with a Java program. I make a table that is 7 columns wide and 2.5 million rows (My next one I need to build will be about 200 million rows). I have two problems: building the SQL table is too slow (about 2,000 rows/minute) and searching the database is too slow (I need to find over 100 million rows in under a second if possible, it currently takes over a minute). I have tried creating a csv file and importing it, but I can't get it to work.

I am using xampp and phpMyAdmin on my computer (i5 + 6gb ram). I have three methods I am testing: createTable(), writeSQL(), and searchSQL().

createTable:

public static void createTable() {
    String driverName = "org.gjt.mm.mysql.Driver";
    Connection connection = null;
    try {
        Class.forName(driverName);

    } catch (ClassNotFoundException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }

    String serverName = "localhost";
    String mydatabase = "PokerRanks4";
    String url = "jdbc:mysql://" + serverName + "/" + mydatabase;                                                                        
    String username = "root";
    String password = "";

    try {
        connection = DriverManager.getConnection(url, username, password);
    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }

    ///////////////
    String table = "CREATE TABLE ranks(deckForm bigint(10) NOT NULL,rank0 int(2) NOT NULL,rank1 int(2) NOT NULL,rank2 int(2) NOT NULL,rank3 int(2) NOT NULL,rank4 int(2) NOT NULL,rank5 int(2) NOT NULL,PRIMARY KEY (deckForm),UNIQUE id (deckForm),KEY id_2 (deckForm))";
    try {
        Statement st = connection.createStatement();
        st.executeUpdate(table);
    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
    ///////////////

    try {
        connection.close();
    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
}

writeSQL():

public static void writeSQL() {
    String driverName = "org.gjt.mm.mysql.Driver";
    Connection connection = null;
    try {
        Class.forName(driverName);

    } catch (ClassNotFoundException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }

    String serverName = "localhost";
    String mydatabase = "PokerRanks4";
    String url = "jdbc:mysql://" + serverName + "/" + mydatabase;                                                                        
    String username = "root";
    String password = "";

    try {
        connection = DriverManager.getConnection(url, username, password);
    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }


    /////////////// Prepared Statement with Batch   
    PreparedStatement statement = null;
    String sql = "INSERT INTO ranks VALUES (? ,0, 0, 0, 0, 0, 0)";      
    long start = System.currentTimeMillis();
    try {
        statement = connection.prepareStatement(sql);
        for (int i = 0; i < 100; i++) {
            for (int j = 0; j < 100; j++) {
                statement.setLong(1, (i*100 + j));
                statement.addBatch();
            }
            System.out.println(i);
            statement.executeBatch();
        }
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
      if (statement != null) {
        try {
          statement.close();
        } catch (SQLException e) {
        } // nothing we can do
      }
      if (connection != null) {
        try {
          connection.close();
        } catch (SQLException e) {
        } // nothing we can do
      }       
    }
    System.out.println("Total Time: " + (System.currentTimeMillis() - start) / 1000 );
    ///////////////

}

searchSQL():

public static void searchSQL() {
    String driverName = "org.gjt.mm.mysql.Driver";
    Connection connection = null;
    try {
        Class.forName(driverName);

    } catch (ClassNotFoundException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }

    String serverName = "localhost";
    String mydatabase = "PokerRanks2";
    String url = "jdbc:mysql://" + serverName + "/" + mydatabase;                                                                        
    String username = "root";
    String password = "";

    try {
        connection = DriverManager.getConnection(url, username, password);
    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }



    /////////////// Option 1, Prepared Statement
    ResultSet rs = null;
    PreparedStatement pstmt = null;
    String query = "SELECT rank0, rank1, rank2, rank3, rank4, rank5 FROM ranks WHERE deckForm = ?";
    long start = System.currentTimeMillis();
    try {
        pstmt = connection.prepareStatement(query);             
        for (int i = 0; i < 100000; i++) {              
            pstmt.setLong(1, 1423354957);
            rs = pstmt.executeQuery();
            while (rs.next()) {             
                int[] arr = {rs.getInt(1), rs.getInt(2), rs.getInt(3), rs.getInt(4), rs.getInt(5), rs.getInt(6)};               
            }
        }
    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }                       
    System.out.println("Total Time: " + (System.currentTimeMillis() - start) / 1000 );
    ///////////////

    /*
    /////////////// Option 2
    Statement st = null;
    long start = System.currentTimeMillis(); 
    try {
        st = connection.createStatement();
        ResultSet rs = null;
        long deckForm = 1012213456;             
        for (int i = 0; i < 100000; i++) {          
            rs = st.executeQuery("SELECT rank0, rank1, rank2, rank3, rank4, rank5 FROM ranks WHERE deckForm = " + deckForm);
            while (rs.next()) {
                int[] arr = {rs.getInt(1), rs.getInt(2), rs.getInt(3), rs.getInt(4), rs.getInt(5), rs.getInt(6)}; 
            }
        }
    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }        
    System.out.println("Total Time: " + (System.currentTimeMillis() - start) / 1000 );
    ///////////////
    */


    try {
        connection.close();
    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
}

Sorry that's so long. I've tried everything I can think of to make this faster but I can't figure it out. Any suggestions?

Which RDBMS? Which version? Do you have any indices on your tables? What does the query optimizer tell you about the select statement? — user554546
– user554546, Commented Jun 7, 2012 at 22:39

maksimov · Accepted Answer · 2013-01-28 17:36:16Z

1

Well, there's a few improvements you could make:

You are creating a connection each time you want to search, write or create, you should use a pooled connection and datasources.
Optimize your queries by doing explain plans, and optimize your table relations and indexes.
You can use stored procedures and call them.

Well that's all I can help with, certainly there are more tips.

edited Jan 28, 2013 at 17:36

maksimov

5,8211 gold badge33 silver badges38 bronze badges

answered Jan 28, 2013 at 17:15

Diego

3765 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jim Garrison · Accepted Answer · 2012-06-07 22:50:10Z

0

As to the insert speed, you need to disable all the indexes prior to doing the insert and re-enable them after you're done. Please see Speed of Insert Statements for a lot of detailed information on improving bulk insert speed.

The query speed is probably limited by your CPU and disk speeds. You may have to throw much more hardware at the problem.

answered Jun 7, 2012 at 22:50

Jim Garrison

87k20 gold badges162 silver badges197 bronze badges

2 Comments

user1441453 Over a year ago

Thanks. I removed the indexes when creating the table and even dropped it from 7 columns to 2 and I'm still getting the same speed (about 2000 rows/min) does that seem right? I plan on putting this on a server that will be much faster than my pc, but I've heard people say building this table should be much much faster than it is. Also, fwiw building this data in a csv takes about a minute compared to 17 hours for sql

Jim Garrison Over a year ago

That's only 33 rows/second and seems extremely slow unless there's a bottleneck in I/O or the system is otherwise heavily loaded. You might consider editing your post to include detailed hardware specs (CPU model and speed, bus speed, memory speed and capacity, disk RPM and interface type).

Simon Dorociak · Accepted Answer · 2012-06-07 23:18:30Z

building the SQL table is too slow (about 2,000 rows/minute)

So point of view on inserting a great number of rows is sure use Heap table, it's basic table, also it named as persistent page-array usually created just by CREATE TABLE, it's not effective for searching as you meant that search is slow but for inserting is very efficient because it add rows to first free position that what find or on the end of table. But on other hand, searching is very inefficietly, because is not guaranteed sort of items/rows.

searching the database is too slow (I need to find over 100 million rows in under a second if possible, it currently takes over a minute)

So for this you should create table in that is searching is efficiently. In a case if you using Oracle, so it offers many constructions for physical implementation for example Index organized tables, Data clustering, Clustered tables - Index / Hash / Sorted hash ... SQL Server i'm not sure but also clustered tables and MySQL i don't know exactly, i don't want to tell you something worst. I don't say that MySQL is bad or worse like Oracle for example but just not offer some techniques for physical implementation like Oracle for example

So, i mean that it's quite hard to say some recommendations for this approach but you seriously think and study something about physical implementations of database systems, have look at relational algebra for optimize your statements, which types of tables you should create, @duffymo meant right that you can let explain your query execution plan by EXPLAIN PLANE FOR and based on result to optimize. Also how to use indexes, it's strong database construction but each index mean much more operations for any modifying of database so well to rethink for which attribute you create index etc.

Via Google, you find many useful articles about data modeling, physical implementation etc.

Regards man, I wish best of luck

Collectives™ on Stack Overflow

Java SQL Optimization

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related