How to better join my queries and prevent usage of functions

Question

How to better join my queries and prevent usage of functions

team.bernard

Old Hand

Points: 300
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

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

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

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: 101120 More actions · Answer 2

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".

team.bernard Old Hand Points: 300 More actions · Answer 3

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: 1004406 More actions · Answer 4

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: 76996 More actions · Answer 5

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