Best practice

• sampathsoft

  SSC Eights!

  Points: 855

  More actions

  February 1, 2012 at 9:45 pm

  #252291

  I see query somebody use like below. So let me know this method (joinig tables/ outer join) is good for performance or any other way to do query .

  Thanks

  SELECT Territory.ParentMasterGroupValue as Center, Territory.ParentMasterGroupValueDescription as CenterDescription,

  SalesInvoiceHeader.LocationCode as Location, SalesInvoiceHeader.ExecutiveCode as RepCode, Executive.ExecutiveName as SalesRepName,

  SalesInvoiceHeader.InvoiceDate, UserProfileDetails.UserProfile as UserProfileCode, UserProfileDetails.UserProfileName ,

  B.TotalInvoiceValue AS TotalInvoiceValue FROM RD.SalesInvoiceHeader INNER JOIN RD.Executive ON RD.Executive.BusinessUnit=RD.SalesInvoiceHeader.BusinessUnit

  AND RD.Executive.ExecutiveCode=RD.SalesInvoiceHeader.ExecutiveCode INNER JOIN XA.MasterDefinitionValue as Territory ON

  Territory.BusinessUnit=SalesInvoiceHeader.BusinessUnit AND Territory.MasterGroup='TETY' AND Territory.MasterGroupValue=

  SalesInvoiceHeader.TerritoryCode OUTER APPLY (SELECT TOP 1 ExecutiveUserProfile.UserProfile, UserProfile.UserProfileName FROM

  RD.ExecutiveUserProfile LEFT OUTER JOIN RD.UserProfile ON ExecutiveUserProfile.BusinessUnit=UserProfile.BusinessUnit AND

  ExecutiveUserProfile.UserProfile=UserProfile.UserProfile WHERE ExecutiveUserProfile.BusinessUnit=Executive.BusinessUnit AND

  ExecutiveUserProfile.ExecutiveCode=Executive.ExecutiveCode AND ExecutiveUserProfile.StartDate <= SalesInvoiceHeader.InvoiceDate AND

  (ExecutiveUserProfile.EndDate >= SalesInvoiceHeader.InvoiceDate OR ExecutiveUserProfile.EndDate IS NULL)

  ORDER BY ExecutiveUserProfile.StartDate DESC) as UserProfileDetails LEFT OUTER JOIN

  (SELECT SUM(TotalInvoiceValue) AS TotalInvoiceValue, BusinessUnit, ExecutiveCode, LocationCode FROM

  RD.SalesInvoiceHeader WHERE (Status = '1') AND (InvoiceDate >= '2/2/2011 12:00:00 AM') AND (InvoiceDate <= '2/2/2012 12:00:00 AM') AND

  (BusinessUnit='HEMA') GROUP BY ExecutiveCode, BusinessUnit, LocationCode ) AS B On RD.SalesInvoiceHeader.BusinessUnit=B.BusinessUnit AND

  RD.SalesInvoiceHeader.ExecutiveCode = B.ExecutiveCode AND RD.SalesInvoiceHeader.LocationCode = B.LocationCode LEFT OUTER JOIN

  ( SELECT RD.LoadCycleInvoices.ExecutiveCode, COUNT(RD.LoadCycleInvoices.InvoiceNo) AS DeliveredInvoice,

  SUM(RD.SalesInvoiceHeader.TotalInvoiceValue) AS TotalDelInvoiceValue, RD.SalesInvoiceHeader.LocationCode, RD.SalesInvoiceHeader.BusinessUnit

  FROM RD.LoadCycleInvoices INNER JOIN RD.SalesInvoiceHeader ON RD.LoadCycleInvoices.TerritoryCode = RD.SalesInvoiceHeader.TerritoryCode

  AND RD.LoadCycleInvoices.InvoiceNo = RD.SalesInvoiceHeader.InvoiceNo AND RD.LoadCycleInvoices.BusinessUnit = RD.SalesInvoiceHeader.BusinessUnit

  WHERE (RD.LoadCycleInvoices.DeliveredDate BETWEEN '01/27/2011' AND '01/27/2012') GROUP BY RD.LoadCycleInvoices.ExecutiveCode,

  RD.SalesInvoiceHeader.LocationCode, RD.SalesInvoiceHeader.BusinessUnit, RD.LoadCycleInvoices.DeliveredDate ) AS C On

  RD.SalesInvoiceHeader.BusinessUnit= C.BusinessUnit AND RD.SalesInvoiceHeader.ExecutiveCode = C.ExecutiveCode AND

  RD.SalesInvoiceHeader.LocationCode = C.LocationCode WHERE RD.SalesInvoiceHeader.Businessunit='HEMA'

  AND RD.SalesInvoiceHeader.InvoiceDate >= '2/2/2011 12:00:00 AM' AND RD.SalesInvoiceHeader.InvoiceDate <= '2/2/2012 12:00:00 AM'

• Paul White

  SSC Guru

  Points: 150468

  More actions

  February 2, 2012 at 2:36 am

  #1441627

  Properly formatted, and with a few minor edits, it looks like a reasonable query to me:

  SELECT

  Center = Territory.ParentMasterGroupValue,

  CenterDescription = Territory.ParentMasterGroupValueDescription,

  Location = SalesInvoiceHeader.LocationCode,

  RepCode = SalesInvoiceHeader.ExecutiveCode,

  SalesRepName = Executive.ExecutiveName,

  SalesInvoiceHeader.InvoiceDate,

  UserProfileCode = UserProfileDetails.UserProfile,

  UserProfileDetails.UserProfileName,

  TotalInvoiceValue = B.TotalInvoiceValue

  FROM RD.SalesInvoiceHeader

  JOIN RD.Executive ON

  RD.Executive.BusinessUnit = RD.SalesInvoiceHeader.BusinessUnit

  AND RD.Executive.ExecutiveCode = RD.SalesInvoiceHeader.ExecutiveCode

  JOIN XA.MasterDefinitionValue AS Territory ON

  Territory.BusinessUnit = SalesInvoiceHeader.BusinessUnit

  AND Territory.MasterGroup = 'TETY'

  AND Territory.MasterGroupValue = SalesInvoiceHeader.TerritoryCode

  OUTER APPLY

  (

  SELECT TOP (1)

  ExecutiveUserProfile.UserProfile,

  UserProfile.UserProfileName

  FROM RD.ExecutiveUserProfile

  LEFT JOIN RD.UserProfile ON

  ExecutiveUserProfile.BusinessUnit = UserProfile.BusinessUnit

  AND ExecutiveUserProfile.UserProfile = UserProfile.UserProfile

  WHERE

  ExecutiveUserProfile.BusinessUnit = Executive.BusinessUnit

  AND ExecutiveUserProfile.ExecutiveCode = Executive.ExecutiveCode

  AND ExecutiveUserProfile.StartDate <= SalesInvoiceHeader.InvoiceDate

  AND (

  ExecutiveUserProfile.EndDate >= SalesInvoiceHeader.InvoiceDate

  OR ExecutiveUserProfile.EndDate IS NULL

  )

  ORDER BY

  ExecutiveUserProfile.StartDate DESC

  ) AS UserProfileDetails

  LEFT JOIN

  (

  SELECT

  SUM(TotalInvoiceValue) AS TotalInvoiceValue,

  BusinessUnit,

  ExecutiveCode,

  LocationCode

  FROM RD.SalesInvoiceHeader

  WHERE

  [Status] = '1'

  AND InvoiceDate BETWEEN '2011-02-02' AND '2012-02-02'

  AND BusinessUnit = 'HEMA'

  GROUP BY

  ExecutiveCode,

  BusinessUnit,

  LocationCode

  ) AS B ON

  RD.SalesInvoiceHeader.BusinessUnit = B.BusinessUnit

  AND RD.SalesInvoiceHeader.ExecutiveCode = B.ExecutiveCode

  AND RD.SalesInvoiceHeader.LocationCode = B.LocationCode

  LEFT JOIN

  (

  SELECT

  RD.LoadCycleInvoices.ExecutiveCode,

  COUNT(RD.LoadCycleInvoices.InvoiceNo) AS DeliveredInvoice,

  SUM(RD.SalesInvoiceHeader.TotalInvoiceValue) AS TotalDelInvoiceValue,

  RD.SalesInvoiceHeader.LocationCode,

  RD.SalesInvoiceHeader.BusinessUnit

  FROM RD.LoadCycleInvoices

  JOIN RD.SalesInvoiceHeader ON

  RD.LoadCycleInvoices.TerritoryCode = RD.SalesInvoiceHeader.TerritoryCode

  AND RD.LoadCycleInvoices.InvoiceNo = RD.SalesInvoiceHeader.InvoiceNo

  AND RD.LoadCycleInvoices.BusinessUnit = RD.SalesInvoiceHeader.BusinessUnit

  WHERE

  RD.LoadCycleInvoices.DeliveredDate BETWEEN '2011-01-27' AND '2012-01-27'

  GROUP BY

  RD.LoadCycleInvoices.ExecutiveCode,

  RD.SalesInvoiceHeader.LocationCode,

  RD.SalesInvoiceHeader.BusinessUnit,

  RD.LoadCycleInvoices.DeliveredDate

  ) AS C ON

  RD.SalesInvoiceHeader.BusinessUnit = C.BusinessUnit

  AND RD.SalesInvoiceHeader.ExecutiveCode = C.ExecutiveCode

  AND RD.SalesInvoiceHeader.LocationCode = C.LocationCode

  WHERE

  RD.SalesInvoiceHeader.Businessunit = 'HEMA'

  AND RD.SalesInvoiceHeader.InvoiceDate BETWEEN '2011-02-02' AND '2012-02-02';

  Paul White
  All articles available on SQL.kiwi
  @SQL_Kiwi

• Jeff Moden

  SSC Guru

  Points: 1004432

  More actions

  February 5, 2012 at 3:47 pm

  #1442863

  sampathsoft (2/1/2012)

  ---

  So let me know this method (joinig tables/ outer join) is good for performance ...

  Heh... "It Depends". How long does it currently take to run and how big are the tables? If you really want to know, do and post the things listed at the 2nd link in my signature line below.

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