Удаленный запрос медленный, если использовать переменные vs literal

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

Вот таблица, которую я хочу ОБНОВИТЬ на сервере 2:

(Stuff Id UNIQUEIDENTIFIER
, stuffname NVARCHAR(64))

Мне нужно обновить его с сервера 1.

Итак, я пытался это сделать:

DECLARE @newstuff nvarchar(64)

SELECT @newstuff = 'new stuff'

UPDATE [server2].database2.dbo.Stuff
SET [email protected]
WHERE stuffId='4893CD93-08B3-4981-851B-5DC972288290'

Это занимает 11 секунд. Этот следующий с использованием литерала пробегает менее 1 секунды

UPDATE [server2].database2.dbo.Stuff
SET stuffname='new stuff'
WHERE stuffId='4893CD93-08B3-4981-851B-5DC972288290'

Я сравнил фактические планы выполнения. Медленный делает удаленное сканирование, которое берет 100% стоимости, плюс 5 других шагов (фильтр, катушка таблицы, вычислительный скаляр, удаленное обновление, обновление). Быстрый - это просто шаги UPDATE и Remote Query. Мне нужно использовать переменные, поэтому мне нужен способ заставить его выполнять весь запрос удаленно.

Я попытался использовать OPTION (RECOMPILE), но server1 использует SQL Server 2005. server2 использует SQL Server 2012. Я не могу полностью изменить структуру базы данных на сервере2 без серьезных проблем. У меня нет проблем с проверкой подлинности. Я пробовал сглаживание таблицы при ее обновлении.

Я также пытался использовать Openquery. Когда я помещаю фильтр id в строку запроса, он возвращается до менее 1 секунды:

UPDATE OPENQUERY([server2], 'select stuffname, stuffid from database2.dbo.stufftable where contactid=''4CA1D489-9221-E511-A441-005056C00008''')
SET stuffname = @newstuff

Но мне нужен этот идентификатор также как переменная, а открытый запрос не принимает переменные (https://msdn.microsoft.com/en-CA/library/ms188427.aspx). Я попробовал запустить Openquery с фильтром id вне запроса, но это работает через 4 секунды. Это лучше, чем 11, но не здорово:

UPDATE OPENQUERY([server2],'select stuffname, stuffid from database2.dbo.stufftable')
set [email protected]
where contactid='4CA1D489-9221-E511-A441-005056C00008'

Конечно, я запускаю openquery с помощью exec (@sql), но я действительно не хочу идти этим путем. Я мог бы сделать всю инструкцию обновления таким образом, используя литералы, и даже не использовать OPENQUERY и получить такой же результат в любом случае.

Есть ли какой-либо способ для исправления этой производительности без использования exec (@sql)?

Ответ 1

Вы можете использовать динамический SQL с параметрами, используя sp_executesql на удаленной стороне.

declare @SQL nvarchar(max);

set @SQL = 'UPDATE database2.dbo.Stuff
            SET [email protected]
            WHERE stuffId=''4893CD93-08B3-4981-851B-5DC972288290'''

exec [server2].master.dbo.sp_executesql @SQL, N'@newstuff nvarchar(64)', @newstuff

Ответ 2

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

есть ссылки, где этот случай был объяснен, и у меня был подобный опыт. вот несколько ссылок:

OPENQUERY при выполнении связанных запросов сервера в SQL Server

ТОП-3 УБИРАЮЩИХСЯ ПОКАЗАТЕЛЕЙ ЗА ПОСЛЕДНИЕ СЕРВЕРА QUERIES

Я отправлю свое решение ниже.

Я установил среду для тестирования вашего решения. мой сервер2 - это сервер sql 2005

мой сервер1 - это сервер sql 2012.

На сервере2 я создал и заполнил таблицу материалов следующим образом:

Я использую базу данных, называемую tablebackups, с определенным соглашением об именах, но я уверен, что вы можете понять: Результатом является таблица с кластерным первичным ключом в поле идентификации и другое поле для обновления. эта таблица в моем примере содержит 100 000 записей.

select @@version
--Microsoft SQL Server 2005 - 9.00.5000.00 (Intel X86) 
    --Dec 10 2010 10:56:29 
    --Copyright (c) 1988-2005 Microsoft Corporation
    --Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

use tablebackups
go

CREATE TABLE dbo._MM_201504710_stuff ( Id UNIQUEIDENTIFIER
                    , stuffname NVARCHAR(64)
                    )

ALTER TABLE dbo._MM_201504710_stuff ADD  CONSTRAINT [PK_Stuff] UNIQUE CLUSTERED ( ID );

-- add 100,000 records to the table so that we can have an idea of execution
SET NOCOUNT ON
insert into dbo._MM_201504710_stuff values (NewID(),'Radhe Radhe')
GO 100000 -- 100,000
SET NOCOUNT OFF
--this took 19:38


--just to test
SELECT TOP 100 * FROM dbo._MM_201504710_stuff
--18D4BDEA-6226-47E1-94DB-00402A29798F



DECLARE @newstuff nvarchar(64)
SELECT @newstuff = 'new stuff'
UPDATE dbo._MM_201504710_stuff
SET [email protected]
WHERE Id='18D4BDEA-6226-47E1-94DB-00402A29798F'



UPDATE dbo._MM_201504710_stuff
SET stuffname='new stuff'
WHERE Id='18D4BDEA-6226-47E1-94DB-00402A29798F'

планы выполнения этих обновлений очень похожи и не являются проблемой. Как вы можете видеть на рисунке ниже.

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

Затем я перехожу к server1.

НЕТ, Прежде чем я перейду к серверу1, на сервере2 у меня есть этот sql-логин со следующими разрешениями:

Я называю это "монитором"

и для разрешений "монитора" Я использую этот выбор:

SELECT p.[name], sp.permission_name, p.type_desc AS loginType FROM sys.server_principals p 
  JOIN sys.server_permissions Sp
   ON p.principal_id = sp.grantee_principal_id WHERE sp.class = 100

Это показывает мне эти разрешения:

СЕЙЧАС на сервере1 У меня есть связанный сервер с server2 (sqlsalon1.dev.boden.local) и этот сервер LINKED использует "монитор" для подключения к серверу2.

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

В СЕРВЕРЕ 1: Я подключаюсь к серверу2 с помощью следующего связанного сервера:

запуск этих скриптов (менее секунды)

-- just to test
select top 100 *
from [SQLSALON1.dev.boden.local].tablebackups.dbo._MM_201504710_stuff


--first update
UPDATE  [SQLSALON1.dev.boden.local].tablebackups.dbo._MM_201504710_stuff
SET stuffname='new stuff'
WHERE Id='18D4BDEA-6226-47E1-94DB-00402A29798F'



--second update
DECLARE @newstuff nvarchar(64)
SELECT @newstuff = 'new stuff'
UPDATE [SQLSALON1.dev.boden.local].tablebackups.dbo._MM_201504710_stuff
SET [email protected]
WHERE Id='18D4BDEA-6226-47E1-94DB-00402A29798F'

Я получаю этот план запроса:

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

Другой способ Из SQL 2005 Обновление таблицы в SQL 2012

на sql 2012 создать и заполнить таблицу

select @@version
--Microsoft SQL Server 2012 - 11.0.5058.0 (X64) 
--  May 14 2014 18:34:29 
--  Copyright (c) Microsoft Corporation
--  Standard Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)


use tablebackups
go

CREATE TABLE dbo._MM_201504710_stuff ( Id UNIQUEIDENTIFIER
                    , stuffname NVARCHAR(64)
                    )

ALTER TABLE dbo._MM_201504710_stuff ADD  CONSTRAINT [PK_Stuff] UNIQUE CLUSTERED ( ID );

-- add 100,000 records to the table so that we can have an idea of execution
SET NOCOUNT ON
insert into dbo._MM_201504710_stuff values (NewID(),'Radhe Radhe')
GO 100000 -- 100,000
SET NOCOUNT OFF
--this took 19:38


--just to test
SELECT TOP 100 * FROM dbo._MM_201504710_stuff
--3E29A8E5-BA57-4A9C-803E-003C13A80905

после заполнения таблицы Я проверяю статистику

получается, что статистика не обновляется

поэтому я обновляю статистику:

--================================================
-- HAD TO UPDATE THE STATS BEFORE RUNNING THE UPDATES
--================================================
UPDATE STATISTICS dbo._MM_201504710_stuff

Я снова проверяю, и на этот раз все нормально.

Создайте связанный сервер с sql 2005 на sql 2012:

USE [master]
GO

/****** Object:  LinkedServer [SQLMON1]    Script Date: 13/07/2015 17:09:08 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'SQLMON1', @srvproduct=N'SQL Server'
 /* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'SQLMON1',@useself=N'False',@locallogin=NULL,@rmtuser=N'monitor',@rmtpassword='########'

GO

EXEC master.dbo.sp_serveroption @server=N'SQLMON1', @optname=N'collation compatible', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'SQLMON1', @optname=N'data access', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'SQLMON1', @optname=N'dist', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'SQLMON1', @optname=N'pub', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'SQLMON1', @optname=N'rpc', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'SQLMON1', @optname=N'rpc out', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'SQLMON1', @optname=N'sub', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'SQLMON1', @optname=N'connect timeout', @optvalue=N'0'
GO

EXEC master.dbo.sp_serveroption @server=N'SQLMON1', @optname=N'collation name', @optvalue=null
GO

EXEC master.dbo.sp_serveroption @server=N'SQLMON1', @optname=N'lazy schema validation', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'SQLMON1', @optname=N'query timeout', @optvalue=N'0'
GO

EXEC master.dbo.sp_serveroption @server=N'SQLMON1', @optname=N'use remote collation', @optvalue=N'true'
GO

Я удалил параметр ниже сервера.

Проверьте разрешения "монитора" на целевом сервере

SELECT p.[name] collate database_default, 
       sp.permission_name, 
       p.type_desc AS loginType 
   FROM sys.server_principals p 
  JOIN sys.server_permissions Sp
   ON p.principal_id = sp.grantee_principal_id
    WHERE sp.class = 100 
    and name = 'monitor'

и после этого мы можем запускать обновления с сервера sql 2005.

--first update
UPDATE  [SQLMON1].tablebackups.dbo._MM_201504710_stuff
SET stuffname='new stuff'
WHERE Id='3E29A8E5-BA57-4A9C-803E-003C13A80905'



--second update
DECLARE @newstuff nvarchar(64)
SELECT @newstuff = 'new stuff'
UPDATE [SQLMON1].tablebackups.dbo._MM_201504710_stuff
SET [email protected]
WHERE Id='3E29A8E5-BA57-4A9C-803E-003C13A80905'

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

<Т411 >

Ответ 3

Я бы сделал это так. Вместо отправки фактического запроса UPDATE для выполнения на server2, я бы сделал хранимую процедуру на сервере2 с необходимыми параметрами и вызвал ее с сервера1.

Внутри хранимой процедуры вы можете настроить запрос по мере необходимости, используя все возможности сервера2, чтобы он работал быстро (например, OPTION(RECOMPILE)).

Кроме того, наличие явных хранимых процедур, подобных этому, определяет интерфейс, как взаимодействуют две системы, что само по себе хорошо.

Ответ 4

Решение этого должно состоять в том, чтобы убедиться, что параметры, которые вы используете, соответствуют длине и типу столбцов. Например, убедитесь, что столбец NVARCHAR (64) нацелен на "DECLARE @var AS NVARCHAR (64)".

В вашем примере, который, кажется, имеет место, но при тестировании этого в локальной настройке (с SQLEXPRESS 2005, связанным с SQLEXPRESS 2014), я получаю только "Удаленное сканирование", если не соответствует длине и типу.

Ответ 5

Возможно, проблема связана с уникальным столбцом идентификатора. Ты пробовал: На сервере2 определите следующую хранимую процедуру:

CREATE PROCEDURE updateStuff( @newstuff nvarchar(30), @stuffid    varchar(36))
AS 
 UPDATE Stuff
 SET [email protected]
 WHERE stuffId=convert(uniqueidentifier, @stuffid))

От сервера 1 вызывают: exec server2.database2.updatestuff N'New stuff ',' 4893CD93-08B3-4981-851B-5DC972288290 '

СТАРОЕ ПРЕДЛОЖЕНИЕ: Объявить @stuffid uniqueidentifier Set @stuffid = convert (uniqueidentifier, = '4893CD93-08B3-4981-851B-5DC972288290') ОБНОВЛЕНИЕ [server2].database2.dbo.Stuff SET stuffname = @newstuff WHERE stuffId = @stuffid

Ответ 6

Вы можете попробовать установить значение в объявлении переменной:

DECLARE @newstuff nvarchar(64) = 'new stuff'

UPDATE [server2].database2.dbo.Stuff
SET [email protected]ewstuff
WHERE stuffId='4893CD93-08B3-4981-851B-5DC972288290'

Я думаю, что удаление части select поможет.