mysql execution time from Java

Question

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?

This is a partial answer, adarshr is probably right that I should implement connection pooling, however I managed to shave off the 3.8 seconds for most of the database calls, I realised I used: private Connection con; when I should have used private static Connection con; Making this one change has saved the 3.8 seconds on all DB calls apart from the first (which is fine). Now I want to optimise the ~ 2 seconds for the select call. Any more suggestions for optimisations are still appreciated. — Steve
– Steve, Commented Apr 20, 2012 at 12:24
Using a static connection will give you problems if multiple queries are run in parallel — Esben Skov Pedersen
– Esben Skov Pedersen, Commented Apr 20, 2012 at 13:16
Like @EsbenSkovPedersen pointed out, using a static variable for such problems will only worsen your problems. Your best option is the industrial-strength solution of using Connection Pools. — adarshr
– adarshr, Commented Apr 20, 2012 at 13:41

Community · Accepted Answer · 2020-06-20 09:12:55Z

2

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.

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Apr 20, 2012 at 11:40

adarshr

62.8k24 gold badges146 silver badges175 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Steve Over a year ago

thanks adarshr, I will look into connection pools to further optimise. I have found part of the issue with the connection, I've entered an answer below, but +1 for your help.

James Black · Accepted Answer · 2012-04-20 11:42:42Z

0

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.

answered Apr 20, 2012 at 11:42

James Black

41.8k10 gold badges92 silver badges172 bronze badges

Collectives™ on Stack Overflow

mysql execution time from Java

2 Answers 2

EDIT

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

EDIT

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related