1

I have a Java application that parses files and stores the data I need in a SQLite database.

I'm trying to have it store the data in a remote MySQL database that is on my hosts' server instead.

I rewrote the queries that had a different syntax, but I still have a problem: it's going way slower than when I do it locally using a SQLite database. Is it something I have to live with since one is local while the other is a remote connection, or is there something I should know before continuing?

As an example, parsing 636 files and storing all the data I need (some of the data is useless, I have to sort some of it, etc.) takes 2.5 minutes when I use the SQLite database. That's about 4.24 files per second. On average, it takes about 15 seconds per file when I'm using the MySQL database, so it would take about 63 times longer using the MySQL database.

What I think the main problem might be, but I don't know how to solve it: each file has, on average, 190 inserts. Since the files are statistics sheets for a bowling center and each game is a row (60 players per league times 3 games), plus some checks to see if the league exists in the leagues table, schedule, etc., I can't really just make less inserts since all the information is important.

I think it would help a lot if I could group all the inserts for the games part in one big insert, but I have no idea if that's possible / how it's possible / if it's even worth it or if the problem is elsewhere.

I'd like to know if I'm at least on the right track or if I should get used to my application taking that much time to store the data since it is remotely connected.

Jumbala
  • 4,764
  • 9
  • 45
  • 65
  • Yes, you can have batch inserts like `INSERT INTO mytable (col1, col2) VALUES (1, 2), (3, 4), (5, 6)`. Regarding compression, read on this - http://stackoverflow.com/questions/2506460/when-should-i-use-mysql-compressed-protocol – Roman Newaza Mar 05 '12 at 07:21
  • @RomanNewaza The problem with this is that I'm going to have a line with 180 sets of values on the same line, which I guess is not such a big problem, but there's also the fact that there's a ON DUPLICATE KEY UPDATE clause in case a file is reuploaded with new results if there was an error in the previous one, I feel that a batch insert would make it hard to manage. I guess it's worth trying, thanks! – Jumbala Mar 05 '12 at 07:28
  • BTW, you can try to set compression flag via your DB connector. – Roman Newaza Mar 05 '12 at 07:30

1 Answers1

1

Since no one replied with an answer and that I found something I could do to improve performance, I'm going to post it here so people can use this in the future if, like me, they didn't know about batches.

I was using PreparedStatements (but regular statements would have worked the same), and instead of doing the usual ps.executeUpdate(); to execute my queries, I used ps.addBatch(); between different queries and then ps.executeBatch(); when I was done building the queries and wanted to make one big INSERT with everything in the batch.

Note that when doing this, if your connection is named conn, you should do conn.setAutoCommit(false); before starting the batch and conn.commit(); after ps.executeBatch();. You can then set autoCommit back to true after this if you'd like.

Jumbala
  • 4,764
  • 9
  • 45
  • 65