2 questions

Question

2 questions

ramrajan

SSCommitted

Points: 1737
More actions
June 19, 2014 at 7:31 am

#308275

sdf

Viewing 8 posts - 1 through 7 (of 7 total)

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

ramrajan (6/19/2014)
Question 1 : can you rewrite the same with parantheses to understand how it is executing
INNER JOIN dbo.controllersystem cs WITH (NOLOCK)ON sl.SubLocationId IS NULL
AND cs.ParentId = fm.FacilityId
AND cs.ParentLevelId = 5
OR sl.SubLocationId IS NOT NULL
AND cs.ParentId = sl.FacilityId
AND cs.ParentLevelId = 6
Question 2 : Why we need two join below can we rewrite with single join is that possible.
LEFT OUTER JOIN dbo.subsystem subsys WITH (NOLOCK)ON subsys.ParentID = cs.SystemId
AND subsys.Leveltype = 8
AND subsys.Parentleveltype = 7
INNER JOIN dbo.subsystem comp WITH (NOLOCK)ON subsys.SubsystemId IS NULL
AND comp.ParentID = cs.SystemId
Question 3 :

INNER JOIN dbo.controllersystem cs

(ON sl.SubLocationId IS NULL

AND cs.ParentId = fm.FacilityId

AND cs.ParentLevelId = 5

AND cs.ParentId = sl.FacilityId

AND cs.ParentLevelId = 6)

OR sl.SubLocationId IS NOT NULL

The answer to #2 is: MAYBE

Whether a single join or multiple joins to that table is necessary depends on the requirements and the data that needs to be returned. Notice that the joins are different.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

djj SSCoach Points: 18831 More actions · Answer 2

ramrajan (6/19/2014)
Question 1 : can you rewrite the same with parantheses to understand how it is executing
INNER JOIN dbo.controllersystem cs WITH (NOLOCK)ON sl.SubLocationId IS NULL
AND cs.ParentId = fm.FacilityId
AND cs.ParentLevelId = 5
OR sl.SubLocationId IS NOT NULL
AND cs.ParentId = sl.FacilityId
AND cs.ParentLevelId = 6
Question 2 : Why we need two join below can we rewrite with single join is that possible.
LEFT OUTER JOIN dbo.subsystem subsys WITH (NOLOCK)ON subsys.ParentID = cs.SystemId
AND subsys.Leveltype = 8
AND subsys.Parentleveltype = 7
INNER JOIN dbo.subsystem comp WITH (NOLOCK)ON subsys.SubsystemId IS NULL
AND comp.ParentID = cs.SystemId
Question 3 :

Make sure you know what "NOLOCK" does. It can lead to problem data.

ramrajan SSCommitted Points: 1737 More actions · Answer 3

still waiting reply for the below question

Question 2 : Why we need two join below can we rewrite with single join is that possible.

LEFT OUTER JOIN dbo.subsystem subsys WITH (NOLOCK)ON subsys.ParentID = cs.SystemId

AND subsys.Leveltype = 8

AND subsys.Parentleveltype = 7

INNER JOIN dbo.subsystem comp WITH (NOLOCK)ON subsys.SubsystemId IS NULL

AND comp.ParentID = cs.SystemId

djj SSCoach Points: 18831 More actions · Answer 4

As SQLRNNR said, maybe.

We would need to see the reset of the query.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 5

ramrajan (6/19/2014)
still waiting reply for the below question
Question 2 : Why we need two join below can we rewrite with single join is that possible.
LEFT OUTER JOIN dbo.subsystem subsys WITH (NOLOCK)ON subsys.ParentID = cs.SystemId
AND subsys.Leveltype = 8
AND subsys.Parentleveltype = 7
INNER JOIN dbo.subsystem comp WITH (NOLOCK)ON subsys.SubsystemId IS NULL
AND comp.ParentID = cs.SystemId

Look at the two join types and conditions being used. Based on that information alone, the answer would be no.

But we can't provide a real answer because we don't have all of the information.

Why are you focusing on rewriting it with a single join?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

ramrajan SSCommitted Points: 1737 More actions · Answer 6

cant it can be rewritten without looking at the data and resultset

SQLRNNR SSC Guru Points: 281334 More actions · Answer 7

ramrajan (6/19/2014)
cant it can be rewritten without looking at the data and resultset

Sure go ahead. But you are missing the point.

Those two joins are DIFFERENT. The reason they are different is because they were probably set to serve different purposes. We can't know if that is true or not without seeing your actual queries and sample data results.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events