1 big or several small dbs?

  • Thread starter Thread starter Tim
  • Start date Start date
T

Tim

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?
 
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.
 
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
Click to expand...

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)
 
Back
Top