Performance decline after splitting database

  • Thread starter Thread starter Susan
  • Start date Start date
S

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
 
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)
 
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.
 
If you are loading up some huge tables with an enormous number of records
(or a query that accesses these huge tables), I can see it taking forever to
load completely. How long does it take? How many records? What is the db
size?

--
Kevin Hill
President
3NF Consulting

www.3nf-inc.com/NewsGroups.htm
 
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.
 
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
 
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.
 
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
 
Susan said:
Ok, I think we've solved it ... so I wanted to make sure
that I let you all know what we found.
Click to expand...

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
 
(snip)
Second, create a table ( I called mine "Keep Open" with only one field and
Click to expand...
no data.

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

HTH,
TC
 
TC said:
no data.

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

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 said:
It works fine - as long as the reference variable stays in scope for the
whole run :-)
Click to expand...

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