Filters and sorts in datasheet view

[phobos]

I have a complex query built up out of lots of little queries; I run
the final one and look at the output. This query takes a little while
to run - like maybe a couple of minutes. Lots of calculations about
the place, being done on a few thousand records; this could probably
be improved upon as I learn new tricks.

But I notice that when I add a filter, or I sort the results, it takes
a long time to run - even though all the calculations have already
been done, and all that's being filtered or sorted is a set of a few
hundred rows of four or five columns. Not so computationally
intensive, one would have thought.

Is Access actually appending an ORDER BY or a WHERE clause to the
original query and then recalculating everything from scratch? If so,
_why_? If not, why the big hold-up?

[Lyle Fairfield]

ph*****@hotmail.com (phobos) wrote in news:af26c87a.0402160657.78407d21
@posting.google.com:

I have a complex query built up out of lots of little queries; I run
the final one and look at the output. This query takes a little while
to run - like maybe a couple of minutes. Lots of calculations about
the place, being done on a few thousand records; this could probably
be improved upon as I learn new tricks.

But I notice that when I add a filter, or I sort the results, it takes
a long time to run - even though all the calculations have already
been done, and all that's being filtered or sorted is a set of a few
hundred rows of four or five columns. Not so computationally
intensive, one would have thought.

Is Access actually appending an ORDER BY or a WHERE clause to the
original query and then recalculating everything from scratch? If so,
_why_? If not, why the big hold-up?

Perhaps you should post the SQL of these queries? Often JET will not optimize
calculated expressions nor sub-queries.

--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)

[Fletcher Arnold]

"phobos" <ph*****@hotmail.com> wrote in message
news:af**************************@posting.google.c om...

I have a complex query built up out of lots of little queries; I run
the final one and look at the output. This query takes a little while
to run - like maybe a couple of minutes. Lots of calculations about
the place, being done on a few thousand records; this could probably
be improved upon as I learn new tricks.

But I notice that when I add a filter, or I sort the results, it takes
a long time to run - even though all the calculations have already
been done, and all that's being filtered or sorted is a set of a few
hundred rows of four or five columns. Not so computationally
intensive, one would have thought.

Is Access actually appending an ORDER BY or a WHERE clause to the
original query and then recalculating everything from scratch? If so,
_why_? If not, why the big hold-up?

The general approach to relational databases is not to duplicate data That
is if you have tblOrderItems = ItmID, ItmQty, ItmUnitCost, etc, then having
a field tblOrders.OrderTotal is redundant and should be avoided as it could
lead to contradictory information.
However, there are times and this might be one of them where you create a
table of totals which has indexed fields where you do the sorting /
filtering. So if you analyse yearly sales totals, the table might have
ProductType and FinancialYear as indexed fields so you could quickly find
the summarised data without re-calculating from scratch.

[phobos]

Lyle Fairfield <Mi************@Invalid.Com> wrote in message news:<Xn*******************@130.133.1.4>...

ph*****@hotmail.com (phobos) wrote in news:af26c87a.0402160657.78407d21
@posting.google.com:

I have a complex query built up out of lots of little queries; I run
the final one and look at the output. This query takes a little while
to run - like maybe a couple of minutes. Lots of calculations about
the place, being done on a few thousand records; this could probably
be improved upon as I learn new tricks.

But I notice that when I add a filter, or I sort the results, it takes
a long time to run - even though all the calculations have already
been done, and all that's being filtered or sorted is a set of a few
hundred rows of four or five columns. Not so computationally
intensive, one would have thought.

Is Access actually appending an ORDER BY or a WHERE clause to the
original query and then recalculating everything from scratch? If so,
_why_? If not, why the big hold-up?

Perhaps you should post the SQL of these queries? Often JET will not optimize
calculated expressions nor sub-queries.

I don't think it's them that's the problem. They're slow, sure:
calculated expressions everywhere, which are used as lookups in later
queries on the ones that have gone before - really a hack until I get
a better design sorted out. I'm fairly new at this game, but I'm
getting there...

What puzzles me is that Access seems to rerun the entire thing when I
do something simple like right-click and choose 'Filter by Selection'.
Once it's generated this result, surely it doesn't have to do it all
again just in order to pick out a few rows and eliminate the rest?

Is this what you mean by Jet not optimising them? Because I've joined
on calculated values, for some reason it can't retain the output and
instead has to recalculate the whole lot?

[Alan Webb]

Phobos,
A filter is in essence an addition to the existing SQL statement that
produced the result set which further limits the returned rows or sorts
them. So, yeah, Access has to rerun the SQL to return the requested result
set and that can take time. If you have a complex query built up out of
lots of little queries you might improve things by building temporary tables
with your intermediate results and using the temporary tables in subsequent
steps. Better yet, use VBA to script your process so the end result is a
temporary table with the results. Then you can index the result table and
dramatically reduce the recalculation needed to apply a filter or sort.

ph*****@hotmail.com (phobos) wrote in news:af26c87a.0402160657.78407d21
@posting.google.com:

I have a complex query built up out of lots of little queries; I run
the final one and look at the output. This query takes a little while
to run - like maybe a couple of minutes. Lots of calculations about
the place, being done on a few thousand records; this could probably
be improved upon as I learn new tricks.

[phobos]

"Alan Webb" <kn*****@hotmail.com> wrote in message news:<p1*****************@news.uswest.net>...

Phobos,
A filter is in essence an addition to the existing SQL statement that
produced the result set which further limits the returned rows or sorts
them. So, yeah, Access has to rerun the SQL to return the requested result
set and that can take time.
I thought it might be. Still seems like an odd design, though. I
suppose it has to be this way to take account of possible updates made
by other users...
If you have a complex query built up out of
lots of little queries you might improve things by building temporary tables
with your intermediate results and using the temporary tables in subsequent
steps.
Interesting idea. In fact, it's probably a very good idea: there are
three separate queries that really just format the output of this
whole structure in different ways for use in reports. Storing the
results of the final stage and then running the output queries on that
store would improve things quite a bit.
Better yet, use VBA to script your process so the end result is a
temporary table with the results. Then you can index the result table and
dramatically reduce the recalculation needed to apply a filter or sort.

Or, I suppose, output the whole recordset to Excel and use that :-)

I mean to start moving a lot of this stuff into VBA anyway... it's
certainly easier to follow what's going on in a good VBA module than
in a collection of cryptically named, uncommented fragments of SQL.