How put query with openquery faster?-CodePudding

this query takes a lot of time running. Does anyone know anyway to put them faster?

    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

Thanks

CodePudding user response：

It is better to use a different approach while executing queries via linked servers.

EXECUTE ... AT [Linked_Server];

Benefits:

This way a query will be executed on the remote server. Guaranteed. Thus it will be much more performant.
Very easy to pass parameters. No strings concatenation.

Useful link: SQL Server: Execute At LinkedServer

Check it out below.

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;