Re: Archiving old data to improve performance

Thank you so much for your valuable input.
Yes, we do use LAN.
As far as the forms, all of my forms display one record at a time,
except for the search form which may display multiple results. It's
not so much that the performance is bad for us (by us I mean a group
of roughly 20 users in our US office). The database works fine for us
and we can live with it (except for some complex quires which may take
a several minutes to run), but it is mostly a problem to our
colleagues in Europe who are trying to use the same database and are
experiencing real delays. We timed the performance of the db on their
side. We found that some forms which open in less than one SECOND
(virtually instantaneous) for us would take about a MINUTE for them to
open. The search for instance, may take us 3 seconds to complete, and
40 seconds for them. That is really bad, and I don't blame them that
they don't want to use the database if they have too put up with such
sluggish performance. And my boss demands that the database works
better, but I don't know what else to do to make it run faster for
them. They are using the same network as we do (not VPN, which I do
sometimes from home), so I assume there should not be so much delay.
Any other ideas would be greatly appreciated.


On Jan 26, 4:43 pm, "Albert D. Kallal" <PleaseNOOOsPAMmkal...@xxxxxxx>
wrote:
If you have a table on sql server, and there is 10 reocrds.

If you load up a form with a 'where' clause to open up that one reocrd, then
lets now assume that the table has 10 million roecrds.

If you load up that form to one reocrd...the performance will BE THE SAVE AS
IF YOU HAD ONLY 10 reocrds.

So, the #1 critial concpet here is to reduce the number of reocfds a form
laods to.

You also mentoend that your tables aer small...only in the 100,000 rocfrd
range.

Even without sql server, a talbe of 150,000 reocord is VERY small.

You mentioned a seach form. that seach form should be near instanclry in
terms of perahpse.
As along as you avoicd using findfirst in yoru reocrdsets, then 100, or 1
millon recrds should
perform IDETNICAL...adn be instanct.

Here is some ideas for a serach screen:

A few things:

having a table with 100k records is quite small. Lets assume you have 12
users. With a just a 100% file base system (jet), then the performance of
that system should really have screamed.

I have some applications out there with 50, or 60 HIGHLY related tables.
With 5 to 10 users on a network, response time is instant. I don't think any
form load takes more then one second. Many of those 60+ tables are highly
relational..and in the 50 to 175k records range.

I mean, when using a JET file share, you grab a invoice from the 75k record
table..only the one record is transferred down the network with a file share
(and, sql server will also only transfer one record).

Here is a few tips:

** What kind of network are you using. If you are trying to use a WAN in
place of a LAN..then you REALLY need to read the following:

http://www.members.shaw.ca/AlbertKallal//Wan/Wans.html

Here is a few more tips:

** Ask the user what they need before you load a form!

The above is so simple, but so often I see the above concept ignored.
For example, when you walk up to a instant teller machine, does it
download every account number and THEN ASK YOU what you want to do? In
access, it is downright silly to open up form attached to a table WITHOUT
FIRST asking the user what they want! So, if it is a customer invoice, get
the invoice number, and then load up the form with the ONE record (how can
one record be slow!). When done editing the record...the form is closed, and
you are back to the prompt ready to do battle with the next customer. You
can read up on how this "flow" of a good user interface works here (and this
applies to both JET, or sql server appcltions):

http://www.attcanada.net/~kallal.msn/Search/index.html

I actually asked a 90 year old grandmother if it was dumb to download
everyone's account into a teller machine, and THEN start searching. If
a 90 old grandmother can figure out this concept, then as developer
this should be crystal clear.

the same goes for reocrdsets. Don't load up a huge reocrdset, and
then seach, or filter. Simply load up the reocrdset with ONLY the
reocrds you need.

My only point here is restrict the form to only the ONE record the user
needs. Of course, sub-forms, and details records don't apply to this rule,
but I am always dismayed how often a developer builds a nice form, attaches
it to a large table, and then opens it..and the throws this form attached to
some huge table..and then tells the users to go have at and have fun. Don't
we have any kind of concern for those poor users? Often, the user will not
even know how to search for something ! (so, prompt, and asking the user
also makes a HUGE leap forward in usability. And, the big bonus is reduced
network traffic too!...Gosh...better and faster, and less network
traffic....what more do we want!).

** You can continue to use bound forms..but as mentioned..restrict the form
to the one record you need. You can safely open up to a single invoice, and
even continue to use the "where" clause of the openform. Bound forms are way
less work then un-bound forms...and performance is generally just is good
anyway when done right.

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKal...@xxxxxxx

.

Relevant Pages

  • Re: Archiving old data to improve performance
    ... If you have a table on sql server, and there is 10 reocrds. ... If you load up a form with a 'where' clause to open up that one reocrd, ... Even without sql server, a talbe of 150,000 reocord is VERY small. ... and then opens it..and the throws this form attached to ...
    (microsoft.public.access.modulesdaovba)
  • Re: Speed Issues
    ... It is also not clear how good of a network you have. ... form load takes more then one second. ... I mean, when using a JET file share, you grab a invoice from the 75k record ... and then opens it..and the throws this form attached to ...
    (microsoft.public.access.forms)
  • Re: Message Alert
    ... Dim strSQL As String ... This will trigger the Load event. ... check a few things in your VBA editor. ... assembled strSQL by pressing Ctrl + G, which opens the Immediate window, ...
    (microsoft.public.access.modulesdaovba)
  • RE: Form Load
    ... Below is a delay function: ... If you use the form "frmJobInfo" often then I would load the form as Hidden ... the code that "Opens" the forms to simple make them visible. ...
    (microsoft.public.access.formscoding)
  • Re: workgroup is not accessable; the network is not present or not
    ... The workstation service terminated with service specific error 2250 ... Could not load RDR device driver ... not see any of the other machines on the network. ... > Have you used Event Viewer to see what events or error ...
    (microsoft.public.win2000.networking)