Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

T-Sql Query Expand / Collapse
Author
Message
Posted Tuesday, May 27, 2014 2:15 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 4:51 PM
Points: 550, Visits: 665
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?

Post #1574692
Posted Tuesday, May 27, 2014 2:24 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:13 AM
Points: 39,976, Visits: 36,336
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 2008, MVP
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

Post #1574696
Posted Tuesday, May 27, 2014 9:52 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 6:05 AM
Points: 4,364, Visits: 6,206
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 at GMail
Post #1574856
Posted Tuesday, May 27, 2014 10:35 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 12:05 AM
Points: 17,710, Visits: 15,580
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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1574871
Posted Tuesday, May 27, 2014 10:40 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:13 AM
Points: 39,976, Visits: 36,336
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 2008, MVP
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

Post #1574874
Posted Tuesday, May 27, 2014 12:19 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 6:48 AM
Points: 4,046, Visits: 3,482
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
Post #1574910
Posted Tuesday, May 27, 2014 7:47 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 6:05 AM
Points: 4,364, Visits: 6,206
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?!?


Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #1575025
Posted Thursday, May 29, 2014 7:19 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 4:51 PM
Points: 550, Visits: 665
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.

Post #1575565
Posted Thursday, May 29, 2014 10:40 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:50 AM
Points: 35,263, Visits: 31,751
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1575689
Posted Thursday, May 29, 2014 11:07 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 4:51 PM
Points: 550, Visits: 665
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
Post #1575704
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse