Performance decline after splitting database
Susan
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
Kevin3NF
(Tools>>Options>>General)
--
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"Susan" <anon...@discussions.microsoft.com> wrote in message
news:031f01c3a3db$fb021990$a401...@phx.gbl...
Susan
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
coddi...@cobrohsv.com
Kevin3NF
file at the same time. When you load a form, is it slow at first, then
quick?
--
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"Susan" <anon...@discussions.microsoft.com> wrote in message
news:029101c3a3e3$93d947e0$a601...@phx.gbl...
Susan
comes up quickly enough, but the data takes a while to
load. During execution, we are reloading data.
Kevin3NF
(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
"Susan" <anon...@discussions.microsoft.com> wrote in message
news:057101c3a3e9$0a08eb50$a301...@phx.gbl...
Allen Browne
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.
--
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.
"Susan" <anon...@discussions.microsoft.com> wrote in message
news:031f01c3a3db$fb021990$a401...@phx.gbl...
Susan
Susan
Susan
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.
Kevin3NF
Everything else on the network runs quickly?
--
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"Susan" <anon...@discussions.microsoft.com> wrote in message
news:0bf901c3a47b$af3cdcc0$a601...@phx.gbl...
Felice
process is eating the time?
>.
>
Susan
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
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
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
--
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.
"Susan" <anon...@discussions.microsoft.com> wrote in message
news:016d01c3a48d$6f3e1730$a301...@phx.gbl...
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
Alan Fisher <anon...@discussions.microsoft.com> wrote in message
news:059801c3a4b4$fdd9be00$a101...@phx.gbl...
(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
>> 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().
Hmmm, now there's another idea.
TC
Tony Toews <tto...@telusplanet.net> wrote in message
news:j3srqv4ojvqrmk61v...@4ax.com...
> "TC" <a...@b.c.d> wrote:
>
> >> 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().
>
> 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.