January 27, 2004 at 9:04 am
I have a sales summary table that is used to keep track of our sales with the following simplified structure. The table contains about 250 mil records (sales history for 3 years). This table is loaded as part of a DW job.
Sales
-------
SaleWeek (PK)
StoreId (PK) (150 possible values)
ItemId (PK) (~500,000 possible values)
SaleQuantity
SalesAmount
There are Item and Store tables too.
Item
------
ItemID
Category
Store
-------
StoreId
StoreChain
The clustered index (and PK) is on Saleweek, Store and Item. There is a NC index on Item, Store and saleweek. Most of the queries use the Clustered Index. However recently there is a requirement to calculate the 52 week sales sum for certain items (identified by category) for certain stores (identified by Chain). The query, simplified below, takes long and the query plan indicates it is using the NC index (as expected) and spending a good amount of time (over 80%) in the bookmark lookup.
select sum(SalesAmount) SalesSum
from Sales join Item on Sales.ItemId = Item.itemId
join Store on Sales.StoreID = Store.StoreID
where
saleweek between Dateadd (ww, -52, saleweek ) and saleweek
and item.Category = @Category
and Store.StoreChain = @Chain
I made a copy of the table, transposed the indexes (ie made the non-clustered index -- > clustered and vice versa), now it takes a fraction of what it did before. The question is, we need to do the 52 week running sales sum for each Category every week. If I were to keep this solution, I would need to keep two tables that are identical except different index structures.
I also need to modify the load process to load the weekly sales data into two tables.
Are there any other reasonable solutions?
Thanks.
January 27, 2004 at 9:38 am
Do you have indexes on the Item and Store tables? How about using an index hint?
What index did you change to a clustered index when the new table was created...kind of curious here 🙂
"Keep Your Stick On the Ice" ..Red Green
January 27, 2004 at 9:44 am
I do have indexes on Item (Clustered: Category, ItemID) and Stores(Clustered: Chain, StoreID) tables.
On the Sales Table, the initial clustered index is on Saleweek, Store and Item. The non clustered was on Item, Store and Salesweek.
On the new table, I made the clustered on Item, Store and Salesweek and NC on Saleweek, Store and Item.
January 27, 2004 at 9:58 am
The selectivity on the Item+Store+Salesweek index appears to be much better than the Salesweek+Store+Item index.
A non-clustered index creates more i/o and lookups when accessing the datapage and looking for the SalesAmount. Once the record is found it uses the key value for that record to transverse the tree of the clustered index to pull the data for the query. I find it helpful to put the most selective columns first when creating tables and indexes.
Any chance you can change the structure (order of the columns) of the clustered index with the original table? Would performance be hurt somewhere else?
Since this is a DW you might want to think about adding SalesAmount to the NC index in the original table.
"Keep Your Stick On the Ice" ..Red Green
January 27, 2004 at 1:23 pm
Make the non-clustered index a covering index by adding the salesamount field to it. ie. Item, Store, Saleweek, salesamount. This will eliminate the bookmark lookup which I imagine is causing the query to be so slow.
Try it and let us know.
Tom
January 28, 2004 at 3:24 pm
Thanks for your input. I sincerely appreciate it.
Though I could add the sales filed to the NC index, I am short on time to test out the apps that use the index. I am instead going with a pre-summarized table, that keeps track of sales sum for each item by period, so I don't have to go the base table for 52 week summary. It seems to be working within our expectations.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply