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

Mark Breen marklbreen at gmail.com
Thu Feb 17 06:11:41 CST 2005


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


On Mon, 14 Feb 2005 07:33:43 -0600, Steve Erbach <erbachs at gmail.com> wrote:
> Mark,
> 
> I have to confess that I've resisted writing much SQL from scratch
> because of all the blankety-blank typing one has to do! Then there are
> the inevitable spelling mistakes. I don't mind using the View and
> Sproc designer in Enterprise Manager because that's what it looks like
> while designing an Access ADP. I have used visual query tools for so
> long -- indeed, since 1985 and Paradox for DOS -- that that's the way
> I think. With a complex query I'd be sure to mess up the JOINs.
> 
> The two queries I created to get the count were SELECT DISTINCT and
> then COUNT. Regarding performance or best practice, the tables are so
> small that it never occured to me to test their performance. The
> largest table is maybe 2000 records. Maybe later I'll look at running
> these queries multiple times in a loop and time them. Would that be
> what you're looking after?
> 
> Steve Erbach
> Neenah, WI
> 
> 
> On Mon, 14 Feb 2005 11:37:55 +0000, Mark Breen <marklbreen at gmail.com> wrote:
> > Hello Steve,
> >
> > I first discovered this a few years ago.  Because the SQL window in
> > Access is not a great text editor, we tend not to do much SQL editing
> > in there, is is also very easy to embed an existing query into an
> > Access QBE window.  However in the MS SQL environment, I have tended
> > to nest my queries like this without thinking about it.  I do not
> > think that it makes the query run much faster, but it does wrap it all
> > in one package.
> >
> > Another aspect of laying out the queries in SQL is that the editor
> > encourages me to comment as I build the SQL string, this makes it
> > easier for me to later on figure out what I was doing.
> >
> > Once you get used to embedding them, they can sometimes make us a
> > little lazy, i.e., we want some data, we just grab it in a nested
> > query and tie it in.  It may be that sometimes there is a more
> > efficient way to achieve that data!
> >
> > Going back to your original question though, you mentioned that you
> > wished to avoid two queries.  Was the two queries you were referring
> > to the Count and the Group By?
> >
> > I am presuming that you were asking the question as a best practice /
> > idiological aspect rather than a pure performance point.  What I am
> > curious about now is, which format do you think is more efficient,
> > Count and Group By in one query, or Select Distinct in one and count
> > in another?
> >
> > Thanks for the interesting question.
> >
> > Mark
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>



More information about the AccessD mailing list