Post reply

subquery's optimisation

  • November 30, 2015 at 9:26 am

    #302554

    Hi , I need some help to kno how can optimize the follow queries ?

    SELECTpc.Name

    ,ppsc.Name

    , (SELECT COUNT(*) FROM Production.Product pp

    WHERE pp.ProductSubcategoryID = ppsc.ProductSubcategoryID) as NumberOfProducts

    FROMProduction.ProductCategory pc

    INNER JOINProduction.ProductSubCategory ppsc

    ONpc.ProductcategoryID = ppsc.ProductcategoryID

    AND

    SELECTpw.ProductID

    , pp.Name

    , ppsc.Name

    , SUM(pw.OrderQty) as Quantity

    FROMProduction.Product pp

    LEFT JOINProduction.ProductSubCategory ppsc

    ONpp.ProductSubcategoryID = ppsc.ProductSubcategoryID

    INNER JOINProduction.WorkOrder pw

    ONpw.ProductID = pp.ProductID

    WHERE pw.DueDate = '2003-06-03 00:00:00.000'

    GROUP BY pw.ProductID, pp.Name, ppsc.Name

  • November 30, 2015 at 9:32 am

    #1842925

    Table definitions, index definitions and execution plan (as a .sqlplan file) please.

    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

  • November 30, 2015 at 10:02 am

    #1842939

    Hi,

    Thanks a lot of your assware. But you are ask me for that information?

  • November 30, 2015 at 10:09 am

    #1842941

    http://sqlblog.com/blogs/rob_farley/archive/2011/04/13/the-power-of-t-sql-s-apply-operator.aspx

    Catch-all queries done right [/url]
    Gail Shaw's Performance Blog[/url]

  • November 30, 2015 at 10:48 am

    #1842952

    pedro.j.manuel (11/30/2015)

    Hi,

    Thanks a lot of your assware. But you are ask me for that information?

    Assware?


    Alvin Ramard
    Memphis PASS Chapter[/url]

    All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

    For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • November 30, 2015 at 10:58 am

    #1842954

    It was answer...

  • November 30, 2015 at 1:54 pm

    #1842998

    Yes, I am asking you for that information. We can't help you tune a query just by seeing the T-SQL of it.

    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

  • November 30, 2015 at 3:07 pm

    #1843032

    Hi,

    You can use the query in Adventureworks2012.

    Thanks,

    Pedro

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

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