MySQL / SQLite Synchronization

[litchi]

Hi :)

I am writing an admin application for a website I've written. It's a basic database update/insert/delete app. I started by using the website's server, but the time it took to connect to the server (since the main JTable is populated from the db, you have to wait for the connection) was making the application *seem* extremely slow, so to rectify the problem, I copied the database into a local SQLite database.

That improved the perceived speed of the application immensely. Now I have to synchronize the local SQLite db with the online MySQL database. At the moment, every time a change is made, I start a separate update thread for the online db and the local db. Was just wondering if there is a better way to do it?

Also, in SQLite is it necessary (better practice or w/e) to reopen and close the connection each time you use it? I tend to keep the connection open, and pass it to the classes that need it, since the db is accessed quite often.

Thanks for any pointers :)

[masijade]

It probably didn't "seem" slow, it probably was slow. Properly handling DB connections and network traffic is a part of writing an application and simply circumventing those by building in a manual copy step to have the data local is a crutch and not a solution.

You should be opening the connection (or a pool of connections) on startup of the app and use it through out and there will be no problem (as long as you are using a decent fetch size).

[litchi]

Thank you,

I wrote the program as efficiently as I know how, using a connection pool vs one connection, prep statements vs normal queries, batch queries vs single queries and performing all tasks that need a lot of computing in separate threads.

The problem comes in at startup. I need to populate 4 JTables (Multi threaded, with connection pool) with data from the table, and before I show the GUI, I make sure everything is loaded. That takes 10-25 seconds, since my client is situated in SA and the server in the US of A. All the code is fast, the only thing that is slow, is the actual connecting, but after the conn pool is up, everything is super fast.

Which is why I said that it seemed slow.

Also what do you mean by decent fetch size? As in queries?

Thank you for the help so far

[masijade]

When you execute a select query the first call to next() will "fetch" a specific number of records into a "cache" and then use that cache until the next next() call goes beyond it and fetches the next "set" of rows. The slower the network connection the more it behooves to increase this fetch size (depending on available memory). Default wise it is, for most DBs, 500, which should be enough, but you can play with it. See the API docs (for either ResultSet or Statement or maybe, even, Connection, I forget). Also, I would simply display a "wait" message while the initialisation occurs and then show the GUI after everything has been initialised. There is not much you can do about the network. If you feel you must use a "copy" I would suggest a MySQL DB on the local side as well and set it up as a replication DB.

[litchi]

Ah ok, thanks, I'll look into that, sounds promising. The problem is, as I understand it, I need to install MySQL on the local machine as well to access the local copy of the database? That starts to increase the file size a little too much. The reason I settled for SQLite is because it's serverless.

I have just started googling it, but is there a way to make a standalone MySQL installer?

Thank you for all the help

[pkulkarni89]

Hi,litchi i also have the same problem could you send me your code
on [removed]