如何更快地使用 openquery 进行查询?

里卡多·费雷拉

此查询需要花费大量时间运行。有谁知道让他们更快?

    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<[email protected])

    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<[email protected]);

select @expected;
select @actual;

本文收集自互联网,转载请注明来源。

如有侵权,请联系 [email protected] 删除。

编辑于
0

我来说两句

0 条评论
登录 后参与评论

相关文章