Is it bad to have multiple backend mbd's?

[rico]

Hi all,

Over the last few months i have joined a few databases so that our users
only have to use one frontend, but the back end is still kept in about 3
mdb's on our server. Is this a bad idea? should i import all the tables to
one mdb? I ask because there have be some perfomance issues recently with
data taking too long to populate the users front end.

TIA

Rico

 

[Douglas J. Steele]

One of the major reasons for having everything in the same backend is that's
the only way you can have the Jet engine enforce Referential Integrity.

 

[rico]

Thanks Doug,

But i the database does not really use relations ships, most of the data
control, is done by code. Do you think tho the multiple mdbs might be causing
a slowdown.

Thanks

Rico

 

[Douglas J. Steele]

Yes, it's quite possible, especially if you're joining tables that are in
different backends.

 

[Immanuel Sibero]

Rico,

Do you use persistent connection(s) to the back end(s)?
About the only reason to use multiple back ends is when you're bumping
against the 2GB limit of mdb file size.


Immanuel Sibero

 

[Jerry Whittle]

I'd be much more inclined to think that the slowdown is because data control
is done by code and not the database! An application supported by a well
designed, normalized database with referential integrity enabled is always
faster and more efficient than something controlled by external code.

I can't count the number of times that developers have told me that their
code will do things like prevent duplicates only to have it fall apart. My
favorite is "Please remove the primary key as it's making the application not
work properly"!

 

[Patrice]

I would see things the other way round. What is the compelling reason for
using multiple DBs... AFAIK the default design is a code frontend to ease
code updates and a data backend.

--

 

[Guest]

No, there is a tiny amount of overhead in the file system caused
by having 4 files open instead of two, and there is the connection
delay unless you keep links open on both files, but the lock/read/write
process is identical with one file or with 1000.

Since you haven't defined relationships between the tables, you
just need to check that the join fields are indexed.

I've had multiple BE mdb's for years: there is the occasional
problem when someone deletes or modifies a related record,
but there never has been any speed problems.

(david)

 

[-Kevinmi]

There are small advantages have it all in one, but nothing you'll miss if you
have it working across ultiple files already.

Take a look at this article, especially the section regarding OPLOCKS.

http://support.microsoft.com/kb/300216