T-Sql Query

Question

Post reply

T-Sql Query

ramana3327

SSCoach

Points: 19722
More actions
May 27, 2014 at 2:15 am

#396817

Hi ,
I am working with a query. I need to change the query
select distinct top(10) RV.Id, RV.PurchaseDate
FROM dbo.PurchaseHeader PH (nolock)
JOIN ( SELECT DISTINCT R.ID FROM TableR R WITH (NOLOCK)
JOIN Tableb Q WITH (NOLOCK) ON R.ID = Q.ID
WHERE R.Role ='Test' OR (R.Status = 'Active' AND
( Q.ContactID = 15 OR @Orgid is NULL )
AND (Q.EndDate IS NULL OR Q.EndDate >= GetDate()) )) Tab ON Tab.ID = PH.ID
join TableR AS RV ON H.ID = RV.Id
LEFT OUTER JOIN Tableb QM (nolock) ON PH.ID = QM.ID
LEFT OUTER JOIN Tableb QM1 (nolock) ON QM1.ID = PH.ID AND QM1.Name = PH.UserName
LEFT OUTER JOIN dbo.Pricing PD (nolock) ON RV.RId = PD.Id
Left Outer Join --- On --
Left Outer Join---
I need to avoid some of the Left Outer joins. How to find the most efficient way. Is there any possibility to use derived tables or temp tables and do join?

Viewing 15 posts - 1 through 15 (of 16 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Why do you want to avoid the left joins?

Why are you using hints which allow incorrect results?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 2

You also have the dreaded IS NULL OR construct (Q.ContactID = 15 OR @Orgid is NULL). Go to Gail's site, http://sqlinthewild.co.za/, and click the top Popular Link, Catch-All Queries, and read up.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

I too am curious why you are trying to eliminate the left joins.

I am also curious to see the full query and execution plan.

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

TheSQLGuru (5/27/2014)
You also have the dreaded IS NULL OR construct

How did I miss that....

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Ed Wagner SSC Guru Points: 287026 More actions · Answer 5

GilaMonster (5/27/2014)
TheSQLGuru (5/27/2014)
You also have the dreaded IS NULL OR construct
How did I miss that....

It was probably all the NOLOCKs that tend to cause dizziness and the occasional blackout.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 6

GilaMonster (5/27/2014)
TheSQLGuru (5/27/2014)
You also have the dreaded IS NULL OR construct
How did I miss that....

Because you have seen it so much you just don't see it any more?!? :hehe:

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

ramana3327 SSCoach Points: 19722 More actions · Answer 7

I am not sure why did they use no lock hint. They use without blocking. Might be they don't need accurate data. The dev lead think that we can do some changes and bring some more optimization to the query.

They want to me create the a temptable or table variable for the main query output and then try to avoid the left outer join.

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 8

I've been watching your posts for several days now. You good folks have the right idea to try to optimize all of the queris you've posted but I think you need some professional help. You've wanted to change some queries to APPLYs (and didn't know how nore that such a thing probably wouldn't help) and now they want you to try to get rid of LEFT OUTER JOINs based on some mistaken assumption that OUTER JOINs are automatically a performance problem. They might be, in this case, but they also might not be. The problem is that we don't have your data and we just can't tell and we certainly can't tell which part of the query is the performance culprit.

Bearing no malice to you or the folks you work with, I strongly recommend that you folks get a tuning expert on temporary payroll. Gail or Kevin would make excellent choices in this area.

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

ramana3327 SSCoach Points: 19722 More actions · Answer 9

Actually I tried with Outer Apply but it increased the execution time. Actually that SP is not taking that much time from SSMS but from the application it is little longer.

The dev is saying that we can still implement that query by avoiding that joins. I am in a way to find that

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 10

ramana3327 (5/29/2014)
Actually I tried with Outer Apply but it increased the execution time. Actually that SP is not taking that much time from SSMS but from the application it is little longer.
The dev is saying that we can still implement that query by avoiding that joins. I am in a way to find that

That's all a part of the reason why I'm suggesting that the company get some professional help.

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 11

ramana3327 (5/29/2014)
The dev is saying that we can still implement that query by avoiding that joins.

So go to that dev and ask him to please show you the magic join without a join that he knows.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 12

ramana3327 (5/29/2014)
They want to me create the a temptable or table variable for the main query output and then try to avoid the left outer join.

They want you to add additional overhead and still have to use a left join to the temp table in order to degrade performance further? Sounds legit.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 13

TheSQLGuru (5/27/2014)
GilaMonster (5/27/2014)
TheSQLGuru (5/27/2014)
You also have the dreaded IS NULL OR construct
How did I miss that....
Because you have seen it so much you just don't see it any more?!? :hehe:

Pretty much. On the other hand, I look like Scotty when I add two words and two brackets to a query and suddenly it's running in a fraction of the time. 😀

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ramana3327 SSCoach Points: 19722 More actions · Answer 14

Thanks for you replies.

I am thinking about applying union and exists. I have to see the columns & data types. If it works, I will check the time difference.

Actually his plan is To use a table variable to avoid LEFT OUTER JOIN.

What I understand is that, we have to put all the values from the left table to the table variable, first. Then we have to UPDATE the table variable with the right table values. Then select from the table variable.

This is his his plan.