speed up my ado conn access an server dir

[luca90]

...
Set CNSQL = New ADODB.Connection
CNSQL.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\DB_PAS_DUE.mdb;"
....

i use this ADO conn to point on my local c:\ and all work very fast...
but if i copy the same mdb on a server dir, the operation to read and write in table of mdb are very very slow...
Peraph is a question of server, or cursor type not set, or ?????

[Hack]

What kind of traffic does the server get?

Is it already busy with other things when you are trying to work with your database?

[si_the_geek]

What other code are you using?

The chances are that the speed is due to bad methods (eg: getting all the data from the database and using VB to manipulate it, rather than just ask the DB for what you need), or bad parameters (eg: setting CursorLocation to client).

[dilettante]

I think Hack and si the geek have pretty much covered it.

You'll have the cost of network I/O as well as contention for a slow or busy file server to cope with here.

Both of these factors can be magnified by different ways of using ADO and Jet to perform the operations you require. If you let a lot of parameters default you get a scenario where huge amounts of data can be hauled back and forth over the network to do simple things. Your SQL can cost you when not written properly as well. For example SELECT COUNT(*) FROM <table> can be horribly expensive while SELECT COUNT(1) FROM <table> gives the same result and is much faster.

One of the attractions of SQL Server (Express, etc.) and other client/server DBMSs is that even if you use novice techniques most of the nastiness happens in the database server where memory can be cheap and data transfers fast. The end result is that you don't push as much over the wire and thus incur vastly reduced network-related application latency.

Even this won't help if your program always pulls whole tables from the database when it only needs a few records though.

There are some things you can do to get amazing performance improvements using Jet MDBs on a file server, but the specifics depend a great deal on what your application's needs are. Because of this it is almost impossible to give advice. What might be a good thing for one application might make matters much worse for others. All of them though involve the two factors of (1.) reducing lock overhead and unnecessary locking and (2.) reducing the amount of data moved over the network.

Beyond this there are ways to use Jet in a true client/server fashion via RDS, but at that point you might as well consider SQL Server Express or Standard (but not Compact - which behaves a lot like Jet).

Remember, most LANs are much slower than the path between your CPU and local hard drives. It is even worse if the LAN is busy.

[dilettante]

Forget my COUNT(1) example. It works fine but most DBMS engines treat COUNT(*) as a special optimized case now anyway.