subquery's optimisation

• pedro.j.manuel

  Mr or Mrs. 500

  Points: 598

  More actions

  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

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  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

• pedro.j.manuel

  Mr or Mrs. 500

  Points: 598

  More actions

  November 30, 2015 at 10:02 am

  #1842939

  Hi,

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

• MadAdmin

  SSChampion

  Points: 11261

  More actions

  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]

• Alvin Ramard

  SSC-Forever

  Points: 41190

  More actions

  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]

• pedro.j.manuel

  Mr or Mrs. 500

  Points: 598

  More actions

  November 30, 2015 at 10:58 am

  #1842954

  It was answer...

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  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

• pedro.j.manuel

  Mr or Mrs. 500

  Points: 598

  More actions

  November 30, 2015 at 3:07 pm

  #1843032

  Hi,

  You can use the query in Adventureworks2012.

  Thanks,

  Pedro