4
\$\begingroup\$

I have a query below using CTE. I am wondering if there are some approach other than this. So I am collecting some advise to refactor it. Or does the query look good even though I am using this IN (SELECT InvariantLanguageCultureCode FROM y) statement many times in the query?

DECLARE  @ReportingInstanceID INT

 SET @ReportingInstanceID=34
 ;WITH x AS (
        SELECT  [dbo].[tenant_Reporting_Instance].UserID
        FROM    [dbo].[tenant_Reporting_Instance]
        WHERE   tenant_Reporting_Instance.ReportingInstanceID = @ReportingInstanceID
        )
        ,y AS ( SELECT  [TRCCore].[dbo].[pr_Language].InvariantLanguageCultureCode
        FROM    [TRCCore].[dbo].[pr_Language]
                JOIN [dbo].[tenant_User_Setting] ON tenant_User_Setting.LanguageID = pr_Language.LanguageID
        WHERE   tenant_User_Setting.UserID IN (SELECT UserID FROM x))
        ,Default_User_Settings_CTE ( RoundingTypeInvariantCode, AgingDateValue, CurrencyLabel, JobZoneLinkTypeLabel, MatchPeriodLabel, PayFrequencyLabel, PayTypeLabel )
                  AS ( SELECT   r.RoundingTypeInvariantCode ,
                                a.AgingDateValue ,
                                cur.CurrencyLabel ,
                                jz.JobZoneLinkTypeLabel ,
                                m.MatchPeriodLabel ,
                                pf.PayFrequencyLabel ,
                                pt.PayTypeLabel
                       FROM     dbo.tenant_User_Setting s
                                JOIN TRCCore.dbo.pr_Rounding_Type r ON r.RoundingTypeID = s.RoundingTypeID
                                JOIN dbo.tenant_Aging_Date a ON a.AgingDateID = s.AgingDateID
                                JOIN TRCCore.dbo.translation_pr_Currency cur ON cur.CurrencyID = s.CurrencyID
                                                              AND cur.LanguageCultureCode IN (SELECT InvariantLanguageCultureCode FROM y)
                                JOIN dbo.translation_tenant_Job_Zone_Link_Type jz ON jz.JobZoneLinkTypeID = s.JobZoneLinkTypeID
                                                              AND jz.LanguageCultureCode IN (SELECT InvariantLanguageCultureCode FROM y)
                                JOIN dbo.tenant_Match_Period m ON m.MatchPeriodID = s.MatchPeriodID
                                JOIN dbo.translation_tenant_Pay_Frequency pf ON pf.PayFrequencyID = s.PayFrequencyID
                                                              AND pf.LanguageCultureCode IN (SELECT InvariantLanguageCultureCode FROM y)
                                JOIN dbo.translation_app_Pay_Type pt ON pt.PayTypeID = s.PayTypeID
                                                              AND pt.LanguageCultureCode IN (SELECT InvariantLanguageCultureCode FROM y)
                       WHERE    s.UserID IN (SELECT UserID FROM x)
                     )
        SELECT * FROM Default_User_Settings_CTE
\$\endgroup\$
1
\$\begingroup\$

Move the First and second Cte to temp table.

you don't have to refer ( SELECT InvariantLanguageCultureCode FROM y) more than once in join condition instead use it in one column then join that column with other tables.

Try this.

DECLARE @ReportingInstanceID INT

SET @ReportingInstanceID=34;

SELECT [TRCCore].[dbo].[pr_Language].InvariantLanguageCultureCode
INTO   #temp_InvariantLanguageCultureCode
FROM   [TRCCore].[dbo].[pr_Language]
       JOIN [dbo].[tenant_User_Setting]
         ON tenant_User_Setting.LanguageID = pr_Language.LanguageID
WHERE  tenant_User_Setting.UserID IN (SELECT [dbo].[tenant_Reporting_Instance].UserID
                                      FROM   [dbo].[tenant_Reporting_Instance]
                                      WHERE  tenant_Reporting_Instance.ReportingInstanceID = @ReportingInstanceID)

SELECT [dbo].[tenant_Reporting_Instance].UserID
INTO   #temp_UserID
FROM   [dbo].[tenant_Reporting_Instance]
WHERE  tenant_Reporting_Instance.ReportingInstanceID = @ReportingInstanceID

SELECT r.RoundingTypeInvariantCode,
       a.AgingDateValue,
       cur.CurrencyLabel,
       jz.JobZoneLinkTypeLabel,
       m.MatchPeriodLabel,
       pf.PayFrequencyLabel,
       pt.PayTypeLabel
FROM   dbo.tenant_User_Setting s
       JOIN TRCCore.dbo.pr_Rounding_Type r
         ON r.RoundingTypeID = s.RoundingTypeID
       JOIN dbo.tenant_Aging_Date a
         ON a.AgingDateID = s.AgingDateID
       JOIN TRCCore.dbo.translation_pr_Currency cur
         ON cur.CurrencyID = s.CurrencyID
            AND cur.LanguageCultureCode IN (SELECT InvariantLanguageCultureCode
                                            FROM   #temp_InvariantLanguageCultureCode)
       JOIN dbo.translation_tenant_Job_Zone_Link_Type jz
         ON jz.JobZoneLinkTypeID = s.JobZoneLinkTypeID
\$\endgroup\$
2
  • 2
    \$\begingroup\$ Why do you advocate a temp table instead of a CTE? \$\endgroup\$ Jan 5 '15 at 7:25
  • 1
    \$\begingroup\$ Temp tables can be faster if referenced multiple times. They are also easier to test while developing a query. \$\endgroup\$
    – jmoreno
    Jan 5 '15 at 7:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.