Split Database Poor Performance

Daveo

Hi there,

Since splitting my database, one form in particular takes about 10
times as long to load and refresh, compared to the unsplit version on
the server.

The code behind it contains 36 SQL statements which populate individual
text boxes on the form - could that be why?

Thanks - David

Nov 13 '05 #1

pietlinden

In a word, YES. Networks are *significantly* slower than your hard
drive. Your question is almost like saying "It takes a lot longer for
me to drive through rush hour traffic from my house to work than it
used to take me to walk downstairs to my home office." Well, yeah,
especially since you don't need to get into rush hour traffic
congestion.

That's why people usually dump all that static tables into the front
end of the database and update them en masse as necessary. You might
want to read Access Developer Handbook volume 2 and maybe Chipman and
Baron's book on using Access as a front end to SQL server...

Nov 13 '05 #2

Daveo

Thanks for the reading advice.

I appreciate the difference between my hard drive and the network but
my unsplit database is on the server too in the same location as the
split backend. When I run the unsplit one, am I loading the whole thing
into my client PCs memory?

I thought splitting was supposed to improve performance?

Thanks - David

Nov 13 '05 #3

Allen Browne

A split database will be somewhat slower, but a factor of 10 indicates that
other issues are playing a part here too.

Work your way through the items that Tony Toews lists in the Access
Performance FAQ at:
http://www.granite.ab.ca/access/performancefaq.htm
Several of them such as Name AutoCorrect and Subdatasheets make a major
difference.

Regarding this specific form, there may be other things you can do. You
cannot use a SQL statment directly in the Control Source of a text box, so
I'm not sure where/how these 36 statments are used, but if they are all
coming from one table, you might use unbound text boxes, and assign values
to them by opening a recordset in Form_Current so it all happens in one trip
instead of 36 different trips.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Daveo" <wr**********@gmail.com> wrote in message
news:11**********************@g47g2000cwa.googlegr oups.com...

Since splitting my database, one form in particular takes about 10
times as long to load and refresh, compared to the unsplit version on
the server.

The code behind it contains 36 SQL statements which populate individual
text boxes on the form - could that be why?

Thanks - David

Nov 13 '05 #4

Allen Browne

Splitting does not improve performance. It improves updatability, and
prevents corruption where multiple users have the database open at once.

FYI, changing the back end to SQL Server tables will typically make
performance of most Access databases worse too.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Daveo" <wr**********@gmail.com> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com...

Thanks for the reading advice.

I appreciate the difference between my hard drive and the network but
my unsplit database is on the server too in the same location as the
split backend. When I run the unsplit one, am I loading the whole thing
into my client PCs memory?

I thought splitting was supposed to improve performance?

Thanks - David

Nov 13 '05 #5

Nick Coe \(UK\)

Have a look at these pages and see if they help:

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

http://www.granite.ab.ca/access/perf...ldblocking.htm

http://www.granite.ab.ca/access/spli...erformance.htm


--
Nick Coe (UK)
Available - Will work for money :-)
http://www.alphacos.co.uk/ AccHelp + pAnimal
http://www.pjandcoe.co.uk/ Online Store

In
news:11**********************@g14g2000cwa.googlegr oups.com,
Daveo typed:

Thanks for the reading advice.

I appreciate the difference between my hard drive and the
network but my unsplit database is on the server too in
the
same location as the split backend. When I run the unsplit
one, am I loading the whole thing into my client PCs
memory?

I thought splitting was supposed to improve performance?

Thanks - David

Nov 13 '05 #6

Salad

Allen Browne wrote:

Splitting does not improve performance. It improves updatability, and
prevents corruption where multiple users have the database open at once.

Allen, how does it imporve updatability? I can see if an app is in a
static state where few if any updates are made to it. But if the app is
constantly being updated with new forms/reports/other features then
distributing the new updated mdb would appear to be more of a pain.

FYI, changing the back end to SQL Server tables will typically make
performance of most Access databases worse too.

Nov 13 '05 #7

Rick Brandt

Salad wrote:

Allen Browne wrote:

Splitting does not improve performance. It improves updatability, and
prevents corruption where multiple users have the database open at
once.

Allen, how does it imporve updatability? I can see if an app is in a
static state where few if any updates are made to it. But if the app
is constantly being updated with new forms/reports/other features then
distributing the new updated mdb would appear to be more of a pain.

Because you are (usually) only updating the front end you can do this
without kicking all users out of the app, you can work on your copy of the
front end at your leisure and you don't even need to update all users at the
same time.

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

Nov 13 '05 #8

Allen Browne

Yes, that and also the chance to update the front end without overwriting
the data.

If you don't split, the user must stop work, give you a copy of their
database including all data, and then do no more work until you have updated
it and released their update back to them.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Rick Brandt" <ri*********@hotmail.com> wrote in message
news:nw***************@newssvr24.news.prodigy.net. ..

Salad wrote:

Allen Browne wrote:

Splitting does not improve performance. It improves updatability, and
prevents corruption where multiple users have the database open at
once.

Allen, how does it imporve updatability? I can see if an app is in a
static state where few if any updates are made to it. But if the app
is constantly being updated with new forms/reports/other features then
distributing the new updated mdb would appear to be more of a pain.

Because you are (usually) only updating the front end you can do this
without kicking all users out of the app, you can work on your copy of the
front end at your leisure and you don't even need to update all users at
the
same time.

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

Nov 13 '05 #9

David W. Fenton

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in
news:42***********************@per-qv1-newsreader-01.iinet.net.au:

Splitting does not improve performance. It improves updatability,
and prevents corruption where multiple users have the database
open at once.
Splitting *soes* improve performance in one regard, and that's the
initial load of Access objects (as opposed to data tables). Since
your split database has everything on the client PC, none of the
forms/reports/etc. are being dragged across the network, which
should make them open more quickly.

The only thing that should be dragged across the wire is the part of
your application that *must* be shared, the data tables.
FYI, changing the back end to SQL Server tables will typically
make performance of most Access databases worse too.

Hogwash. It all depends on how your forms are designed. If you're
using a typical bind-the-whole-table-to-the-form approach (as most
novices will), then you're unlikely to see much in the way of
performance improvement switching the back end to clidnt/server.

It's only if you've designed your front end to be efficient in the
first place that you get those benefits. The great thing about such
designs is that they make plain old Jet applications faster, too,
which makes it less painful to upsize to SQL Server.

Anyone blindly expecting switching to SQL Server to be a magic
bullet solving absolutely all performance problems is going to be
severely disappointed. If it were really that simple, there wouldn't
be much point in using Jet at all!

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

Nov 13 '05 #10

Salad

David W. Fenton wrote:

Hogwash. It all depends on how your forms are designed. If you're
using a typical bind-the-whole-table-to-the-form approach (as most
novices will), then you're unlikely to see much in the way of
performance improvement switching the back end to clidnt/server.

Could you explain the above statements?

What do you mean "bind-the-whole-table-to-the-form approach"?

Are you saying bound forms are used only my novices?

How could one do continuous forms or datasheets?

Should one not use navigation buttons on forms?

Unbound forms add much more work to designing the form. There is no
form BeforeUpdate event, no AfterUpdate event.

Nov 13 '05 #11

David W. Fenton

Salad <oi*@vinegar.com> wrote in
news:Uk***************@newsread3.news.pas.earthlin k.net:

David W. Fenton wrote:

Hogwash. It all depends on how your forms are designed. If you're
using a typical bind-the-whole-table-to-the-form approach (as
most novices will), then you're unlikely to see much in the way
of performance improvement switching the back end to
clidnt/server.
Could you explain the above statements?

What do you mean "bind-the-whole-table-to-the-form approach"?

Table as data source, instead of SQL string for a filtered
recordset.
Are you saying bound forms are used only my novices?
No, but the usual way to do Access, if you're following the sample
databases and Northwind and most tutorials, is to load the whole
table behind the form and navigate the whole table.
How could one do continuous forms or datasheets?
Different issue. A subform is, by definition, filtered, since it's
used for displaying child records (of course, it doesn't *have* to
be, but the most common use for it is for displaying child records).
Should one not use navigation buttons on forms?
Well, it depends on how many records you're loading.

I believe the best forms ware what I call "semi-bound" that use a
recordsource, and are thus bound forms, but in which the
recordsource is changed according to what data set is requested.
Most of my main forms open with an empty recordset (actually a
single blank record, based on a TOP 1 query on the smallest table in
the application; I've explained how to do that many times so I won't
repeat it), and provide a FIND function that sets the recordsource
to an appropriately filtered result. This could be one record or 100
(my FIND uses LIKE, so you can do partial matches, as well as type
wildcards, if you know how to use them).
Unbound forms add much more work to designing the form. There is
no form BeforeUpdate event, no AfterUpdate event.

I said absolutely nothing about unbound forms. I hardly use them,
myself, but my applications would upsize very well to client/server,
because I'm only loading limited recordsets, and never full tables.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

Nov 13 '05 #12