CODEWITHSUNDEEP
javamysqloptimization
Steve
Steve

Reputation: 350

mysql execution time from Java

I'm looking back at an application which I built and released some time ago. At the time there wasn't much time for optimisation, but I've got some time to optimise it a little now.

I have a java DAL class which loads an object with items from the database as follows:

sql = "SELECT COUNT(*) FROM projects";
count = execute(sql) // this returns the count

sql = "SELECT p.*, m.name AS clientName"
...
sql = sql + " FROM projects p"
sql = sql + " LEFT JOIN clients m ON p.clientID = m.id";

Project[] p = execute(sql, count); //this returns Project array

The problem I have is the count script takes over 4 seconds to run and the select script takes about 2 seconds. Although this is delivered to the presentation layer asynchronously, 6 seconds is still a very long time to wait (there are only about 300 records in the projects table).

I'm thinking that I can probably improve this by adding stored procedures and maybe adding additional filters to the scripts such as "where active=1". But I'm wondering if anyone can suggest a better solution overall?

Thanks

EDIT: I've been doing some further testing with System.nanoTime() and I've found the largest chunk of the 4 seconds (3.8s) from the count script happens inside the execute function in one line:

Connection c = getCon();

The getCon function does this:

if (con == null) {
try {
Class.forName("com.mysql.jdbc.Driver").newInstance();
con = DriverManager.getConnection(db_connect_string,db_userid,db_password);
}
...

So I have a new question now - why would it take 3.8 seconds to get the db connection?

Upvotes: 1

Views: 972

Answers (2)

adarshr
adarshr

Reputation: 62583

Try using SELECT COUNT(1) FROM projects instead of COUNT(*). It has been known to improve some performance.

EDIT

With regards to your edit, looks like you're not making use of Connection Pooling mechanism. If you don't use one, each call will try and open up a new connection which can be expensive. A Connection Pool, on the other hand, maintains a pool of connections, ready for use whenever needed. If you do a connection.close() in your code, it will simply go to the pool for reuse rather than completely disconnected.

You might want to check a list of commonly used connection pools. I prefer DBCP or C3P0.

Note: If you use an application server such as WebSphere / WebLogic / JBoss, you don't need to worry about this as it would've already been taken care of for you. All you've to do is to make your application use DataSources instead of direct DriverManager.getConnection() and configure the connections on your application server.

Upvotes: 2

James Black
James Black

Reputation: 41858

Make certain you have indexes on your table, but before changing any code I would write some automated tests to get numbers.

Then as you make changes you can compare the actual numbers and see what will work best for you.

That way, if you try a stored procedure, you can see how that works, for example.

Upvotes: 0

Related Questions