[AccessD] A2000: Am I blind? (swe)

Mark,

Yikes! I haven't gotten within sniffing distance of a database that
big. Of course that would encourage me to make queries as efficient as
possible.

>> No so much looking after anything, I was just 'impressed' with your
consideration of the fact that there were two queries happening when
one does a count and a group by or distinct, I suppose that is the
case, but I never thought of it like that before. <<

I have thought about queries in this way since my Paradox days. It was
usually quicker to do two simple queries rather than make one query do
multiple things. Although I must say that it was possible in a Paradox
query to append records from one table to another and then make a new
table from the combined result.

I have a fondness for MakeTable queries because of my Paradox
experience. I like having an "audit trail" of intermediate query
results if I have a long process requiring lots of calculations and
joins.

Good luck with your "mega" queries.

Steve Erbach

On Thu, 17 Feb 2005 12:11:41 +0000, Mark Breen <marklbreen at gmail.com> wrote:
> Hiya Steve,
> 
> No so much looking after anything, I was just 'impressed' with your
> consideration of the fact that there were two queries happening when
> one does a count and a group by or distinct, I suppose that is the
> case, but I never thought of it like that before.
> 
> I always know that grouping and summing puts an overhead on the
> engine, as does order by, but I liked the train of thought that says
> it is two queries that are running.  Prior to this, I would only have
> referred to two queries as a nested select.
> 
> I suppose it is terminology really, and opening up the concept of what
> a 'query' is makes me think a little closer about my queries.
> 
> As you say, when there are only 2k records, it does not matter, I am
> working on some queries at the moment, in Dublin that have millions of
> big records joined to millions of records, an order by in this case,
> totally floors the query, where as removing it gives instant
> responses.  Also, a carefully selected criteria that was optional
> moves it from being minutes to execute to immediate !
> 
> Thanks for the thought process
> 
> Mark