Performance Issue With Qty Counts By Day

I have an issue with the performance of the below script. I was under the impression that 1 select query would be fast than a while loop, but right now, that isn't the case... I'm on sql 2005 sp2, but the query plan is mostly the same on sql 2000 sp4 as well.

I've tried as best as possible to reproduce my situation with the below script. The data is limited to 2000 rows in CustomerDates, but in our environment, there are over 2million rows of customer activity. I want to summarize the Quantity owned per day, even if there was no activity.

Even though the below script is a small subset of data, the query plan given for it is the same as when there is a full set of data. The biggest hog of the execution is the Hash Match (Aggregate) section, being somewhere around 2-6 cost for this small set of data. That number times 1000 makes for a pretty massive query cost.

Does anyone have some ideas of what could be done to speed up the last query (there are 2 copies of it - each one using a different index).



SQL Code:






Original
- SQL Code




CREATE TABLE Integers
(
i int PRIMARY KEY
)

insert into Integers
values (0)
insert into Integers
values (1)
insert into Integers
values (2)
insert into Integers
values (3)
insert into Integers
values (4)
insert into Integers
values (5)
insert into Integers
values (6)
insert into Integers
values (7)
insert into Integers
values (8)
insert into Integers
values (9)


SELECT DATEADD(DAY, dt.i, '12-1-2006') as dtDate
INTO Dates
FROM
(
SELECT ones.i + tens.i * 10 + hundreds.i * 100 as i
FROM integers ones
CROSS JOIN integers tens
CROSS JOIN integers hundreds
) dt
WHERE dt.i < 150--limit the data to extend only into April 2007

CREATE UNIQUE CLUSTERED INDEX cnxDates ON Dates(dtDate)


SELECT CustomerID,
CASE WHEN CustomerID > 100 THEN ProductID ELSE 1 END as ProductID, --override productId of 1 to be 2 for some customers
CASE WHEN CustomerID > 100 OR ProductID = 1 THEN 10 ELSE -10 END as Qty,
CAST(CASE WHEN CustomerID > 100 OR ProductID = 1 THEN '1-1-2007' ELSE '3/1/2007' END AS datetime) as ActivityDate
into CustomerDates
from
(--make some customerIDs
select 1 + ones.i + tens.i * 10 + hundreds.i * 100 as CustomerID
from integers ones
cross join integers tens
cross join integers hundreds
) c
cross join
(
select i as ProductID
from integers dbl--double the records
where i IN (1, 2)
) p

CREATE UNIQUE CLUSTERED INDEX cnxUnique ON CustomerDates(CustomerID, ActivityDate, ProductID)

--make a copy of the table w/ a different style of index
SELECT * INTO alt_CustomerDates FROM CustomerDates
CREATE UNIQUE CLUSTERED INDEX cnxUnique ON alt_CustomerDates(ActivityDate, CustomerID, ProductID)



SELECT c.CustomerID, d.dtDate, SUM(c.Qty) AS quantity, c.ProductID
FROM Dates d
INNER JOIN CustomerDates c
ON c.ActivityDate <= d.dtDate
GROUP BY c.CustomerID, c.ProductID, d.dtDate
HAVING SUM(c.Qty) > 0

SELECT c.CustomerID, d.dtDate, SUM(c.Qty) AS quantity, c.ProductID
FROM Dates d
INNER JOIN alt_CustomerDates c
ON c.ActivityDate <= d.dtDate
GROUP BY c.CustomerID, c.ProductID, d.dtDate
HAVING SUM(c.Qty) > 0






CREATE TABLE Integers(    i int PRIMARY KEY)    INSERT INTO Integers   VALUES (0)   INSERT INTO Integers   VALUES (1)   INSERT INTO Integers   VALUES (2)   INSERT INTO Integers   VALUES (3)   INSERT INTO Integers   VALUES (4)   INSERT INTO Integers   VALUES (5)   INSERT INTO Integers   VALUES (6)   INSERT INTO Integers   VALUES (7)   INSERT INTO Integers   VALUES (8)   INSERT INTO Integers   VALUES (9)  SELECT DATEADD(DAY, dt.i, '12-1-2006') AS dtDateINTO DatesFROM   (   SELECT ones.i + tens.i * 10 + hundreds.i * 100 AS i   FROM integers ones      CROSS JOIN integers tens      CROSS JOIN integers hundreds   ) dtWHERE dt.i < 150--limit the data to extend only into April 2007 CREATE UNIQUE CLUSTERED INDEX cnxDates ON Dates(dtDate)  SELECT CustomerID,        CASE WHEN CustomerID > 100 THEN ProductID ELSE 1 END AS ProductID, --override productId of 1 to be 2 for some customers       CASE WHEN CustomerID > 100 OR ProductID = 1 THEN 10 ELSE -10 END AS Qty,       CAST(CASE WHEN CustomerID > 100 OR ProductID = 1 THEN '1-1-2007' ELSE '3/1/2007' END AS datetime) AS ActivityDateINTO CustomerDatesFROM   (--make some customerIDs   SELECT 1 + ones.i + tens.i * 10 + hundreds.i * 100 AS CustomerID   FROM integers ones      CROSS JOIN integers tens      CROSS JOIN integers hundreds   ) c   CROSS JOIN    (   SELECT i AS ProductID   FROM integers dbl--double the records   WHERE i IN (1, 2)   ) p CREATE UNIQUE CLUSTERED INDEX cnxUnique ON CustomerDates(CustomerID, ActivityDate, ProductID) --make a copy of the table w/ a different style of indexSELECT * INTO alt_CustomerDates FROM CustomerDatesCREATE UNIQUE CLUSTERED INDEX cnxUnique ON alt_CustomerDates(ActivityDate, CustomerID, ProductID)   SELECT c.CustomerID, d.dtDate, SUM(c.Qty) AS quantity, c.ProductIDFROM Dates d   INNER JOIN CustomerDates c      ON c.ActivityDate <= d.dtDateGROUP BY c.CustomerID, c.ProductID, d.dtDateHAVING SUM(c.Qty) > 0 SELECT c.CustomerID, d.dtDate, SUM(c.Qty) AS quantity, c.ProductIDFROM Dates d   INNER JOIN alt_CustomerDates c      ON c.ActivityDate <= d.dtDateGROUP BY c.CustomerID, c.ProductID, d.dtDateHAVING SUM(c.Qty) > 0