[AccessD] Performance tips anyone?

Hi Gustav,

The FEs are on the local machine... sorry for not specifying this.

The temp tables are on the local machine also, but I never thought to have
those table put in a completely seperated db and to recreate them at start
up... I'll have to look this up.

Thanks!



Annie Courchesne, CMA


 -----Message d'origine-----
De : 	accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]  De la part deGustav Brock
Envoyé :	5 juillet, 2007 07:03
À :	accessd at databaseadvisors.com
Objet :	Re: [AccessD] Performance tips anyone?

Hi Annie

It sounds like you have the FEs on the server?

Try to copy them to a workstation, relink BE tables and test performance.
You should observe a great improvement.
If so, at start up of a workstation, copy a master of each FE to a local
drive and use these.

Or (also,) move the temp tables to a separate local database which you
recreate at every start up. Write protect the report FE to disable bloat.

/gustav

>>> accma at sympatico.ca 05-07-2007 12:35 >>>
Hi all,

Wow, lots of great tips!

Any thoughts about having a dedicated server for the BE db only?

The BE db is 650mb with 200 tables.  The largest table 1.5 millions records.

The FE db is split in two db, one with the forms and one with the report.
The form db is 45mb with about 2000 objects.  The report db is 14 mb with
about 1000 objects.  The report db as to be compacted regularly since it's
using temp table and the size can grow to 500 mb depending of the report
printed.

Thanks!

Annie Courchesne, CMA


-----Message d'origine-----
De : accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] De la part de Steve Schapel
Envoyé : 5 juillet 2007 01:10
À : Access Developers discussion and problem solving
Objet : Re: [AccessD] Performance tips anyone?

Annie,

If you have forms with lots of comboboxes, it certainly helps if you
don't populate them unless and until they are needed, i.e. set the Row
Source on their Enter event.

Similarly, if you have forms with subforms on different pages of a tab
control, leave the Source Object of the subforms blanks, and only set it
if and when that tab is selected.

And here's another thing I only found out about this week... the first
time a saved query is run after the database is compacted, will be slow,
because the query plan has to be re-created.  So if you compact/repair
the front-end application file, queries will run slow the first time,
and forms based on queries will open slowly the first time.  An argument
against 'Compact On Close' - but then, for memory, I don't think Comapct
On Close was available in Access 97.

Regards
Steve


Annie Courchesne, CMA wrote:
> Hi all,
>
>
>
> I have a customer that complains about his database (BE/FE A97 running in
> runtime mode) is slow.  The number of concurrent user keep growing over
the
> years and it's up to 10 or 12 now.
>
>
>
> What I'm looking at right now is to optimize the whole database and
upgrade
> to Access 2003.  I've look at the performance tips from this page
> (http://www.granite.ab.ca/access/performancefaq.htm) and I've found some
> pretty usefull information.
>
>
>
> Anyone has other tips on getting this database more performing?
>
>
>
> I was also wondering if using a dedicated server for the database would
help
> to improve performance?
>
>
>
> And what about SQL Server 2005 Express?  I've read here that it's free and
> has a large capacity (more than enough for what I need).  Will it really
> help in speeding up the database?  How hard is it to set up?  Any good
> documentation I can read on this?
>
>
>
> Thanks to all of you!
>
>
>
>
>
> Annie Courchesne, CMA



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