0

I have a query on my FirstServer like this one:

DROP TABLE IF EXISTS #TEMP;
CREATE TABLE #TEMP
(
    ItemId NVARCHAR(100),
    [Date] DATE
); 
INSERT INTO #TEMP(ItemId,[Date])
(
    SELECT 'test', '11/11/2011'
)

SELECT tmp.ItemId, tmp.[Date]
FROM #TEMP tmp
    WHERE NOT EXISTS
    (
        SELECT 1 FROM SECONDSERVER.SECONDDB.dbo.Items items
        WHERE 
            items.ItemId = tmp.ItemId
            AND tmp.[Date] = items.[Date]
    )
END

Execution of it takes 25 s. But if I execute the same code on my SECONDSERVER then execution is 200 ms.

When from my FirstServer I execute simple query:

select top 10 * from SECONDSERVER.SECONDDB.dbo.Items

Then I get results in 300 ms.

Any hints why it takes so long for query with temp table and join?

witkacy1986
  • 41
  • 2
  • 4
  • 1
    What does the actual execution plan show?...you can upload it to Paste The Plan and link it in you Post, for help. I bet in the slow case you're getting a Remote Scan (causing the entire table to be brought across the Linked Server before being processed). – J.D. Nov 24 '22 at 16:29
  • I think that you are correct. @J.D. plan is here (edited a bit) https://www.brentozar.com/pastetheplan/?id=rk6IkX6Uo

    Can I do something with that? It looks that it takes the whole table from one server to the other and then starts calculation which makes no sense in my case, I would prefer to move my temp data (which is not much) there and perform calculations there but I have no idea how linked serves computations work on sql server

     – witkacy1986 Nov 24 '22 at 16:53
  • 1
    That's the estimated execution plan. Please provide the actual execution plan, which can be (and probably is) different. – J.D. Nov 24 '22 at 17:51
  • 1
    @J.D. here is actual plan: https://www.brentozar.com/pastetheplan/?id=SJ804ITUj Sorry for changing some tbale names etc. – witkacy1986 Nov 24 '22 at 20:32

1 Answers1

0

Linked server computations with four part naming in SQL Server work, as you've seen, very poorly. Reading 7.5 million rows to find 0 rows is obviously not an efficient search.

With your example, your #temp table has exactly 1 row in it.

Thus, you have the option of a very simple remote query:

DECLARE @nsql NVARCHAR(n) = 'SELECT FROM SECONDDB.dbo.Items WHERE ItemId = ''test'' AND Date = ''11/11/2011'''

SELECT FROM OPENQUERY([SECONDSERVER],@nsql)

Note that if you're passing in variables - unlike your example - you're vulnerable to SQL injection, which is bad.

You can also try something like what https://learn.microsoft.com/en-US/troubleshoot/sql/admin/pass-variable-linked-server-query shows:

DECLARE @VAR char(2) SELECT @VAR = 'CA' EXEC MyLinkedServer.master.dbo.sp_executesql N'SELECT * FROM pubs.dbo.authors WHERE state = @state', N'@state char(2)', @VAR

which is parameterized.

Essentially, you MUST get the remote server to perform the WHERE clause itself, using its own indexes and returning only the necessary and useful data you need, to get good performance.

You should also beware SQL Injection.

Anti-weakpasswords
  • 1,708
  • 10
  • 13
  • Hi thanks, I presented only a simplified example. The case is that I have lets say 200-500 of items like this one and I need to check which of those are/arent in table on second server. I came up with an idea to create stored procedure on second server and pass serialized to xml list of items as a string to that procedure and execute it on another server. It takes 1 s so it is fine. You also wrote:

    Note that if you're passing in variables - unlike your example - you're vulnerable to SQL injection, which is bad.

    Could you elaborate a bit more about it?

     – witkacy1986 Nov 25 '22 at 09:53
  • https://xkcd.com/327/ https://dba.stackexchange.com/questions/127/do-stored-procedures-prevent-sql-injection – Anti-weakpasswords Nov 26 '22 at 06:26