Delphi and Access

[cpmasesa]

Hello all,

When you create a database application with ms access as the 'backend' (getting data using TADOQuery) do the clients pull the entire database over the LAN or just the records specified by the sql?

If the clients pull the entire database across and execute the query locally, what can i do so that only what i want is pulled from the database server to the clients?

I am running an application at the office that has 15 - 20 people connecting to an access mdb placed on the server. Response time is sometimes slow and am looking for ways to improve.


TIA

Clemens

 

[dilettante]

Jet (the guts underlying "Access" database manipulation) is a little more clever about using the MDB file than simply slurping the whole thing over the wire. Depending on what you need to do and how well queries are formed though, you can end up with a large amount of network traffic to perform a small task.

Other overhead includes the creating and modification of the LDB lockfile on the file server.

If you are having serious performance problems with Jet and 15 to 20 users, there may or may not be easy solutions. One thing to look at is your queries. Inefficient or unnecessary operations can slow any application system down. Sometimes the design of the tables and various relationships among them can be the culprit. Another issue is holding locks on database pages too long, which in some cases can be ameliorated by changing the lock granularity from page locks to row locks where it makes sense.

Another thing to consider is changing your connection philosophy. Some developers open numerous connections on the same database resource within the same program! This almost never makes sense, and can cause performance problems. Often a single connection is used, but the programmer opens it when the program starts and keeps it open until the program is terminated - even if users just leave it sitting idle for hours!

The usual answer to performance concerns though is to just eliminate most of the network traffic. You can mask some pretty piggy logic with a fast machine as a server if you just avoid pumping so much data over the wire.

Some people will tell you to move to a client/server DBMS instead of using Jet. This has the advantage of letting you continue to write simple monolithic applications.

Another option is to separate the database actions (queries and updates) from most of the rest of your logic by splitting your program into a client and a server piece. The server piece is preferably written as some sort of multiuser program. The glue between the pieces can be DCOM, TCP sockets, HTTP, or almost anything.

The idea is to have an "application server" machine where both the MDB and the server piece of your application reside. A file server by itself just won't cut it. This lets you have even fairly sloppy database design and queries - yet achieve acceptable performance. Most of the network I/O is replaced by local disk I/O then instead.

It can be a lot of work though, especially trying to retrofit this approach into an existing application.

Things like MSDE (a free, cut-down version of SQL Server) are popular as alternatives to Jet. Many a hack programmer masks his ineptitude by moving to MSDE and a fast server. As I said earlier though, this does let one stick to fairly simple monolithic programming approaches - and productivity is productivity no matter how you achieve it.

But once again, you'll need a database server and not just a file server to go this route. There really isn't any "magic bullet.