Access DB running slow in small server environment

[Jerry]

We have a simple warehouse tracking database which is split into front and
back ends. It does not do many transactions, except calculate daily
inventory, etc. We have a daily inventory report that calculates by going
through all the transactions since day one for the customer selected and
gives a report with results from a query written to do the calculations.
Naturally, as the number of transactions in the file gets larger, the report
takes longer to run. But it is taking inordinately long, and on some of the
slower office computers now can take 8 minutes. On my duo-core 6 month old
Dell it takes 3 minutes (for the customer with the largest number of
transactions). Can I "rejoin" the database and will this make it faster?
How do i do this? Is there another way to do this report?
Thanks
Jerry Bennett
Massachusetts

 

[Tom van Stiphout]

On Tue, 27 Nov 2007 05:28:00 -0800, Jerry

If with "rejoin" you mean using the Linked Table Manager, no that
won't have any effect.
Typically when a report is slow, it is because of bad query design
(incorrect joins) and/or bad database design (e.g. missing indexes).
You may need to have an expert take a look at that. Perhaps this page
will help as well:
http://www.granite.ab.ca/access/performancefaq.htm

-Tom.

 

[Jerry]

Thanks Tom; I checked out the link you included and copied everything; simply
correcting the indexing problem helped a lot; we are having our network
people come in and to through the stuff in the articles relating to the
network setup. Thanks a lot
Jerry Bennett

 

[Jeff Boyce]

In addition to Tom's suggestions, check the network. We suffered a major
slowdown in the response time of a front-end/back-end database until we
discovered that the network gurus had added a network-level anti-virus.
When they excluded the Access applications, the apps returned to normal
performance. I believe Tony mentions this at the site Tom referred you to.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[Jerry]

Thanks Jeff, I just called our server people and they are going to do exactly
as you said. By the way, do you know what the "Jet" program does and how to
determine which version i have (if i have it at all) and does it reside on
each computer or just on the server?
Thanks
Jerry Bennett

 

[Jeff Boyce]

"JET" is the database engine that comes with Access. I haven't had to
determine version, but I bet a quick on-line search would show how to find
it.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Tony Toews [MVP]]

Thanks Jeff, I just called our server people and they are going to do exactly
as you said. By the way, do you know what the "Jet" program does and how to
determine which version i have (if i have it at all) and does it reside on
each computer or just on the server?

Jet comes with the OS on Windows XP and is updated by
Windows/Microsoft Update. So that's not a big deal these days.

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
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Tony Toews [MVP]]

We have a simple warehouse tracking database which is split into front and
back ends. It does not do many transactions, except calculate daily
inventory, etc. We have a daily inventory report that calculates by going
through all the transactions since day one for the customer selected and
gives a report with results from a query written to do the calculations.
Naturally, as the number of transactions in the file gets larger, the report
takes longer to run. But it is taking inordinately long, and on some of the
slower office computers now can take 8 minutes. On my duo-core 6 month old
Dell it takes 3 minutes (for the customer with the largest number of
transactions). Can I "rejoin" the database and will this make it faster?

I suspect much of your performance problem would be solved using the
bound form or recordset always open. That's first on the list of
suggestions at the web page that Jeff mentioned. Then do the next two
or three as well.

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
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[David W. Fenton]

Typically when a report is slow, it is because of bad query design
(incorrect joins) and/or bad database design (e.g. missing
indexes).

Another thing that I'm reminding myself about lately is trying to
avoid outer joins where possible. This means that, whenever
possible, foreign keys should be *required*. I recently encountered
significant slowdowns on this issue when upsizing from Jet to SQL
Server 2K, and the only cure was populating the foreign key (which
in reality should have been required all along, but had been left
with Null allowed in order to accomodate legacy data).

But outer joins in general are very high cost and should be avoided
when not absolutely necessary.

 

[David W. Fenton]

I just called our server people and they are going to do exactly
as you said. By the way, do you know what the "Jet" program does
and how to determine which version i have (if i have it at all)
and does it reside on each computer or just on the server?

As Tony says, Jet is installed on every copy of Windows, workstation
or server, starting with Windows 2000.

With an Access app connecting across a network to a Jet MDB stored
on a server, Jet is only running on the workstations. There is no
server-side process involved with an Access app that is editing Jet
data, any more than there is a server-side process when editing a
Word document stored on a server.

 

[David W. Fenton]

I suspect much of your performance problem would be solved using
the bound form or recordset always open. That's first on the
list of suggestions at the web page that Jeff mentioned. Then do
the next two or three as well.

You know, I never do this, and never really see the problem. It
might be that I keep some bound forms open in the background in most
apps, but I don't do that nearly as much as I used to. It can't be
my cached reference to CurrentDB() as that involves only the front
end. Perhaps I should experiment with adding a cached reference to
the back end MDB. Of course, all my bigger projects are now moving
in the direction of SQL Server, so this is going to be less and less
of an issue for my larger apps.