Post reply

SQL Query help

  • August 8, 2016 at 2:43 am

    #315962

    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

    #1894343

    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.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • August 8, 2016 at 3:11 pm

    #1894437

    Luis has given you good advice. Second the need for indexes.

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply