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 >