Большая разница в производительности (от 1 часа до 1 минуты) обнаружена в SQL. Можете ли вы объяснить, почему?

Следующие запросы занимают 70 минут и 1 минуту соответственно на стандартной машине для 1 миллиона записей. Какие могут быть возможные причины?

Запрос [01:10:00]

SELECT * 
FROM cdc.fn_cdc_get_net_changes_dbo_PartitionTest(
    CASE WHEN sys.fn_cdc_increment_lsn(0x00)<sys.fn_cdc_get_min_lsn('dbo_PartitionTest')        
        THEN sys.fn_cdc_get_min_lsn('dbo_PartitionTest')        
        ELSE sys.fn_cdc_increment_lsn(0x00) END
    , sys.fn_cdc_get_max_lsn()
    , 'all with mask') 
WHERE __$operation <> 1

Измененный запрос [00:01:10]

DECLARE @MinLSN binary(10)
DECLARE @MaxLSN binary(10)
SELECT @MaxLSN= sys.fn_cdc_get_max_lsn()
SELECT @MinLSN=CASE WHEN sys.fn_cdc_increment_lsn(0x00)<sys.fn_cdc_get_min_lsn('dbo_PartitionTest')     
        THEN sys.fn_cdc_get_min_lsn('dbo_PartitionTest')        
        ELSE sys.fn_cdc_increment_lsn(0x00) END

SELECT * 
FROM cdc.fn_cdc_get_net_changes_dbo_PartitionTest(
        @MinLSN, @MaxLSN, 'all with mask') WHERE __$operation <> 1

[Изменено]

Я попытался воссоздать сценарий с аналогичной функцией, чтобы увидеть, оцениваются ли параметры для каждой строки.

CREATE FUNCTION Fn_Test(@a decimal)RETURNS TABLE
AS
RETURN
(
    SELECT @a Parameter, Getdate() Dt, PartitionTest.*
    FROM PartitionTest
);

SELECT * FROM Fn_Test(RAND(DATEPART(s,GETDATE())))

Но я получаю то же значение для столбца «Параметр» для миллиона записей, обработанных за 38 секунд.


sql function sql-server performance cdc
person Faiz    schedule 30.11.2009    source источник

Ответы (2)


arrow_upward
2
arrow_downward

Даже детерминированные скалярные функции вычисляются по крайней мере один раз в строке. Если одна и та же детерминированная скалярная функция встречается несколько раз в одной и той же «строке» с одними и теми же параметрами, я считаю, что только тогда она будет оцениваться один раз, например. в CASE WHEN fn_X(a, b, c) > 0 THEN fn_X(a, b, c) ELSE 0 END или что-то в этом роде.

Я думаю, что ваша проблема RAND заключается в том, что вы продолжаете повторно раздавать:

Повторные вызовы RAND() с одним и тем же начальным значением возвращают одинаковые результаты.

Для одного соединения, если RAND() вызывается с указанным начальным значением, все последующие вызовы RAND() дают результаты на основе начального вызова RAND(). Например, следующий запрос всегда будет возвращать одну и ту же последовательность чисел.

Я приступил к кэшированию результатов скалярной функции, как вы указали, - даже зашел так далеко, что предварительно вычислил таблицы результатов скалярной функции и присоединился к ним. Что-то должно быть сделано в конце концов, чтобы заставить скалярные функции работать. Верно, лучший вариант - CLR - очевидно, они намного превосходят SQL UDF. К сожалению, я не могу использовать их в моей текущей среде.

person Cade Roux    schedule 30.11.2009
comment
Но повторное заполнение RAND() гарантирует, что мы получим случайные значения, верно? Подавая вторую ценность времени как начальное значение, я думаю, я обеспечиваю это. - person Faiz; 30.11.2009
comment
Вы не выполняете повторное заполнение RAND(). Вы передаете ему одно значение, и оно возвращает одно значение, и это единственное значение затем передается в вашу функцию. - person Tom H; 30.11.2009
comment
Извините, когда я впервые прочитал об использовании RAND(), я подумал, что вы выполняете SELECT fn(RAND(seed)) from tbl, что, конечно же, то же самое, что и SELECT fn(a_number) FROM tbl, поскольку вы выполняете повторное заполнение с тем же стоимость. В любом случае информация о поведении скалярной функции является правильной интерпретацией того, что происходит в вашем случае. - person Cade Roux; 30.11.2009
comment
@Tom H: я передаю текущее второе значение в RAND() для повторного заполнения. Вы хотите сказать, что это не работает? Если выражение параметра оценивается каждый раз, то с течением времени функции RAND() будут передаваться новые начальные значения, верно? - person Faiz; 01.12.2009

arrow_upward
6
arrow_downward

В вашем первом запросе ваши fn_cdc_increment_lsn и fn_cdc_get_min_lsn выполняются для каждой строки. Во втором примере только один раз.

person Rubens Farias    schedule 30.11.2009
comment
Но это всего лишь параметры функции. Почему он оценивается для каждой строки. Я тоже так подумал, когда впервые это увидел. Затем я попытался протестировать случай с помощью аналогичной функции [обновил вопрос с подробностями], но в обоих случаях он завершается одновременно. - person Faiz; 30.11.2009
comment
Верно, но функцию необходимо оценивать для каждой строки вывода. - person Thorsten; 30.11.2009
comment
Может ли кто-нибудь ответить «почему»? - person Faiz; 30.11.2009
comment
Почему? Потому что даже детерминированные скалярные функции вычисляются по крайней мере один раз в строке. Детерминированная скалярная функция с идентичными параметрами в строке будет оцениваться только один раз для каждой строки, но все равно будет оцениваться в последующих строках, даже если вызываться с теми же параметрами. - person Cade Roux; 30.11.2009
comment
Я думаю, вы упускаете из виду, что функция, которую я вызываю, является функцией с табличным значением. Итак, насколько я понимаю, не существует концепции «оценки для каждой строки». Ваш аргумент верен, если я использую скалярную функцию, такую ​​​​как Select Fn_Scalar (Param) From Table1, тогда для каждой строки в таблице Table1 функция будет оцениваться. В моем случае это табличная функция. Если я правильно понял, порядок выполнения будет таким: Вычислить параметр из выражения ›› Выполнить функцию ›› Применить фильтр к набору результатов ›› Выбрать указанные столбцы; для вышеуказанного запроса. Скажите, я что-то пропустил? - person Faiz; 01.12.2009
comment
Фаиз, во втором случае вычисляется одно случайное значение и передается в табличную функцию, поэтому вы получаете одно и то же значение снова и снова. План выполнения не связан с планом вашей предыдущей проблемы. - person Cade Roux; 01.12.2009