[AccessD] Query tuning in Access?

Correct on the first one.

For what concerns report.
In my opinion, for reports, you try to use a SQL string (unless you need
cascaded queries) thats sit on the rowsource.
Otherwise you gonna get a lot of queries (1 per report), unless you
reuse a query for multiple reports.
If you do that you gonna put more fields in one query to cover all
reports which will slow down the query.
Also I find for myself, the danger is bigger that you sort the query on
a field that can be contraproductive if the sorting in the report is
otherwise, same thing for filtering.

Probably there are other reasons to, but from what I read in books and
websites this is the commonly advised method. Anyway there is/was a big
reason why I started to do that I believe in this case:

At a moment (a couple of years ago) I started to have user editable
reportfilters from a form.
I found it practicle to open the filterform when opening the report in
the open_report event.
I found this better then first opening the form and then the report.
Don't know why exactly, but while developing I found it more practicle
to open the report and the formfilter.
I believe I noticed after a while that when the report uses a saves
query the report executed the query twice.
Once to know what fiels are in the query and twice to apply my criteria
I just had set when cosling the filter form.
I'm not sure anymore, and maybe this is A97 thing that already long
solved in later version...

I regulary notice procedures that I use to do are stricly no longer
needed because new access functionality (or debugging) has solved this.


Anyway personal basic rule is:
Filter as soon as posible, sort as late as posible and only once.
Ofcourse this is not always posible, espcialy in case of using group and
cross queries

Erwin



 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
Sent: Thursday, February 24, 2005 10:19 AM
To: accessd at databaseadvisors.com
Subject: RE: [AccessD] Query tuning in Access?

Hi Erwin

One should leave the sorting to the report's Sorting and Grouping and if
possible avoid any sorting in the source.

And what is wrong with a saved query as source for a report? Many of our
reports uses quite complicated queries as source. It would be a mess to
pull that SQL into the recordsource of the reports.

/gustav

3) In a report, if SQL string based on saved querie, do not sort in
saved querie but only in SQL string of report.

Infact avoid reports based on saved queries, if you can.

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