Results 1 to 6 of 6

Thread: Java + SQL

  1. #1
    atomant is offline Member
    Join Date
    May 2010
    Posts
    45
    Rep Power
    0

    Default Java + SQL

    I'm trying to build a large SQL Database with java. It will have 2.5 million rows with 7 columns containing one 10-digit integer and 6 2-digit integers. Using Java it is creating about 2,500 rows/minute on my local MySQL. Does this sound about right? Is there a faster way to do it?

    When I tried importing a CSV file of a small portion of my data in phpMyAdmin, it timed out after 5 minutes.

    This is only a one-time build so speed isn't a huge deal for this db, but I also want to create a similar db that contains almost 200 million rows and that would take like a month to create using the java program..

  2. #2
    doWhile is offline Moderator
    Join Date
    Jul 2010
    Location
    California
    Posts
    1,642
    Rep Power
    7

    Default Re: Java + SQL

    It all depends upon your code. Things such as not using PreparedStatement where applicable, inefficient SQL calls (or sequence of SQL calls), and even String additions can all lead to poorer performance. Saying it takes X amount of time is extremely relative to your system, and may differ significantly from one computer to the next.

  3. #3
    atomant is offline Member
    Join Date
    May 2010
    Posts
    45
    Rep Power
    0

    Default Re: Java + SQL

    This is my first time ever using SQL so I just wanted to see if that sounded reasonable. 17 hours to build a 240mb database seems like it's probably inefficient code. I have an i5 with 6 gb of ram btw.

    Thanks for pointing me to PreparedStatement, that looks like it might help speed things up a bit. Though, I'm not even sure SQL is what I am looking for. I would like something that can search through a very large table a couple hundred million times in under a second, is that at all possible with SQL?

    Here is some code I wrote up for searching through my table, is there anything I can do to make this more efficient? It's just for testing how long it takes to run through a lot of queries:

    Java Code:
        	String driverName = "org.gjt.mm.mysql.Driver";
            Class.forName(driverName);
            String serverName = "localhost";
            String mydatabase = "db";
            String url = "jdbc:mysql://" + serverName + "/" + mydatabase;                                                                         
            String username = "root";
            String password = "";
            connection = DriverManager.getConnection(url, username, password);
            
            st = connection.createStatement();	        
            ResultSet rs = null;
            PreparedStatement pstmt = null;
    
            String query = "SELECT rank0, rank1, rank2, rank3, rank4, rank5 FROM ranks WHERE deckForm = ?";
            pstmt = connection.prepareStatement(query); 
    
            for (int i = 0; i < 1000000; 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)};
    	        	//System.out.println(arr[0] + "," + arr[1] + "," + arr[2]);
    	        }
            }
            
            rs.close();
            pstmt.close();
            connection.close();

  4. #4
    doWhile is offline Moderator
    Join Date
    Jul 2010
    Location
    California
    Posts
    1,642
    Rep Power
    7

    Default Re: Java + SQL

    Does your database table have an index on the column 'deckform'?

    I know that's just a short snippet, and should your project code not do so, you should surround it with a try/catch/finally - closing the appropriate resources in the finally statement and handling caught exceptions accordingly.

  5. #5
    atomant is offline Member
    Join Date
    May 2010
    Posts
    45
    Rep Power
    0

    Default Re: Java + SQL

    The table has indexes PRIMARY, id, and id_2 on deckForm.

    My method throws SQLException, that should do right?

  6. #6
    Tolls is online now Moderator
    Join Date
    Apr 2009
    Posts
    11,824
    Rep Power
    19

    Default Re: Java + SQL

    That won't do.
    The result of that would be if an exception occurs in that code then your connection to the database would remain open, as would the cursor for that select.
    This would eventually result (depending on how your app actually works) in your db running out of resources.
    The above needs to be in a try/catch/finally block.
    Please do not ask for code as refusal often offends.

    ** This space for rent **

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •