August 8, 2016 at 2:43 am
Hi All,
I have four tables:
Tab1 (Master Table): contains MasterProduct and ChildProduct (having one to many relations)
Tab2 (Master Table): contains MasterLocation and ChildLocation (again having one to many relations)
Tab3 (Fact Table): contains records having BusinessDate, ChildProduct and ChildLocation along with cost
Tab4 (Fact Table): contains records having BusinessDate, MasterProduct and MasterLocation
I've to write a query in efficient way to find cost at MasterProduct and MasterLocation level for a BusinessDate. I've written a query like this:
select T4.businessdate, T4.MasterProduct, T4.MasterLocation, sum(T3.cost)
from Tab4 T4
inner join Tab3 T3 on T4.businessdate = T3.businessdate
inner join Tab1 T1 on T4.MasterProduct = T1.MasterProduct and T3.ChildProduct = T1,ChildProduct
inner join Tab2 T2 on T4.MasterLocation = T2.MasterLocation and T3.MasterLocation = T2,MasterLocation
group by T4.businessdate, T4.MasterProduct, T4.MasterLocation
Let me know if we can have some other way of writing this efficiently. Since these tables are in OLTP environment, there are no clustered indexes. Tables T3 and T4 have only non clustered indexes.
August 8, 2016 at 8:10 am
This seems like a weird design. Why would you have a transaction table for master product and master locations when you're supposed to be able to get them through your Product and Location tables?
Also, having no clustered indexes because is an OLTP database is a bad decision, especially when having non-clustered indexes on some tables. Clustered indexes are important as they don't add overhead when writing or deleting. They don't take additional storage space because they're the table, not just pointers as non-clustered indexes.
Your query has errors which are corrected in here:
select T4.businessdate, T4.MasterProduct, T4.MasterLocation, sum(T3.cost)
from Tab4 T4
inner join Tab3 T3 on T4.businessdate = T3.businessdate
inner join Tab1 T1 on T4.MasterProduct = T1.MasterProduct and T3.ChildProduct = T1.ChildProduct
inner join Tab2 T2 on T4.MasterLocation = T2.MasterLocation and T3.ChildLocation = T2.ChildLocation
group by T4.businessdate, T4.MasterProduct, T4.MasterLocation;
Without DDL, sample data, expected results and execution plans, there's no way I can tell you that it's the best way, but it might be.
August 8, 2016 at 3:11 pm
Luis has given you good advice. Second the need for indexes.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply