Sander,
Including YEAR(orderdate) in the GROUP BY clause is my first guess as
to what makes this slow, assuming there is an index on (client_id,
category, orderdate) or (category, client_id, orderdate). The YEAR()
function eliminates the possibility of using indexes to evaluate the
subquery. If you must know the latest date *in each year* each client
placed an order for each category, consider one of the following solutions:
1. Use an auxiliary table of dates containing an indexed column
containing January 1 at midnight of every year, then join to that table
as well with AND orderdate < newtable.date, grouping on newtable.date
instead of YEAR(orderdate).
2. Depending on indexes, it may be faster to run separate queries for
each year and collect results in a temp table for output, manually
inserting WHERE orderdate < '2004-01-01T00:00:00.000' and so on, in
place of the grouping by year.
3. alter table orders add order_year as YEAR(orderdate), to add a
computed column to orders, then put an index (clustered or not depends
on other stuff) orders on (order_year, client_id, category, orderdate).
SK
[quoted text, click to view] Sander wrote:
>Hi! I hope somebody has a faster solution for me!
>
>Basicly I have a table containing:
>client_id, orderdate, category, amount
>
>I want to select the amount where MAX(orderdate) = true grouped by
>year
>
>I currently have:
>
>SELECT
> amount
>FROM orders A
>INNER JOIN
> (SELECT
> client_id,
> MAX(orderdate) AS maxdate,
> category
> FROM orders
> GROUP BY client_id, YEAR(orderdate), category) B
>ON A.client_id = B.client_id AND A.orderdate = B.maxdate AND
>A.category = B.category
>
>Is this the fastest solution available? Or is there some magic HAVING
>orderdate = MAX(orderdat) solution that I don't know of?!
>
>--Sander
>
>
>
Mmm... I forgot that I can use the SUM function with the amount. So
that gives me which I consider to be fast enough:
SELECT
client_id,
category,
SUM(amount)
FROM dwh_orders
GROUP BY client_id, category, YEAR(orderdate)
HAVING YEAR(orderdate) = YEAR(MAX(orderdate))
Sorry for this post, the problem is that I've been talking to myselve
for the whole day :)
--Sander
On Sun, 15 Feb 2004 20:57:24 +0100, Sander <nospam@thisaddress.com>
[quoted text, click to view] wrote:
>Hi! I hope somebody has a faster solution for me!
>
>Basicly I have a table containing:
>client_id, orderdate, category, amount
>
>I want to select the amount where MAX(orderdate) = true grouped by
>year
>
>I currently have:
>
>SELECT
> amount
>FROM orders A
>INNER JOIN
> (SELECT
> client_id,
> MAX(orderdate) AS maxdate,
> category
> FROM orders
> GROUP BY client_id, YEAR(orderdate), category) B
>ON A.client_id = B.client_id AND A.orderdate = B.maxdate AND
>A.category = B.category
>
>Is this the fastest solution available? Or is there some magic HAVING
>orderdate = MAX(orderdat) solution that I don't know of?!
>
>--Sander
[quoted text, click to view] > HAVING YEAR(orderdate) = YEAR(MAX(orderdate))
That HAVING clause doesn't actually achieve anything except removing any
NULL orderdates from the result. Because you are grouping by YEAR(orderdate)
the expression YEAR(orderdate) = YEAR(MAX(orderdate)) will *always* evaluate
to TRUE if orderdate is non-null. If this query gives the result you want
then just remove the HAVING clause altogether or add:
WHERE orderdate IS NOT NULL
instead of HAVING.
--
David Portas
SQL Server MVP
--
Don't see what you're looking for? Try a search.
