Performance decline after splitting database

[Susan]

We have split a database using the database splitter
tool. The resultant database is extremely slow. That
is, queries are taking an abnormally long time to
execute. At first, we were using the network version of
the front end database. However, we also used a local
copy of the front end with the same results.

Ideas?

Thanks!
Susan

 

[Susan]

We are using Access 2002 on XP ... even though I keep
seeing Access 2000 file format (does this make a
difference?).

I turned off the Name Auto-correct, but the results were
the same.

Anything else we can try?

Thanks!
Susan
(e-mail address removed)

 

[Susan]

It is slow at first _and_ during execution. The form
comes up quickly enough, but the data takes a while to
load. During execution, we are reloading data.

 

[Allen Browne]

Tony Toews has a bunch of suggestions on his "Performance" page at:
http://www.granite.ab.ca/accsmstr.htm

It covers a range of issues including Name AutoCorrect (as Kevin3NF
mentioned), the caching of the table links, the setting of table's
SubDatasheets, etc.

 

[Susan]

Thanks Allen ... I'll check it out!

Susan

 

[Susan]

Actually, it's a very small amount of data that I'm
showing ... on the order of 20 records. The front end db
is about 900K and the back end is 2.5M.

It is so slow that you can see the value of each field
come up separately ... maybe a few seconds total, but
definitely not like the "big bang" fill we were getting
before the split. And then each time we update, we get
the same slow filling of the form.

 

[Susan]

Ok, I think we've solved it ... so I wanted to make sure
that I let you all know what we found.

In the filling of the form, we were using a function
(DCOUNT) which worked just find when the database was
one. When we split it, the performance went way down.
We replaced the DCOUNT call with an equivalent SQL
statement that counted the records as desired. And wa-
la ... the form is filling fast again.

So I guess the lesson learned is that for performance
reasons we should use functions only as a last resort.

Thanks for all of your help and interest!
Susan

 

[Alan Fisher]

There are a couple of other things that you should do
besides what you already did that will help a lot. First
go to every table in design mode and look at the
properties and set subdatasheet to none. Second, create a
table ( I called mine "Keep Open" with only one field and
no data. Then create a form bound to that table and have
the form open (using the On Activate property) of the
first form that opens when the database opens. Make it
invisible. This will keep the backend open for the users
while they are using it. Make sure you close it on your
exit database event. I did these things along with turning
off the autatrack and it helped a lot.

 

[Allen Browne]

Susan, thank you for posting your results. Appreciated.

The domain aggregrate function such as DSum(), DCount(), DLookup() are slow.
It's interesting that the difference was the significant factor in your
case, and that the difference was more significant after splitting.

Tony, if you are reading this, you might want to consider adding this to
your page at:
http://www.granite.ab.ca/access/performancefaq.htm

 

[Tony Toews]

Ok, I think we've solved it ... so I wanted to make sure
that I let you all know what we found.

Much appreciated. Thanks for posting your solution.

I received Allen's email, thanks Allen, and have updated my Access
Performance FAQ page accordingly. A link to this thread and a thanks
to Susan has been added.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[TC]

(snip)

Second, create a table ( I called mine "Keep Open" with only one field and

no data.

FYI, you don't need the table. Just open a reference to the BE database
using OpenDatabase().

HTH,
TC

 

[Tony Toews]

no data.

FYI, you don't need the table. Just open a reference to the BE database
using OpenDatabase().

Hmmm, now there's another idea.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[TC]

Hmmm, now there's another idea.

It works fine - as long as the reference variable stays in scope for the
whole run

TC

 

[Tony Toews]

It works fine - as long as the reference variable stays in scope for the
whole run

Oh yeah, I'd forgotten that one. That's why I prefer a, usually
hidden, form open at all times.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm