Remote server MySQL access via DAO... very slow!?

Hi guys,

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:)

 - Lazar
LVL 1
kerznerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DabasCommented:
Hi kerzner:
>  dealing with a MySQL database via DAO
Have you considered using ADO instead?

Dabas
dr_binksCommented:
I agree with  Dabas, using ADO would be a better idea, DAO is now outdated and not being developed anymore where as ADO has security patches and such.

using ADO:

'add the reference: Microsoft ActiveX Data Objects 2.x Libraray (2.8 would be the best version to use... if you dont have it download MDAC2.8 from microsoft.com

'global
Dim rs As ADODB.Recordset
Dim adoconn As ADODB.Connection

priavte sub form_load()

''this could also be done in sub main()

'datasource = IP of mySQL server
'catalogue = database name

Set adoconn = New ADODB.Connection

adoconn.ConnectionString = "User ID=" & username & ";password=" & password & ";Initial Catalog=" & catalogue & ";Provider={MySQL ODBC 3.51 Driver};Data Source=" & datasource & ";"
adoconn.Open

end sub

private sub .......   'any sub where you want to use the database

Set rs = New ADODB.Recordset

rs.open SQLStatment, Conn

end sub

you may also want to look into Microsoft Transaction Sevices (MTS).


hope this helps

~Binks
kerznerAuthor Commented:
Not actually. Thanks anyway.
I know how to use ADO, but I guess it will still be quite slow.
I'm mostly looking for a direction on how a complex master-slave(s) db system with synchronization could be build in the best way, from somebody who designed such thing. Anyway I will try switching to ADO first, since I have a db-independent layer and this won't be a big trouble.

-Lazar
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

DabasCommented:
kerzner:
You are asking about "best solution" and "complex master-slave(s) db systems" and needing advice from somebody who has designed such a thing.

First of all, I suspect that ADO will be more efficient.
I note that you are using MySQL, and unfortunately I cannot help you much with for lack of experience.
On the other side, I am an IT Manager for a company that has such a complex system with a head office and three branches.
Luckily for me, we use SQL Server, and the replication utility does it all for me. Easy to set up and works like a dream.
Whenever a record gets updated at Head Office, it magically appears at the branch within a minute.
If the connection is down for some reason or other, synchronization just picks up.
Maybe it would be worth while spending the $$$$$?

Dabas
kerznerAuthor Commented:
Well, switching to MSSQL won't be a problem at all (usually I use MSSQL too); I just thought replication stuff is rather slow. Imagine a situation like this: we have in the store 5 cars, and in the master office somebody sells 3 cars (with invoice, etc), while in the same time at one of the branches somebody else sells another 3 cars (because quantities are updated 'within a minute', that would be possible; moreover, both invoices might get same numbers if they just search for max(number)+1 (well for numbers it's obvious how to avoid that, np). But what about quantities? How is this solved in your company?

Thanks,
Lazar
DabasCommented:
Lazar:
Most of the data we are replicating is not that crucial. It is mostly lookup tables, so I would not be able to talk out of experience.
As you say, the invoice issue can be solved.
As to the car quantities, probably a direct read and write to the main database would be something to consider.
We do have a stock count program where data is entered directly into the Head Office SQL server from a branch that is on the other side of the continent.

Dabas

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.