What runs faster?

[PearlGI]

I'm about to start work on a BIG multi-user database (big in terms of records, not complexity).

I'm considering two potential ways of setting this up (both constructed as BE / FE). Either putting all the data on one BE .mdb or splitting the data into 2-3 BE's.

The data involved can be easily split and individual users would only be interested in the data from just one of the BE's at any one time, if I went down the multiple BE route.

Can anyone enlighten me as to which would run faster - single BE or multiple BE's?

 

[wh00t]

well, I have never tried multi be, but I have just put a db live that stores a lot of records and is running ok (single be)

 

[RichMorrison]

As far as Access is concerned, there is no difference between one back end versus multiple back ends.

Depending on your local network, you might experience better response with multiple BEs on different servers.

RichM

 

[The_Doc_Man]

If you split back ends across different servers, the one thing where you will have performance problems galore is any join that crosses a server.

Or any Union query that crosses a server and has to do a sort on a field that doesn't depend on the server.

Also, the locking involved gets uglier. On a single BE file, you've got the file lock on that file plus whatever is in the .LDB file that Access manages. But when multiple BE files are open, you have multiple file locks AND multiple .LDB files to manage.

I vote for the single BE case. You might get fewer lockouts due to interference from other users, but the down side is that when this method goes bad, it goes REALLY bad.

 

[RichMorrison]

Doc Man's comment is true, but......

As the original poster stated
<<
The data involved can be easily split and individual users would only be interested in the data from just one of the BE's at any one time, if I went down the multiple BE route.
>>

RichM

 

[ABlair]

I agree with RichMorrison. AS they teach you at SQLSERVER2000 classes - THat multiple BE will be efficient.

But the DOC pointed out that locks with access may become a problem.

 

[The_Doc_Man]

As I stated earlier, if all that occurs is queries of one BE at a time, there should be no big problem.

I was just pointing out that having multiple BE's invites trouble in a specific situation.

Pearl says it is likely that the users will only be in a single BE at a time. But is that true of the administrator or the supervisor who wants roll-up reports?

 

[RichMorrison]

It sounds like there are 2 topics here.

First seems to be one network server versus two or more.

I see 2 significant cases. First case is all back end data on exactly ONE network server. Second case is all back end data on two or more network servers.

I believe the 2 cases are the same. Access does not know or care if several back end databases reside on one server or on 2+ servers.

The second seems to be whether it is better to put all back end tables in exactly ONE mdb. I have no opinion on the performance or security implications of this approach.

Practical experience has taught me to use multiple back end MDBs because the data in one MDB can have different users with different security requirements, different backup frequencies, different growth rates, etc.

RichM