1 big or several small dbs?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Tim - 02 Aug 2004 17:13 GMT

My db is split with the tables on the server.

As the data builds up, I'm wondering am I better having just one
back-end, or should I separate out some of the tables and create
several back-ends? It shouldn't make a difference to the front-end
because all the tables will be linked just the same.

I realise that I will probably lose some of the relationships and
won't be able to do cascade updates/deletes between different
backends, but I reckon I can minimise that problem to an acceptable
degree by careful selection of which tables to move. I'm thinking of
keeping the core stuff in one db, where the relationships can stay
intact, and move some of the peripheral tables out.

Is there a performance gain in doing this, or is it a recipe for
disaster? Eg. is it beneficial to keep the overall size of a back-end
down, or will it cause noticable slowdown (or worse) having 2 or more
dbs with linked tables in?

Reply to this Message

Douglas J. Steele - 02 Aug 2004 17:32 GMT

My recommendation would be a single back-end. It's unlikely you'll get any
performance gains, and, as you noted, you will lose RI. If your application
is that large that a single back-end is getting too big, you should probably
consider moving to another DBMS such as SQL Server.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> My db is split with the tables on the server.
>
[quoted text clipped - 14 lines]
> down, or will it cause noticable slowdown (or worse) having 2 or more
> dbs with linked tables in?

Reply to this Message

david epsom dot com dot au - 03 Aug 2004 01:27 GMT

> Is there a performance gain in doing this, or is it a recipe for
> disaster? Eg. is it beneficial to keep the overall size of a back-end

Neither. Splitting a BE database makes no noticeable difference at all.

Big databases can take longer to scan and compact (in general, av
programs should NOT scan mdb files): on the other hand with more
files you are more likely to notice open/close delays with creating
and deleting ldb files.

Neither of which is a compelling reason for splitting or combining
back-end files.

(david)

> My db is split with the tables on the server.
>
[quoted text clipped - 14 lines]
> down, or will it cause noticable slowdown (or worse) having 2 or more
> dbs with linked tables in?

Reply to this Message