I have a VB6 app and it is dealing with a MySQL database via DAO in a local network. It is ok since now, but they want another office to use the same database, being connected from remote network (e.g. through internet). Well, queries are small, but nevertheless speed is horrible. The strangest thing is that the network connection icon is showing transfer for a sec, then nothing at all for say 10 seconds, and then goes on again for a sec and the table is getting loaded. Like the other server is on the moon and we have some cosmic delay:)
Both servers have quite good internet connections and low ping to each other.
I'm not using static recordsets, just SELECTs, UPDATEs etc.
Anybody has any idea why does this happen!?
Also, if we decide to have two separate databases that would need to be synchronized say every hour (or on user request), I wonder what is the best solution for that. I guess many companies has 2+ offices with a shared database, and actually have a master database and many slave databases being synchronized somehow. One way I see is programatically - save initial database state, then on all database updates update your local copy, but keep track of changes somewhere, and on sync request upload everything to the master database in a way that guarantees no broken data (mainly correct IDs). This way seems rather complex, I guess is there any automatic way, or, general, what is the best solution.
I'm awaiting some brilliant suggestions:)