How to better join my queries and prevent usage of functions

  • Hello,

    I have this structure and these datas :

    For now, I use sub-queries, like this :
    Select dem2.Id, (SELECT top 1 datetime from demand whereDepartment='Logisitc' and Action = 'Shipped' and comment = 'B' and Id = dem2.Id ORDER BYDatetime desc),(SELECT Actor from service_call_env where Id = dem2.Id),(SELECT top 1 datetime from demand whereDepartment='Sale' and Action = 'Email' and Id = dem2.Id ORDER BYdatetime desc),(ISNULL((SELECT top 1 ISNULL(CAST(demand.Datetime as varchar(30)),NULL) from demand where Department = 'Update' and Id = dem2.Id),NULL) +ISNULL((SELECT top 1 ISNULL(demand.Comment,NULL) from demand where Department = 'Update' and Id = dem2.Id),NULL)),(SELECT top 1 datetime from demand whereDepartment='Admin' and comment IS NULL and Id = dem2.Id ORDER BYdatetime desc),(SELECT top 1 dem1.datetime from demand whereDepartment='Sale' and Action = 'NewAddress' and Id = dem2.Id ORDER BYdatetime desc),(SELECT top 1 search_name from demand dem1inner join person on person.person_id = dem1.Userwheredem1.Department='Sale' and dem1.Action = 'NewAddress' and dem1.Id = dem2.Id ORDER BYdem1.datetime desc) from demand dem1 inner join (SELECT Id FROM demand) AS dem2 on dem1.Id = dem2.Id

    The inner join is weird but needed for my usage, because this query is an extract of a bigger one.

    I try like this :
    Select dem2.Id,  dem3.datetime from demand dem1 inner join (SELECT Id FROM demand) AS dem2 on dem1.Id = dem2.Id
    left join (SELECT top 1 datetime from demand where
    Department='Logisitc' and Action = 'Shipped' and comment = 'B' ORDER BY
    Datetime desc) as dem3 on dem3.Id = dem2.Id  

    Could you help me, because I try some left join but my query take a long time to execute ?

    Regards

    The inner join is weird but needed for my usage, because this query is an extract of a bigger one.Could you help me, because I try some left join but my query take a long time to execute ?Regards

  • You aren't going to get much help with what you have posted.  What we need is the DDL (CREATE TABLE statement) for the table(s) involved, sample data for the table(s) (as INSERT INTO statements), and the expected output of the query you are trying to write based on the sample data.  I will stress, sample data not production data.  Also we need enough that your problem domain is properly represented but not 1000's of rows of data.

    Use an empty sandbox data to write and test the scripts so that when you post them you know that they will work.

  • I'll give it a guess.  Typically now you use OUTER APPLY to do the type of lookup  you're trying to do.  You also definitely don't need to join the table back to itself on its own id ("dem2" alias in the original query).  Instead something like this:


    Select dem1.Id, dem3.datetime
    from demand dem1
    outer apply (
      SELECT top (1) dem2.datetime
      from demand dem2
      where dem2.Id = dem1.Id and dem2.Department='Logisitc' and dem2.Action = 'Shipped' and dem2.comment = 'B'
      ORDER BY dem2.Datetime desc
    ) as dem3

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • Hello,

    With outer apply, the execution time of my query reduce by 90%.

    I still have an issue about one subquery, I'll add it monday, because i'm not at work atm.

    Regards

  • ScottPletcher - Friday, July 21, 2017 7:14 AM

    I'll give it a guess.  Typically now you use OUTER APPLY to do the type of lookup  you're trying to do.  You also definitely don't need to join the table back to itself on its own id ("dem2" alias in the original query).  Instead something like this:


    Select dem1.Id, dem3.datetime
    from demand dem1
    outer apply (
      SELECT top (1) dem2.datetime
      from demand dem2
      where dem2.Id = dem1.Id and dem2.Department='Logisitc' and dem2.Action = 'Shipped' and dem2.comment = 'B'
      ORDER BY dem2.Datetime desc
    ) as dem3

    In the old days, you could achieve the same effect with a correlated sub-query in the Select List.

    --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)

  • ScottPletcher - Friday, July 21, 2017 7:14 AM

    I'll give it a guess.  Typically now you use OUTER APPLY to do the type of lookup  you're trying to do.  You also definitely don't need to join the table back to itself on its own id ("dem2" alias in the original query).  Instead something like this:


    Select dem1.Id, dem3.datetime
    from demand dem1
    outer apply (
      SELECT top (1) dem2.datetime
      from demand dem2
      where dem2.Id = dem1.Id and dem2.Department='Logisitc' and dem2.Action = 'Shipped' and dem2.comment = 'B'
      ORDER BY dem2.Datetime desc
    ) as dem3

    While using an OUTER APPLY may be typical, it may not be the best performing.  (Of course, it's very difficult to test with no sample data.)  Here is an approach using a windowed function.

    SELECT dem1.ID,
        MAX(CASE WHEN dem1.Department = 'Logistc' AND dem1.[Action] = 'Shipped' AND dem1.Comment = 'B' THEN dem1.[Datetime] END)
            OVER(PARTITION BY dem1.ID) AS [datetime]
    FROM dem1

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

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

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