Post reply

Alternate Query for better performance

  • March 8, 2019 at 4:57 am

    #416785

    Hi,

    I have two massive tables, Order Header and Order details, I need to join these two tables. I need to eliminate the rows any orderheaderid if profit is zero for any given dat, but I should include rows if any orderHeader is  Null. 

    I have tried CTE and Subquery method to get expected output, but these two queries are not optimised one. Could anyone please do let me know how can I handle this scenario with much better performance. I need to do this task on million records tables each.

    Thank you

    Sangeeth

  • March 8, 2019 at 7:21 am

    #2023939

    I think this is an equivalent query
    ;WITH cte as
    (
        SELECT tkt.OrderHeader,a.*,sum(value) OVER (partition by tkt.orderheader,date) as Profit
          FROM dbo.[OrderDetails] a
          LEFT JOIN OrderHeader tkt ON tkt.OrderDetailID = a.OrderDetailID
    )
    SELECT a.OrderHeader,a.Date,a.Value,a.ProfitCategory,a.OrderDetailID
      FROM cte a
     WHERE Profit <> 0
     ORDER BY 1
    If you have millions of rows on each you should have the correct indexes on the tables to get better performance.

    I'm just wondering about the design of your tables. Why do you have a column OrderDetailId on the OrderHeader table? Shouldn't the tables be designed so that table OrderDetail has column OrderHeaderId and table OrderHeader has no reference or OrderDetail? Why did you design the tables like that?

  • March 10, 2019 at 3:24 pm

    #2024045

    The tables are backwards on this one.  The detail table rows should have a reference to the header table, not the other way around.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • March 11, 2019 at 2:35 am

    #2024059

    Jonathan AC Roberts - Friday, March 8, 2019 7:21 AM

    I think this is an equivalent query
    ;WITH cte as
    (
        SELECT tkt.OrderHeader,a.*,sum(value) OVER (partition by tkt.orderheader,date) as Profit
          FROM dbo.[OrderDetails] a
          LEFT JOIN OrderHeader tkt ON tkt.OrderDetailID = a.OrderDetailID
    )
    SELECT a.OrderHeader,a.Date,a.Value,a.ProfitCategory,a.OrderDetailID
      FROM cte a
     WHERE Profit <> 0
     ORDER BY 1
    If you have millions of rows on each you should have the correct indexes on the tables to get better performance.

    I'm just wondering about the design of your tables. Why do you have a column OrderDetailId on the OrderHeader table? Shouldn't the tables be designed so that table OrderDetail has column OrderHeaderId and table OrderHeader has no reference or OrderDetail? Why did you design the tables like that?

    Hi Jonathon, 

    I am glad for your reply, this is not design of the tables, though similar set up, In order to create my requirment, I have created this dummy data and structure. I need to give to the Users only Views not Store procedure, my bad, I should have mentioned earlier.

    Thank you

  • March 11, 2019 at 2:38 am

    #2024060

    Jeff Moden - Sunday, March 10, 2019 3:24 PM

    The tables are backwards on this one.  The detail table rows should have a reference to the header table, not the other way around.

    Hi,

    Yes, the tables are backwards, its just dummy data and design I have created to let know the forum what I am trying to achieve. The current structure is very similar, thought they are not such straight forward. Order Header and Order Details.

    Thank you

  • March 11, 2019 at 6:13 am

    #2024071

    Sangeeth Raj - Monday, March 11, 2019 2:38 AM

    Jeff Moden - Sunday, March 10, 2019 3:24 PM

    The tables are backwards on this one.  The detail table rows should have a reference to the header table, not the other way around.

    Hi,

    Yes, the tables are backwards, its just dummy data and design I have created to let know the forum what I am trying to achieve. The current structure is very similar, thought they are not such straight forward. Order Header and Order Details.

    Thank you

    I certainly understand simplifying a problem but to reverse it is a bit insane.  It would be nice if you actually tried to get close.  Please see the article at the first link in my signature line below and follow the suggestions there for how to post.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

Viewing 6 posts - 1 through 5 (of 5 total)

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