Блог

У меня есть следующий запрос, выполнение которого занимает много времени (около 2 часов):

 CREATE TABLE #compareList  
(
     id  INT IDENTITY(1,1), 
     poy_no varchar(max), 
     poy_stat_cd varchar(max), 
     poy_eff_dd datetime, 
     poy_exp_dd datetime,   
     [Name] [nvarchar] (max)
);
    
DECLARE @poy_no varchar(max), @poy_stat_cd varchar(max),
        @poy_eff_dd datetime, @poy_exp_dd datetime, @remarks nvarchar(max)

DECLARE C_Compare CURSOR STATIC FOR
    SELECT b.poy_no, b.poy_stat_cd, b.poy_eff_dd, b.poy_exp_dd, a.remarks    
    FROM table1 a  

OPEN C_Compare

FETCH NEXT FROM C_Compare 
    INTO @poy_no, @poy_stat_cd, @poy_eff_dd, @poy_exp_dd, @remarks

WHILE @@FETCH_STATUS = 0
BEGIN
    INSERT INTO #compareList 
        SELECT @poy_no, @poy_stat_cd, @poy_eff_dd, @poy_exp_dd, @remarks 

    FETCH NEXT FROM C_Compare 
        INTO @poy_no, @poy_stat_cd, @poy_eff_dd, @poy_exp_dd, @remarks
END

CLOSE C_Compare;
DEALLOCATE C_Compare;

-- This query has performance issue
SELECT
    COUNT(1)
FROM 
    #compareList a,
    (SELECT
          pid, single_string_name, original_script_name, 
          surname, first_name, middle_name 
      FROM 
          DJ_PERSON WITH (INDEX (NCIndex_all_needed_columns))) AS p,
    (SELECT pid, desc1 FROM PERSON_DESC) AS pd,
    DESC1 AS d
WHERE  
    p.pid = pd.pid
    AND pd.desc1 = d.d1id
    AND replace(replace(replace(rtrim(ltrim(a.name)), ' ',''), ',',''), '.','') != ''
    AND (replace(replace(replace(a.Name, ' ',''), ',',''), '.','') = replace(replace(replace(p.single_string_name, ' ',''), ',',''), '.','')
        COLLATE database_default
        OR replace(replace(replace(a.Name, ' ',''), ',',''), '.','') = replace(replace(replace(p.original_script_name, ' ',''), ',',''), '.','')
        COLLATE database_default
        OR
         replace(replace(replace(a.Name, ' ',''), ',',''), '.','') = replace(replace(replace(p.surname p.first_name p.middle_name, ' ',''), ',',''), '.','')
        ) 
 

Ниже приведено количество строк для каждой таблицы. Таблицы PERSON и PERSON_DESC имеют большое количество строк.

• ПОЛЬЗОВАТЕЛЬ — 4638768
• PERSON_DESC — 2040027
• #compareList — 26
• Я попытался применить кластеризованный и некластеризованный индекс к таблицам PERSON и PERSON_DESC .
• В таблице PERSON я применил индекс к pid, single_string_name, original_script_name, surname, first_name, middle_name
• В таблице PERSON_DESC я применил индекс pid, desc1 .

Ниже приведен параметр статистики

 Table '#compareList________________________________________________________________________________________________________0000000001C5'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 1, logical reads 8055799, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Workfile'. Scan count 16, logical reads 43232, physical reads 5431, read-ahead reads 42753, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'PERSON'. Scan count 1, logical reads 42966, physical reads 1, read-ahead reads 10440, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'DESC'. Scan count 1, logical reads 7060, physical reads 1, read-ahead reads 7054, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'DESC1'. Scan count 1, logical reads 1, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
 

Какие изменения я могу внести, чтобы увеличить время выполнения этого запроса?

У вас огромная проблема с доступностью, т. Е. Из-за всех вызовов функций, которые вы выполняете в своем where предложении. Таким образом, будет использоваться очень мало индексов, если таковые имеются. У меня есть несколько предложений.

Во-первых, если у вас есть какой-либо способ ограничить записи, которые вам нужно протестировать перед вызовом каких-либо функций, сделайте это, поместите результаты во временную таблицу, а затем запустите where для нее предложения на основе функций. Что-то вроде:

 select columns, compute columns that we can compute here (should be one side of the compare)
into #MyTempTable
from MyTable
where {my saragable conditions};

-- Potentially add some indexes to the temp table computed columns

select columns
from #MyTempTable
where {my unsaragable conditions};
 

Во-вторых, OR изменение нескольких условий является довольно хорошо известной проблемой производительности. Это можно решить UNION ALL , например, с помощью

 SELECT {your query}
WHERE p.pid = pd.pid
AND pd.desc1 = d.d1id
AND replace(replace(replace(rtrim(ltrim(a.[Name])), ' ',''), ',',''), '.','') != ''
AND replace(replace(replace(a.[Name], ' ',''), ',',''), '.','') = replace(replace(replace(p.single_string_name, ' ',''), ',',''), '.','') COLLATE database_default

UNION ALL

SELECT {your query}
WHERE p.pid = pd.pid
AND pd.desc1 = d.d1id
AND replace(replace(replace(rtrim(ltrim(a.[Name])), ' ',''), ',',''), '.','') != ''
AND replace(replace(replace(a.[Name], ' ',''), ',',''), '.','') = replace(replace(replace(p.original_script_name, ' ',''), ',',''), '.','') COLLATE database_default

UNION ALL

SELECT {your query}
WHERE p.pid = pd.pid
AND pd.desc1 = d.d1id
AND replace(replace(replace(rtrim(ltrim(a.[Name])), ' ',''), ',',''), '.','') != ''
AND replace(replace(replace(a.[Name], ' ',''), ',',''), '.','') = replace(replace(replace(p.surname p.first_name p.middle_name, ' ',''), ',',''), '.','');
 

В-третьих, первые два предложения не помогают, возможно, вам придется подумать о материализации данных, которые вы используете в своем where предложении. Под этим я подразумеваю в качестве примера взятие:

 replace(replace(replace(p.single_string_name, ' ',''), ',',''), '.','') COLLATE database_default
 

и сохранить это значение в новом столбце таблицы p , который затем можно проиндексировать. Возможно, вам придется написать триггеры, чтобы поддерживать его.

Говоря, что, учитывая, что часть ваших данных уже находится во временной таблице #compareList , вы должны напрямую сохранить значение сравнения в своей временной таблице, т. Е. Добавить еще один столбец, в котором хранятся:

 replace(replace(replace(rtrim(ltrim(a.[Name])), ' ',''), ',',''), '.','')
 

а затем потенциально проиндексировать его.