T-Sql Query

  • 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?

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

  • 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

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

  • 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

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

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

  • 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

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

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

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

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