Thanks for the tip!

Thanks,

enzo c",)

-----Original Message-----
From: Jeff Mason [mailto:je.mason@a...]
Sent: Thursday, March 13, 2003 7:34 PM
To: sql language
Subject: [sql_language] RE: which is faster, using DISTINCT or GROUP BY


The DISTINCT keyword requires a sort and grouping operation, so it is likely
to be equivalent to the GROUP BY operation performance-wise.  A lot depends
on the presence of indexes and constraints and the distribution of data
values particular to your situation.  This may be one of those "try it and
see" tests.

That having been said, it seems that your GROUP BY alternative query is
doing too much work, sorting (grouping) more rows than it needs to.  In the
processing of a SELECT statement, the only rows that are included in the
grouping operation are those that pass the WHERE test.  Those rows are then
reduced to groups, and then the grouped rows are reduced by the HAVING
clause.  You are grouping *all* the rows from the JOIN of the two tables
then selecting the desired rows *after* the grouping operation has been done
(via the HAVING clause).  Do it before:

	SELECT tblITEMS.lngItemID, tblITEMS.strDesc
		FROM tblITEMS INNER JOIN tblTRANX
			ON tblITEMS.lngItemID=tblTRANX.lngItemID
	WHERE tblTRANX.dtmTranDate=#03/13/2003#
	GROUP BY tblITEMS.lngItemID, tblITEMS.strDesc;


--
Jeff Mason              Custom Apps, Inc.
Jeff@c...


-----Original Message-----
From: Enzo Zaragoza [mailto:enzaux@g...]
Sent: Thursday, March 13, 2003 2:06 AM
To: sql language
Subject: [sql_language] which is faster, using DISTINCT or GROUP BY



hi guys,

	just wanna ask whether which is faster to use, DISTINCT or GROUP BY?  My
situation is like this, everyday I need to create a query
that would give me all the list of items sold for the day.  Below is a
sample table:

tblTRANX
---------------------------------------------------------
lngTranID  lngInvoiceID  lngItemID  dtmTranDate  ...so on
---------------------------------------------------------
123          234           001       03/13/2003
124          234           002       03/13/2003
125          235           001       03/13/2003
126          236           002       03/13/2003
127          236           003       03/13/2003

So my query would be:

SELECT DISTINCT tblITEMS.lngItemID, tblITEMS.strDesc FROM tblITEMS INNER
JOIN tblTRANX ON tblITEMS.lngItemID=tblTRANX WHERE
tblTRANX.dtmTranDate=#03/13/2003#

or

SELECT tblITEMS.lngItemID, tblITEMS.strDesc FROM tblITEMS INNER JOIN
tblTRANX ON tblITEMS.lngItemID=tblTRANX GROUP BY
tblITEMS.lngItemID, tblITEMS.strDesc, tblTRANX.dtmTranDate
HAVING tblTRANX.dtmTranDate=#03/13/2003#

Which is more efficient to use?

thanks,

enzo