Database Splitting - Performance Problems 2

[Steve101]

I have just acquired a database from another developer which is in need of some maintenance. Its a single mdb application, and for all of the 'right reasons', I have decided to split it.

The application mdb resides on a remote file server, and when not split, "form opening" performance is pretty good. When I attempted to split it, performance when initially opening a form was terrible (going from 1 second to about 40 seconds). On subsequent invokations of the form, response was much better. Ive never experienced performance degradation like this before on splitting databases, and am somewhat converned about its cause. When I compacted the client database however, performance on opening a form once again became very bad.

Best explanation that I can come up with is that some form of disk caching was occuring at the remote server which cached the client mdb on loading, but not the data mdb.

Anyone got any other theories on this. Its sort of got me stumped. Could it also be caused anything on the client workstation.

I had to regress back to the non split version of the application, which repeatably peformed much much better. Any suggestions would be most welcome,

TIA,




Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[BobJacksonNCI]

Check thread 702-206410 in case it applies.

Bob

 

[BobJacksonNCI]

thread702-206410 don't see how to copy/paste a thread hotlink.
So, please copy the above and paste it into your Address window to go to that thread.

(Can someone tell me how to copy/paste a
thread hotlink?)

 

[Steve101]

Thanks Bob, that has somewhat improved things, though it does'nt explain why the performance degrades so dramatically with the linked tables.

By the way (as you can see above), TekTips recognises and automatically hyperlinks thread references, providing you use the "Thread999-999999" syntax.

Cheers,


Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[BobJacksonNCI]

You're welcome, Steve.
This was the first time I copied/pasted a thread link, so I hadn't seen how that worked.
One of my seven books on Access/SQL/Client Server
disusses the differences between single MDB, split MDB, Access Projects (tables on an SQL server) and straight SQL.
Unfortunately, I don't remember which book. However, you're on track in suspecting the linked tables mechanism. Handshaking is required between the two servers, I believe for every data exchange. So, if either server is busy with other tasks, your database will respond more slowly.
Since there's no cure that doesn't involve a hardware upgrade, all you can do is tune the application.
You're probably more familiar with that than I.
My immediate thoughts would be to reduce bitmaps that might be included on forms and to examine the tables. If there's heavy use of one and it has a lot of columns, it could reduce network traffic to split it. For example, maybe a table has account number, name and address and a bunch of contact information fields (e-mail, phone numbers fax numbers, etc), and all of that is passed to a query that selects a few records. If most of the time all you need is a name, you could put all of the rest in another related table.

Good luck!
Bob

 

[Steve101]

Hi Bob,

Thanks for the suggestions. I've split more databases than I've had hot breakfasts, but never with such dramatic degradation in performance; hense the post on TT.

Unfortunately, its an existing application on a remote server over which I have no (a big zero!) control. I have no "licence" - or indeed inclination - to alter the design (which is less than ideal). It took a little persuasion on my part to convince the user / existing developer (who is quite possessive of his masterpiece) that it was a good idea to split the db. Problem is that the unsplit db performs noticably better than the split db, which goes contrary to my expectations and explanation; usual performance degradation is minimal and hardly noticable (if at all).

Anyway enough rambling on my part;

We well,




Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[BobJacksonNCI]

Thank you for the Star!

 

[CosmoKramer]

Steve,

You might want to check out this page:

http://www.granite.ab.ca/access/performancefaq.htm

There are a least a couple of FE/BE references there.....

Hoc nomen meum verum non est.

 

[Steve101]

thanks kosmo,

I'll have a read of that later in the day. It looks pretty interesting,

cheers,


Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[willir]

Steve101

I hope you are making progress on this, and I am glad you have plenty of experience splitting tables.

Let's go back to some basics, and forgive me if I ask any obivous question...

The backend with the database is located on the remote server.

- Does this mean the front end is located on the desktop, or another server? Although deploying upgrades is more awkward, placing the front end on the desktop is much faster.

- Do you have any other databases on this remote server? If not, and access to other remote servers is fine, then I would look at your remote network connection -- WAN (frame realy) DSL, etc. What is the ping time to the remote server vs ping time to other remote local servers? Are the similar?

I have a few databases spread across the country. Even with ping times of 50 ms (milliseconds), performance sucked.

As an alternate, would replication work?

Richard

 

[Steve101]

Richard,

The unsplit database was (obviously) located on the server, so my initial attempt was to split it into two databases hosted on the same computer (to allow immediate continued access). This is where the performance anomoly arose. The only difference was the splitting; not the network or the client location.

I'm well aware that a local workstation client would be preferable; the question was why the performance degradation just because of the splitting; other things being equal.

At any rate, thanks for your comments. Performance is acceptable as is, though not ideal. Replication is not an option. I think that they are considering improving the line, so the problem will probably go away soon.

Cheers,




Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)