Best practice

Question

Post reply

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'

Viewing 3 posts - 1 through 2 (of 2 total)

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

Paul White SSC Guru Points: 150457 More actions · Answer 1

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
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 2

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)
Intro to Tally Tables and Functions