which would be faster

[Guest]

Simple question, which one of these two select sql would be faster. I don't
have a large database for testing it and seeing a real difference. But
eventually I will have one.

SQL 1 :

SELECT SALESMANS, PRODUCT, SUM(SALES)
FROM SALES
WHERE PRODUCT = "TOYS"
GROUP BY SALESMANS
ORDER BY SALESMANS

SQL 2 :

SELECT SALESMANS, PRODUCT, SUM(SALES)
FROM SALES
GROUP BY SALESMANS
HAVING PRODUCT = "TOYS"
ORDER BY SALESMANS

The one which is in use in the code that I'm optimizing is SQL 2. I know
that SQL 2 is probably not the best for the situation but this is the kind of
job you have to do when taking code from another person. I just wanted to
know if there's a big enough difference between the 2.

Thanks

 

[Douglas J. Steele]

As I understand it, the first should be slightly faster.

A HAVING clause is like a WHERE clause, but applies only to groups as a
whole (that is, to the rows in the result set representing groups), whereas
the WHERE clause applies to individual rows. A query can contain both a
WHERE clause and a HAVING clause. In that case:

The WHERE clause is applied first to the individual rows in the tables or
table-structured objects in the diagram pane.. Only the rows that meet the
conditions in the WHERE clause are grouped.

The HAVING clause is then applied to the rows in the result set that are
produced by grouping. Only the groups that meet the HAVING conditions appear
in the query output. You can apply a HAVING clause only to columns that also
appear in the GROUP BY clause or in an aggregate function.

 

[Tim Ferguson]

Simple question, which one of these two select sql would be faster.

SELECT SALESMANS, PRODUCT, SUM(SALES)
FROM SALES
WHERE PRODUCT = "TOYS"
GROUP BY SALESMANS
ORDER BY SALESMANS

SELECT SALESMANS, PRODUCT, SUM(SALES)
FROM SALES
GROUP BY SALESMANS
HAVING PRODUCT = "TOYS"
ORDER BY SALESMANS

I hesitate to differ from Doug, but I am not sure that either of these is
actually legal. They should return an error complaining that "Product"
cannot appear in the field list without also appearing in the Group By
list. Perhaps this is a typo -- perhaps you meant

GROUP BY Salesmans, Product

after all?

(P.S. yes, I'd use the first syntax rather than the second one. The
second version is designed for group-aggregate tests such as

HAVING SUM(Sales.Sales)>1000.00


HTH

Tim F

 

[Douglas J. Steele]

I hesitate to differ from Doug, but I am not sure that either of these is
actually legal. They should return an error complaining that "Product"
cannot appear in the field list without also appearing in the Group By
list. Perhaps this is a typo -- perhaps you meant

GROUP BY Salesmans, Product

after all?

(P.S. yes, I'd use the first syntax rather than the second one. The
second version is designed for group-aggregate tests such as

HAVING SUM(Sales.Sales)>1000.00

D'oh! Good catch, Tim! (Although it would be legal to use HAVING PRODUCT =
"TOYS")

 

[Allen Browne]

Like you, Doug, I've always assumed that the WHERE clause would be faster -
especially on an indexed field - since JET could just ignore the
non-matching records instead of having to aggregate them all and then throw
out the non-matching results (as the HAVING clause implies).

The question got me wondering whether the query optimizer is clever enough
to choose the most efficient path anyway. Something else to add to the list
of things to do when there is nothing to do.

 

[Tim Ferguson]

The question got me wondering whether the query optimizer is clever
enough to choose the most efficient path anyway. Something else to add
to the list of things to do when there is nothing to do.

<mutter> free time... free time... I know I had some once, don't know where
it's got to... perhaps under this pile of work..? </mutter>

Tim F

 

[david epsom dot com dot au]

On Jet, you would expect a properly formed Where clause to
be faster than a Having clause. The time is taken in Sorting
the recordset: If the record source is already sorted by
SALSESMANS, then there may be no measurable difference.

When connecting to other database engines, you may get
more complex behaviour. For example, SQL Server predates
several ANSI SQL standards, and Version 6.5 could give
you different results than SS2000 on queries like those.

(david)