CODEWITHSUNDEEP
sql
viejoEngineer
viejoEngineer

Reputation: 404

Is there an alternative to rewriting a self join?

I'm performance tuning a stored procedure and SQL Enlight is throwing a high threshold and guessing is because of the many self joins. Is there another way to rewrite self joins?

Here is the code I am using.

Thanks in advance.

    SELECT x.ClientName
    ,x.Category
    ,x.vchSSNumber
    ,x.dtDateOfBirth
    ,x.CYC0
    ,x.Total0
    ,Limit0 = CASE 
        WHEN x.category = 'IRA'
            THEN i0.mLimit
        WHEN x.category = 'Simple'
            THEN s0.mLimit
        ELSE NULL
        END
    ,Balance0 = CASE 
        WHEN x.category = 'IRA'
            THEN CASE 
                    WHEN i0.mLimit > 0
                        THEN i0.mLimit - x.Total0
                    END
        WHEN x.category = 'Simple'
            THEN CASE 
                    WHEN s0.mLimit > 0
                        THEN s0.mLimit - x.Total0
                    END
        ELSE NULL
        END
    ,x.CYC1
    ,x.PYC1
    ,x.Total1
    ,i1.mLimit AS Limit1
    ,Balance1 = CASE 
        WHEN i1.mLimit > 0
            THEN i1.mLimit - x.Total1
        END
    ,x.CYC2
    ,x.PYC2
    ,x.Total2
    ,i2.mLimit AS Limit2
    ,Balance2 = CASE 
        WHEN i2.mLimit > 2
            THEN i2.mLimit - x.Total2
        END
    ,Over70AndHalf = CASE 
        WHEN x.dtDateOfBirth < @year70
            THEN 1
        ELSE 0
        END
FROM ClientInfo x
--Only get contribution limits for IRA Category. Have to get for each year
--IF DOB is NULL, Assume it is today. This makes them under 50 yrs old
LEFT JOIN dbo.IRAContributionLimits i0 WITH (NOLOCK) ON @year0 - DATEPART(yy, ISNULL(x.dtDateOfBirth, @date)) BETWEEN i0.iMinAge
        AND i0.iMaxAge
    AND i0.iYear = @year0
    AND i0.vchCategory = x.category
    AND x.category = 'IRA'
LEFT JOIN dbo.IRAContributionLimits i1 WITH (NOLOCK) ON @year1 - DATEPART(yy, ISNULL(x.dtDateOfBirth, @date)) BETWEEN i1.iMinAge
        AND i1.iMaxAge
    AND i1.iYear = @year1
    AND i1.vchCategory = x.category
    AND x.category = 'IRA'
LEFT JOIN dbo.IRAContributionLimits i2 WITH (NOLOCK) ON @year2 - DATEPART(yy, ISNULL(x.dtDateOfBirth, @date)) BETWEEN i2.iMinAge
        AND i2.iMaxAge
    AND i2.iYear = @year2
    AND i2.vchCategory = x.category
    AND x.category = 'IRA'
LEFT JOIN dbo.IRAContributionLimits s0 WITH (NOLOCK) ON @year0 - DATEPART(yy, ISNULL(x.dtDateOfBirth, @date)) BETWEEN s0.iMinAge
        AND s0.iMaxAge
    AND s0.iYear = @year0
    AND s0.vchCategory = x.category
    AND x.category = 'Simple'
ORDER BY x.ClientName
    ,x.category
    ,x.vchSSNumber;

Upvotes: 0

Views: 373

Answers (1)

Olivier De Meulder
Olivier De Meulder

Reputation: 2501

In my experience, self joins are not necessarily cause for performance problems.

Looking at your query, or any query with performance issues, the first place I would look is your indices.

Does your dbo.IRAContributionLimits table have an index on iMinAge, iMaxAge, iYear and vchCategory?

Does your ClientInfo table have an index on dtDateOfBirth and category?

Upvotes: 3

Related Questions