SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


T-Sql Query


T-Sql Query

Author
Message
ramana3327
ramana3327
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

Group: General Forum Members
Points: 4605 Visits: 2083
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?
GilaMonster
GilaMonster
SSC Guru
SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)

Group: General Forum Members
Points: 218261 Visits: 46278
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
TheSQLGuru
SSC-Dedicated
SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)

Group: General Forum Members
Points: 31412 Visits: 8670
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
SQLRNNR
SSC Guru
SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)

Group: General Forum Members
Points: 65129 Visits: 18570
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

GilaMonster
GilaMonster
SSC Guru
SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)

Group: General Forum Members
Points: 218261 Visits: 46278
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
Ed Wagner
SSC-Forever
SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)

Group: General Forum Members
Points: 46625 Visits: 10844
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
TheSQLGuru
SSC-Dedicated
SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)

Group: General Forum Members
Points: 31412 Visits: 8670
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
ramana3327
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

Group: General Forum Members
Points: 4605 Visits: 2083
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
Jeff Moden
SSC Guru
SSC Guru (209K reputation)SSC Guru (209K reputation)SSC Guru (209K reputation)SSC Guru (209K reputation)SSC Guru (209K reputation)SSC Guru (209K reputation)SSC Guru (209K reputation)SSC Guru (209K reputation)

Group: General Forum Members
Points: 209631 Visits: 41973
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
ramana3327
ramana3327
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

Group: General Forum Members
Points: 4605 Visits: 2083
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search