Datediff / computed column / indexing in data warehouse

Hi
I've picked up a data warehouse where i have a sales FACT table, and i
need to run a report that shows sales by shop for this week this year
vs. sales for this week last year.
ie.
WEEKLY SALES, WEEK 10
SHOP--THIS YEAR--LAST YEAR
a--100,000--85,000
b--120,100--103,000
etc
The fact table has a DATE field for each sales transaction
grain-record. There are about 25million records in the table.
My resultant select statement will do something like (trying to
remember):
SELECT
store_name,
SUM(a.sales_amount) AS this_year,
SUM(b.sales_amount) AS last_year
FROM sales a
INNER JOIN sales.b
ON b.sales_date = DATEADD(yy, -1, a.sales_date)
WHERE sales_week = 10
GROUP BY store_name
The trouble is, this is taking forever to query. I am at a loss as to
how to improve the query / table design to improve this performance.
Can anyone suggest anything? Should I have another computed column on
my table to help the query know which records it must look up for one
year ago's sales, or is there some index that i am missing'
Thanks
SeanI assume that the join is the problem (since it uses a function on a column
that is hopefully indexed, causing the plan to avoid the index). Does the
following pseudo-code query help?
select year, store,
sum(case when year = 2003 then sales else 0 end) as this_year,
sum(case when year = 2002 then sales else 0 end) as last_year
from (
select 2003 as year, store, sum(sales) as sales
where week = 10 and sales_date between 20030101 and 20031231 group by ...
union all
select 2002, store, sum(sales)
where week = 10 and sales_date between 20020101 and 20021231 group by ...
) as both_years
group by ...
Note - I've made certain assumptions about your use of the terms "this year"
and "last year". Without knowing anything else about your table (including
the DDL and the indexes you currently have), I'm hoping that it has an index
(preferably clustered) involving sales_date. It would be even better if it
also included sales_week. Lastly, since your subject included the term
"computed column", it may be beneficial to tell us what the computed column
is and why you believe that it is pertinent to the problem.
"Sean Walsh" <plugwalsh@.yahoo.com> wrote in message
news:3698af3c.0309180314.429a4f53@.posting.google.com...
> Hi
> I've picked up a data warehouse where i have a sales FACT table, and i
> need to run a report that shows sales by shop for this week this year
> vs. sales for this week last year.
> ie.
> WEEKLY SALES, WEEK 10
> SHOP--THIS YEAR--LAST YEAR
> a--100,000--85,000
> b--120,100--103,000
> etc
> The fact table has a DATE field for each sales transaction
> grain-record. There are about 25million records in the table.
> My resultant select statement will do something like (trying to
> remember):
> SELECT
> store_name,
> SUM(a.sales_amount) AS this_year,
> SUM(b.sales_amount) AS last_year
> FROM sales a
> INNER JOIN sales.b
> ON b.sales_date = DATEADD(yy, -1, a.sales_date)
> WHERE sales_week = 10
> GROUP BY store_name
> The trouble is, this is taking forever to query. I am at a loss as to
> how to improve the query / table design to improve this performance.
> Can anyone suggest anything? Should I have another computed column on
> my table to help the query know which records it must look up for one
> year ago's sales, or is there some index that i am missing'
> Thanks
> Sean