How to better join my queries and prevent usage of functions

• team.bernard

  SSC Veteran

  Points: 270

  More actions

  July 20, 2017 at 1:29 pm

  #343651

  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

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  July 20, 2017 at 1:55 pm

  #1951964

  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.

• ScottPletcher

  SSC Guru

  Points: 100950

  More actions

  July 21, 2017 at 7:14 am

  #1952072

  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) A socialist is someone who will give you the shirt off *someone else's* back.

• team.bernard

  SSC Veteran

  Points: 270

  More actions

  July 21, 2017 at 11:34 am

  #1952163

  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

• Jeff Moden

  SSC Guru

  Points: 1003894

  More actions

  July 21, 2017 at 7:18 pm

  #1952227

  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)
  

• drew.allen

  SSC Guru

  Points: 76948

  More actions

  July 24, 2017 at 9:44 am

  #1952408

  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