[AccessD] Performance tips anyone?

Anita, you havent said how big your FE is in mb?

Also, does every user have a copy of the FE on their own PC, or are they
accessing a common FE also from a network?

In my experience this has a direct bearing on the load speed.

Some other things I have found along the way:

A) only select data based on deterministic fields. In other words if you
have a table with field1, field2, with field1="job" and field2="2222",
don't go building  a query that concatenates these fields then open a
form based on that query using (select * from queryx where
concatfield="job2222"). Any index on field1 and 2 of the table would be
ignored if the index were not compound (including both fields).
If you find yourself routinely selecting data from tables that don't
have exactly what your after (select * from tablex where field1="job"
and field2="2222" then you may incur a performance hit. Consider in this
case, a third field in tablex called "fullfield" with a value of
"job2222", indexed. Then select straight from the table. The fullfield
could be filled at data entry.

B) FE bloat, usually caused by long running queries, repair and compact
of the FE reduces the size in this case.

C) fill tabs on forms, only when the tab is selected. This way when the
form is opened, only the default tab needs to bet data on opening.

If any of this is off base, appologies in advance, but I have just been
through a similar exercise with another persons creation with quite
staggering results.

--Regards
Andrew


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Thursday, 5 July 2007 1:43 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Performance tips anyone?

Anita,

>You are very brave

It doesn't feel that way.  Access works very well for what it does.

>A form will open fast if designed well.

You are right there.  You can grind everything to a halt by trying to
use bound forms pulling tens of thousands of records.  I had to go to
the "single record" thing for this form, but it worked and worked well.

>You can keep throwing hardware at Access applications to will make them
perform faster. The real trick is to get your database to perform fast
regardless.

That is just a silly thing to say.  Try getting a modern real database
running on an old 100 mhz machine with 64 meg.  You have to face facts
and the facts are that the machine makes the difference.  A modern
machine can be had for peanuts from Dell.  If the business owner is
complaining that his Celeron 1ghz machines running 256 megs of ram,
windows 98 and Access 2000 is running slow.... My only response will
be... "and your point is?"

I use a framework which makes it very easy for me to do things like time
form openings and log what machine is doing the opening.  I showed my
client that his users complaining about slow databases were ALL on slow
machines.
And yep, he is buying new modern "workstations", a few a month, to get
rid of the old junk he was running with.  And the users have quit
complaining.

Sorry, facts is facts.  You can index till the cows come home but that
is never going to fly on ancient hardware.  Now, you can certainly move
the whole shooting match to a modern server running Windows server 2003
and SQl Server and pay through the nose for that machine, OS and SQL
Server, and then pay through the nose for SQL Server and OS notworking
experts.  That is certainly an option.  Or you can update your
workstations for less money and have good performance for many (MANY)
more than 10-12 users.  

And no, I am not arguing that an MDB is the solution for hundreds of
users but 10-12 users?  C'mon!

John W. Colby
Colby Consulting
www.ColbyConsulting.com
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Anita Smith
Sent: Thursday, July 05, 2007 12:57 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Performance tips anyone?

John,
My comments in line:

 Errrrr!  Wrong answer.

I have a database with 25 users in the database every day.  The BE is
currently about 800 mbytes.  This BE has tables with hundreds of
thousands of records in some tables, 30K-50K records in the main tables
(claimant / claim).  I open a VERY complex tabbed form with about 20
tabs on it, with subforms on each tab (JIT subforms).
***************************************
You are very brave
***************************************
I open a VERY complex tabbed form with about 20 tabs on it, with
subforms on each tab (JIT subforms).

Users on fast machines open the form in about 1.2 seconds.  Users on
very old slow machines take about 5 to 6 seconds.
***************************************
A form will open fast if designed well.
***************************************

Speed of the individual workstation is the single largest determinate of
acceptable speed.  A high speed processor and LOTS of memory (1 gig for
Windows XP Pro) are essential.  Moving to a 1 gbit lan made a big
difference as well (which requires a gigabit NIC in the machines as
well).
***************************************
You can keep throwing hardware at Access applications to will make them
perform faster. The real trick is to get your database to perform fast
regardless.
***************************************

Anita
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com


This message and any attached files may contain information that is confidential and/or subject of legal privilege intended only for use by the intended recipient. If you are not the intended recipient or the person responsible for delivering the message to the intended recipient, be advised that you have received this message in error and that any dissemination, copying or use of this message or attachment is strictly forbidden, as is the disclosure of the information therein. If you have received this message in error please notify the sender immediately and delete the message.