[AccessD] Performance tips anyone?

>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.

Ohhhh that is something to consider, I kinda knew this but never thought of
it in quite this light.  I build and then upload to a server.  The users
download the FE every day.  Thus the first time they open anything it will
be slow, every day.  Sigh.

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 Steve Schapel
Sent: Thursday, July 05, 2007 1:10 AM
To: Access Developers discussion and problem solving
Subject: 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