0

I have a very short Java application that just opens a connection to a remote MySQL database, reads some data, prints it, and exits. The most time-consuming part of the application is the database connection.

Currently I have only a single thread, and my only concern is to save the time of opening the connection.

I thought of several ways to make it faster, but it turned out they do not help:

  • Connection Pooling - doesn't help because the pool lives only only during a single run of the application. When the application is terminated, the pool is gone, and when I re-run the application, I have to re-open all the connections in the pool.
  • mysql-proxy - connects only to the local server: mysql-proxy for a remote MySQL server
  • TCP/IP server - I thought of holding a local TCP/IP server that will keep a persistent open connection and send it to a TCP/IP client on request. However, Connection objects cannot be serialized, so I have no way to pass the Connection object from client to server.

Any other option?

Community
  • 1
  • 1
Erel Segal-Halevi
  • 33,955
  • 36
  • 114
  • 183
  • 1
    What times are we talking about here? 10s of milliseconds? Seconds? Would it be possible to keep on "server" part of your program alive which has a persistent connection, prepared statements etc which your "short" java program communicates with? – Roger Lindsjö Nov 22 '11 at 09:16
  • How much time are we talking about? Connections takes a while but are we talking seconds or milliseconds? – Andreas Wederbrand Nov 22 '11 at 09:22
  • Connection to the remote DB takes about 10 seconds. The calculation itself takes less than 1 second. – Erel Segal-Halevi Nov 22 '11 at 10:04

4 Answers4

1

Generally connection to a DB is a most time-consuming operation. If the application is to be started and stopped then there is little that you can do.

Using connection-pooling in a web-server and call that by running your app which talks to the web server using JSON might be an option.

Brett Walker
  • 3,566
  • 1
  • 18
  • 36
  • I didn't understand the second option. Can you please elaborate? – Erel Segal-Halevi Nov 22 '11 at 10:06
  • Have a local web server that uses connection pooling. The web server would always be online waiting for a query. It would goto the database when asked. The Java app calls the web server for the data. This is not a database query per se. The web server responds with the data. The Java app consumes the data. This should have the best of two worlds: connection-pooling and faster response. But the downside is more infrastruture, ie. a web server. – Brett Walker Nov 22 '11 at 10:10
  • This could be a good option, however the calculations I do use some third-party code which return non-serializable classes, so I cannot send them back from the TCP/IP server. – Erel Segal-Halevi Nov 22 '11 at 10:36
  • Why send classes back and forth between the app and the server. I just was talking of transferring the data extracted from the db between the app and the server. – Brett Walker Nov 22 '11 at 10:41
  • I have a third-party library that: takes a DB connection as input, does some calculation, and returns a class as an output. I know it is a bad design, but this is what I have... – Erel Segal-Halevi Nov 22 '11 at 12:04
  • 1
    Not knowing what the 3rd party app is doing. Perhaps you could create a pseudo connection in app. It looks and behaves like a connection but all it does is forward the request to the server which actually the queries the server. This is getting fancy. A good probability to be error prone and buggy. Just a thought. It looks like you might be backed in between a rock and a hard place – Brett Walker Nov 22 '11 at 12:11
1

You said you have a very short application so your 3rd option might work if you put the database logic into you "option 3 TCP/IP server" and just forward the results to your connecting client. This is a typical application server pattern.

cssdata
  • 153
  • 2
  • 9
  • This could be a good option, however the calculations I do use some third-party code which return non-serializable classes, so I cannot send them back from the TCP/IP server. – Erel Segal-Halevi Nov 22 '11 at 10:08
1

Another thing you should consider about network look up https://stackoverflow.com/q/3641155/1055715 which Marc B has mentioned in his comment.

Community
  • 1
  • 1
Surasin Tancharoen
  • 5,520
  • 4
  • 32
  • 40
0

It turns out the best solution is to use mysql-proxy with a script that handles connection pooling (a combination of my first two options). I found one such script here: http://forge.mysql.com/tools/tool.php?id=151

It was probably written for an older version of mysql-proxy, so I had to fix it (if anyone need the fixed version - write me).

It works like a charm - I run the exact same application as before, the only change is in the connection string: instead of connecting to "qa-srv:3308" (the remote server) I connect to "127.0.0.1:4040" (the proxy server).

Erel Segal-Halevi
  • 33,955
  • 36
  • 114
  • 183