SQL Server Performance

Warehouse Query

Discussion in 'T-SQL Performance Tuning for Developers' started by jain_vivek_in, Feb 28, 2005.

  1. jain_vivek_in New Member

    Hello All,

    I have a warehouse database which has tables like SalesFact[CustomerId, ProductId, DateId, NetSales, NetUnit] rows(3000000), Customer[CustomerId, CustomerName] rows (40000), Date[DateId, Month, Date, Year] rows(1000), Product[ProductId, ProductName] rows(100000). I need to write a query which shows the top N customer order by Sum(NetSales). If for a month there is no sales (rows) then that month should appear in the output with 0 rows and for a product in the specified date ranges. The output of the query should be like

    Customer1 ,Product1 ,Jan ,NetSales , NetUnits
    Customer1 ,Product1 ,Feb ,NetSales , NetUnits
    Customer1 ,Product1 ,Mar ,0 , 0 (if no rows for the Mar sales)

    ......................

    Customer1 ,Product2 ,Jan ,NetSales , NetUnits
    Customer1 ,Product2 ,Feb ,NetSales , NetUnits

    .....................

    CustomerN ,ProductN, Mar, NetSales , NetUnits
    .....

    OtherCustomers, ,Jan,NetSales , NetUnits
    OtherCustomers, ,Feb,NetSales , NetUnits
    OtherCustomers, ,Mar,NetSales , NetUnits
    ...............................................

    OtherCustomers are the customer which are not fall under the TOP N condition.
    As the rows in each tables are huge I need to write the optimized query. Could anybody suggest me what query and index would be best in this case.

    Thanks,
    Vivek
  2. mmarovic Active Member

    Number of rows is pretty modest compared to number I deal with every day. It should't be the problem if you just put indexes on pk and fk columns. If it is the most frequent/time consuming query by far, and you don't have frequent updates you could add index on SalesFact (CustomerID, NetSales). Besides clasic join between fact and dimension tables you have to include join with:
    Select top N CustomerID, Sum(NetSales)
    from SalesFact
    group by CustomerID
    order by 2
    Because (top) N is constant in mssql 2000 you will need to use dynamic sql if you need N as variable. I suggest to put your question in dw forum, someone may offer better solution I am not aware of.

Share This Page