Выполните оператор обновления с переменными на удаленном сервере.

Содержание

Вопрос или проблема
Ответ или решение
1. Проблема с синтаксисом и переменными
2. Оптимизация запроса
3. Исправленный код
4. Объяснение кода
5. Заключение

Вопрос или проблема

Я использую SQL Server 2019 и столкнулся с проблемой производительности, когда мы используем связанные серверы для передачи данных в наши целевые таблицы с серверов источников. Ранее это работало нормально, когда объем данных был приемлемым, но по мере развития системы и увеличения объема данных мы столкнулись с проблемами производительности. Я пытаюсь переписать хранимые процедуры для выполнения на удаленном целевом сервере вместо этого. Хранимые процедуры либо обновляют, либо вставляют данные; в приведенном ниже примере у меня есть команда обновления.

SQL

DECLARE @RecordStartDate DATE = NULL;

     IF @RecordStartDate IS NULL
    SET @RecordStartDate = GETDATE();

DECLARE @RecordEndDate DATE = DATEADD(DAY, -1, @RecordStartDate);
DECLARE @updateQuery NVARCHAR(MAX);

    SET @updateQuery = '
    UPDATE Tgt
    SET
        Tgt.CURRENT_RECORD_FLAG = ''N'',
        Tgt.RECORD_END_DATE = @RecordEndDate,
        Tgt.RECORD_UPDATE_DT_TM = GETDATE()
    FROM [REMDB].[dbo].[TgtTable] Tgt
    JOIN [SRCDB].[dbo].[SrcTable] Src
        ON Tgt.ID = Src.ID
    WHERE Tgt.CURRENT_RECORD_FLAG = ''Y''
        AND Src.CURRENT_RECORD_FLAG = ''Y''
        AND (
            Tgt.NAME <> Src.NAME
            OR Tgt.LONG_NAME <> Src.LONG_NAME);
';

  PRINT @updateQuery

EXECUTE ('EXEC sp_executesql N''' + @updateQuery + '''') AT [REMOTETARGETSERVER];

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

Ответ или решение

Чтобы успешно выполнить оператор UPDATE с использованием переменных на удалённом сервере SQL Server 2019, важно учесть множество нюансов, связанных с синтаксисом, передачей переменных и производительностью. В данной статье мы рассмотрим, как переписать ваш код, чтобы исправить синтаксические ошибки и обеспечить корректную передачу значений переменных на удалённый сервер, избегая при этом проблем с производительностью, которые возникают при использовании связанного сервера.

1. Проблема с синтаксисом и переменными

Ваша текущая реализация имеет несколько синтаксических ошибок, связанных с тем, как вы строите текущий динамический запрос. Ваша цель — передать значение переменной @RecordEndDate на удалённый сервер, и для этого нужно использовать подход, который позволяет правильно передать параметры в динамический SQL.

2. Оптимизация запроса

Вместо обычного выполнения SQL-запроса с дефиницией переменных в строке, целесообразно использовать sp_executesql, который позволяет передавать параметры и повышает безопасность, снижая риск SQL-инъекций. Давайте перепишем ваш код с учетом этих рекомендаций.

3. Исправленный код

Вот улучшенная версия вашего кода:

DECLARE @RecordStartDate DATE = NULL;

IF @RecordStartDate IS NULL
    SET @RecordStartDate = GETDATE();

DECLARE @RecordEndDate DATE = DATEADD(DAY, -1, @RecordStartDate);
DECLARE @updateQuery NVARCHAR(MAX);
DECLARE @params NVARCHAR(MAX);

SET @updateQuery = '
    UPDATE Tgt
    SET
        Tgt.CURRENT_RECORD_FLAG = ''N'',
        Tgt.RECORD_END_DATE = @RecordEndDate,
        Tgt.RECORD_UPDATE_DT_TM = GETDATE()
    FROM [dbo].[TgtTable] Tgt
    JOIN [SRCDB].[dbo].[SrcTable] Src
        ON Tgt.ID = Src.ID
    WHERE Tgt.CURRENT_RECORD_FLAG = ''Y''
        AND Src.CURRENT_RECORD_FLAG = ''Y''
        AND (
            Tgt.NAME <> Src.NAME
            OR Tgt.LONG_NAME <> Src.LONG_NAME
    );';

-- Определяем структуру параметров
SET @params = '@RecordEndDate DATE';

-- Выполняем запрос с передачей параметра на удаленном сервере
EXECUTE sp_executesql @updateQuery, @params, @RecordEndDate = @RecordEndDate AT [REMOTETARGETSERVER];

4. Объяснение кода

Использование sp_executesql: Мы заменяем явное выполнение строки SQL на вызов sp_executesql, который позволяет передавать параметры более безопасным и эффективным способом.
Параметры: Мы определяем параметры в строке @params, и затем передаем их в вызове sp_executesql.
Структура запроса: Мы убрали необходимость использовать полное имя удалённой таблицы, так как FROM [dbo].[TgtTable] предполагает, что вы уже находитесь на удалённой базе данных. Это уменьшает сложности и повышает читаемость кода.

5. Заключение

Примеры кода, предоставленные в статье, помогут вам избежать ошибок синтаксиса и улучшить производительность при работе с удалёнными серверами SQL. Использование sp_executesql с передачей параметров делает ваш код более безопасным и эффективным, что особенно важно в контексте работы с большими объёмами данных. Следуя этим рекомендациям, вы сможете существенно повысить стабильность и быстродействие вашего приложения.