Блог

Главная - Вопросы по программированию - Оптимизация запроса с помощью нескольких обращений к одной и той же таблице

Оптимизация запроса с помощью нескольких обращений к одной и той же таблице

#sql-server #tsql

#sql-server #tsql

Вопрос:

Мы создаем представление следующим образом:

 create view [dca].[CodeDescriptions_VW]
as 
    select distinct 
        ek.EmpKey
        , RTRIM(CONVERT(varchar(60), abt.fdesc)) AS AboriginalTypeDescription
        , RTRIM(CONVERT(varchar(60), cma.fdesc)) AS CensusMetroAreaDescription
        , RTRIM(CONVERT(varchar(60), q.c_desc)) AS CobraCodeDescription
        , RTRIM(CONVERT(varchar(60), ct.fdesc)) AS ConsentTypeDescription
        , RTRIM(CONVERT(varchar(60), li.fdesc)) AS DirectOrIndirectDescription
        , RTRIM(CONVERT(varchar(60), dt.fdesc)) AS DisabilityTypeDescription
        , RTRIM(CONVERT(varchar(60), doca.fdesc)) AS DocumentADescription
    FROM  
        dbo.hrpersnl AS h 
    INNER JOIN 
        HRActions.dbo.EmployeeKey AS ek ON RTRIM(LTRIM(UPPER(h.p_empno))) = UPPER(ek.EmpNo) COLLATE SQL_Latin1_General_CP1_CI_AS 
                                        AND h.p_company = ek.Company COLLATE SQL_Latin1_General_CP1_CI_AS 
    LEFT OUTER JOIN 
        dbo.hrtables AS abt ON abt.ftable = ''AL'' AND RTRIM(h.p_abotype) = RTRIM(abt.code)
    LEFT OUTER JOIN 
        dbo.hrtables AS cma ON cma.ftable = ''CM'' AND RTRIM(h.p_cma) = RTRIM(cma.code) 
    LEFT OUTER JOIN 
        dbo.hrtables AS ct ON ct.ftable = ''CS'' AND RTRIM(h.p_constype) = RTRIM(ct.code)
    LEFT OUTER JOIN 
        dbo.hrtables AS li ON li.ftable = ''IN'' AND RTRIM(h.p_laborind) = RTRIM(li.code)
    LEFT OUTER JOIN 
        dbo.hrtables AS dt ON dt.ftable = ''DI'' AND RTRIM(h.p_distype) = RTRIM(dt.code)
    LEFT OUTER JOIN 
        dbo.hrtables AS doca ON doca.ftable = ''I2'' AND RTRIM(h.p_doca) = RTRIM(doca.code)

Мне интересно, есть ли более эффективный способ ссылаться на одну и ту же таблицу ( dbo.hratables ) несколько раз. Как есть, это очень дорогостоящее представление (на самом деле существует около 3 десятков различных «описаний», которые нам нужно запросить, я показал только несколько).

Комментарии:

1. Что происходит с условиями ВНУТРЕННЕГО соединения? Почему переключение параметров сортировки необходимо для сравнения ключей таблицы? Маловероятно, что будут использоваться индексы

2. @SteveC Две таблицы, участвующие в этом ВНУТРЕННЕМ СОЕДИНЕНИИ, взяты из разных баз данных, которые мы не контролируем. В зависимости от того, как пользователи настраивают эту базу данных (т.Е., С учетом регистра вкл / выкл), нам нужно выполнить сопоставление.

3. Все ваши условия объединения разные, т. Е. Вы Объединяете разные строки в таблице, поэтому маловероятно, что вы сможете это улучшить.

4. Это прискорбно, но так всегда бывает 🙂 Ну, можно попробовать создать индексированное представление для повторно сопоставленных столбцов из dbo.hrpersnl, а затем использовать это представление в этом представлении.

Ответ №1:

Хотя это и не идеально, возможно, вы могли бы создать встроенный TVF и использовать его для всех соединений.

Что-то вроде:

 CREATE FUNCTION dbo.HRDescription ( 
    @ftable VARCHAR(2), @code VARCHAR(50), @field VARCHAR(50)
)
RETURNS TABLE 
AS
RETURN 
(
    SELECT RTRIM( CONVERT( VARCHAR(60), 
        CASE
            WHEN @field = 'c_desc' THEN c_desc
            ELSE fdesc
        END ) ) AS [Value]
    FROM dbo.hrtables
    WHERE
        ftable = @ftable AND RTRIM( code ) = @code
)
GO

А затем измените свой запрос:

 SELECT DISTINCT 
    ek.EmpKey
    , ( SELECT [Value] FROM dbo.HRDescription( 'AL', h.p_abotype, 'fdesc' ) ) AS AboriginalTypeDescription
    , ( SELECT [Value] FROM dbo.HRDescription( 'CM', h.p_cma, 'fdesc' ) ) AS CensusMetroAreaDescription
    , ( SELECT [Value] FROM dbo.HRDescription( 'CS', h.p_constype, 'fdesc' ) ) AS ConsentTypeDescription
    , ( SELECT [Value] FROM dbo.HRDescription( 'IN', h.p_laborind, 'fdesc' ) ) AS DirectOrIndirectDescription
    , ( SELECT [Value] FROM dbo.HRDescription( 'DI', h.p_distype, 'fdesc' ) ) AS DisabilityTypeDescription
    , ( SELECT [Value] FROM dbo.HRDescription( 'I2', h.p_doca, 'fdesc' ) ) AS DocumentADescription
FROM dbo.hrpersnl AS h 
INNER JOIN HRActions.dbo.EmployeeKey AS ek 
    ON RTRIM(LTRIM(UPPER(h.p_empno))) = UPPER(ek.EmpNo) COLLATE SQL_Latin1_General_CP1_CI_AS 
    AND h.p_company = ek.Company COLLATE SQL_Latin1_General_CP1_CI_AS;

Опять же, не идеально, но это может сэкономить на производительности при объединении.