tags:

views:

945

answers:

2

Hi guys,

I have a problem with a really slow connection between my Java code and a MySQL Database. I don't know where the bottle neck is.

My program is more or less a chatbot. The user types something in, my program splits the sentence into words and sends it word per word to the database. If it finds something there, the user gets an output. The database is on an external Server, but I also tried to connect to a pc next to me. Both is slow.

I tried the connection once at another place then where I normally work and there it was fast, most of the time.

My SQL Code:

SELECT info.INFORMATION FROM INFORMATION info, INFO_SCHLUESSEL sch
WHERE LCASE(sch.SCHLUESSELWORT) LIKE '" + input + "%' AND info.ID_INFO = sch.ID_INFO
Order BY info.PRIORITAET DESC LIMIT 1;

(just remembered, if it helps to understand the sql code:
schluessel = key
Schluesselwort = key word
prioritaet = priority)

My Java Database Code is more or less standard stuff:

String driver = "com.mysql.jdbc.Driver";
String dbase = "jdbc:mysql://bla";
String dbuser = "bla";
String dbpw = "bla";

Class.forName(driver);
Connection con = DriverManager.getConnection(dbase, dbuser, dbpw);
Statement stmt = con.createStatement();

ResultSet rs = stmt.executeQuery(query);
while (rs.next())
{
ergebnis = rs.getString("info.INFORMATION");
}

rs.close();
stmt.close();
con.close();

edit:

I have tried this DBCP for a while now, and I can't seem to get it to work. It seems to be as slow as the old connection. This is the example provided by the website that I use:

GenericObjectPool connectionPool = new GenericObjectPool(null);
ConnectionFactory connectionFactory = new DriverManagerConnectionFactory("jdbc:mysql://bla", "bla", "bla");
PoolableConnectionFactory poolableConnectionFactory = new PoolableConnectionFactory(connectionFactory,connectionPool,null,null,false,true);
PoolingDriver driver = new PoolingDriver();
driver.registerPool("example",connectionPool);
Connection conn = DriverManager.getConnection("jdbc:apache:commons:dbcp:example");

+3  A: 

I suspect that it's the connection setup that is causing the problem. It would be worth timing how long this takes:

Connection con = DriverManager.getConnection(dbase, dbuser, dbpw);

and if so, check out Apache Commons DBCP, which allows you to pool database connections.

Brian Agnew
yeah, it seems to be the problem... This alone takes about 2 seconds. I will read your link.
Excellent (that you've found the problem). The pool will set up and maintain connections for you, and hand them out on demand without the connection time overhead.
Brian Agnew
I tried it, but I can't seem to get it to work. Maybe you could look at the connection pool code I edited into my original post?
Are you building the pool in advance, and simply calling DriverManager.getConnection() when you require (ie. not setting up a whole pool for each iteration?)
Brian Agnew
I wrote a test class that does nothing beside what I posted above (connection pool) and telling me how long it took.I just see, that at my old code, I started a new connection for every word ... maybe if I change that, it's fast enough without this pooling stuff.
It still has to make the connection. The point is that the pooling then keeps that connection for reuse. So a single test won't reveal any speed increase. You have to reuse the connection to make it worthwhile.
Brian Agnew
I changed my old database code, so that it has three methods: one, that is only called once at the beginning of the chat and opens the connection. The second sends the querys and is called a lot. The third, that's called just once at the end of the chat, closes the connection. At the moment, it seems to be fast enough. I will try the pooling code, if I have speed problems again. Never knew that something like this existed. Thanks a lot!
A: 

Well I think this warrants a discussion on the design.There are a few things which you can do in order to improve the performance. Since you are not persisting anything here, its better to preload all the data in memory in some custom java object, a map, list or whatever and then do an in-memory lookup for the word and get the results. Another approach could be to use a batch statement so that you dont go ahead and create and release connections for each word. Oh and if using batch statements make sure you set the batch size to an appropriate number, preferably a prime number

Preloading all the data? That may be possible with a small database, but the bigger the database, the more the user has to load over the internet. It may be faster for searching, but until the computer has all the data it needs, it has taken longer than my old approach.
Well then basically the design warrants a requirement as to why are you storing word based data in a relational database. If you are doing it for the sake of persistence i think the solution does not make sense. A better approach would be to let it be file based and use some IR library for indexing and search.
Maybe it would be a better approch, but I was happy to somehow do what was asked from me. Never thougt that a file based system was also a possebility. Maybe next time ... It's just a prototype. It somehow has to work, it's no big deal if it isn't perfect.