Optimize the connection to the database in mysql

I am developing an application that at first I thought small and has been growing more and more. To the extent that I feel out of hand. One of my main problems is that the application is desktop and do not know if there is no way to optimize the connection to the database, in my case mysql, because some teams are constantly sending me information to the database and for this why should I be reading constantly from the applicative information. This is done exactly every 6 seconds, but to filter all events that are sent to the database using stored procedure, it takes up to 28 seconds to send the results. So far that's what has me more worried. I have tried many things to do but nothing is working in my way so far. So thought that someone from your side can prove helpful for me.!! The main thing I want to know is about optimization of the connection to the database in mysql. Please respond soon.

"A database allows you to organize certain types of information it easy to extract any information from them." To understand with an example we will work on a database that will store data from the list of movies from your collection, and, if properly organized in a database, it is very easy to obtain such a list alphabetical order to know what the director intended it, or as to a certain actor etc participated. Also I would like to tell you that if you are using any coding for connection (seems to be..) then provide that coding, so that it would be helpful to solve the issues (if any..).

The database server is mysql 5 and is mounted on a Centos server, the connection to the database do the form.

Code:

---

public static Connection getConnectionDB () (


if (conn == null)
connect ();

try (
if (conn! = null)
if (Conn. isClosed ()) (
conn = null;
System.out.println ("not connected");
)

) Catch ( SQLException e) (
System.out.println ("isClosed");
)

return conn;
)

public static Connection connect () (
try (
Class.forName (driver);
) Catch ( ClassNotFoundException e) (
System.out.println ("Driver");
)

try (
conn = DriverManager.getConnection (url + db, user, pass);
) Catch ( SQLException e) (
conn = null;
JOptionPane . showMessageDialog (null, "Unable to connect to server");
System.gc ();
)

return conn;

)

public boolean close () (
try (
conn.close ();
conn = null;
return true;
) Catch ( SQLException e) (
e.printStackTrace ();
return false;
)
)

---

At the outset I recommend you use a pool of database connections , not to make a connection every 6 seconds, but you've got reused in place (and still do not think desktop application you use more than 2 or 3). Instead of managing a direct Connection, you will have a DataSource which connections you ask where you need them, wear them and close (when you close, it really happens is returned to the pool, but stay open ready for use again.)

I suppose you use a java.util.Timer or (preferably) a java.util.concurrent.ScheduledExecutor to schedule the task you say you have to do every six seconds. If exactly every 6 seconds but the server is really busy because it takes 28 seconds to do it, because I think it would change entrance time upload a bit. But in case you can optimize something on the server, I think it is appropriate for the Runnable (or TimerTask) running the task, have a flag to know that you are doing and you should not perform again until the end, of Thus although the task is activated every six seconds, if you have not finished the previous execution is not done again, when the current run finished, resets the flag and in the next activation of the task and will take place again.

Thank you for responding, really had a misconception, I thought that was exclusive connection pool for Web applications, thinking it was necessary to use an application server like JBoss. As for the work program, if I am using a java.util.Timer to perform the task, the other class I guess I better mention it'll take care of study. I'm reading C3P0 to implement the pool, thank you and I will be commenting as I was. On the other hand I have a question about java.util.Properties, these files can only be read when running the application?, what happens is that my application I have a server that is responsible for receiving socket connections and is generally prone to lose the connection to the device, which generates the death of the thread. My question is if I can use the properties to store some values each time you connect a device that I can use some data that I sent the device before losing the connection, as a kind of session variables. Thanks for everything. :worthy:

There's no reason you can not save data to a file server. Properties but it is not the best approach ... how you identify? You must use the ID of the client, so that you can only save when the customer is identified. And what happens if a file exists with that? What is the purpose of saving? Surely you want to reload that data into the next connection?

What happens is that the devices are in charge of sending events, one at a time - and often an event is the beginning of a report and then comes an event that ends the report. The problem is that if the connection is lost and happens often because they are devices that use 3GSM. If this happens, the data was lost in the thread and if you get an event that terminates a data report that he had saved the previous event I've lost. Seeking solution to that problem with the properties. The devices are identified each time they send an event as this is associated with the identifier of each device.

Then you can use the device ID as the file name. Properties and thus identify, in the event of generating the report you create or overwrite the properties and report the event to complete the delete. That could also make a mark in the record status of the device in the database, having a table with the data you need to store and manage a record for each device. If the drive. Properties make sure you save them to a directory other than temporary or are not prominently visible from reaching a user outside your application on the server and say "what is this? Bah, it seems serve, I will erase".