此查询需要花费大量时间运行。有谁知道让他们更快?
DECLARE @actual as INT
DECLARE @expected as INT
DECLARE @ID as INT
DECLARE @TSQL varchar(100)
DECLARE @t table (c int)
DECLARE @Id_aux varchar(100)
SET @ID = (select max(id) from [PAY_STG].pay.paybuddy_purchase_ex where fk_country = 5 )
SET @Id_aux= CONVERT(NVARCHAR(100), @ID)
SET @TSQL = 'SELECT * FROM OPENQUERY([BE_PAY_MA],''select count(*) from paybuddy_purchase_ex where id <=' + @Id_aux + ''')'
INSERT INTO @t EXEC (@TSQL)
SET @expected = (select c from @t)
SET @actual = (SELECT count(*) from [PAY_STG].pay.paybuddy_purchase_ex where fk_country = 5 and id<=@ID)
select @expected
select @actual
谢谢
通过链接服务器执行查询时最好使用不同的方法。
EXECUTE ... AT [Linked_Server];
好处:
有用的链接:SQL Server:在 LinkedServer 执行
看看下面。
SQL
DECLARE @actual as INT;
DECLARE @expected as INT;
DECLARE @ID as INT;
DECLARE @TSQL varchar(100);
DECLARE @t table (c int);
--DECLARE @Id_aux varchar(100);
SET @ID = (select max(id) from [PAY_STG].pay.paybuddy_purchase_ex where fk_country = 5 );
--SET @Id_aux= CONVERT(NVARCHAR(100), @ID);
--SET @TSQL = 'SELECT *
-- FROM OPENQUERY([BE_PAY_MA],
-- ''SELECT COUNT(*) FROM paybuddy_purchase_ex WHERE id <=' + @Id_aux + ''')';
--INSERT INTO @t EXEC (@TSQL);
INSERT INTO @t
EXECUTE(N'SELECT COUNT(*) FROM paybuddy_purchase_ex WHERE id <= ?',
@ID) AT [BE_PAY_MA];
SET @expected = (select c from @t);
SET @actual = (SELECT count(*) from [PAY_STG].pay.paybuddy_purchase_ex where fk_country = 5 and id<=@ID);
select @expected;
select @actual;
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句