Your code for the query is using two servers, identified by IP address as shown below. Where is the server [123.456.7.890]? And where is the server [098.765.4.321]?
My guess is that [098.765.4.321] is the [RemoteServer] and [123.456.7.890] is the [LocalServer].
First of all, the slowness of the code is not SQL Agent's fault. Code run from SQL Agent is pretty much as efficient or inefficient as code run directly from SSMS. The problem is that you are moving much more data across the linked server than you might think. Here is the outline of your code:
INSERT ... INTO [LocalServer].[DBASE1].[dbo].[TABLE]
SELECT TOP 3 ... FROM [RemoteServer].[DBASE2].[dbo].[TABLE2]
WHERE NOT EXISTS
(SELECT ... FROM [LocalServer].[DBASE1].[dbo].[TABLE])
If I have interpreted this correctly, then likely most of the data in
[RemoteServer].[DBASE1].[dbo].[TABLE] has to be dragged over to the
[LocalServer] and stored in a worktable in
It is extremely unlikely that the
WHERE NOT EXISTS is processing to find the first 3 matching rows that come across the linked server. Instead it likely waits for the data to be present locally, before processing the worktable data to filtered by the
WHERE NOT EXISTS and then choose the
TOP 3 rows.
The slowness is due to how much data is being moved between servers and by the likelihood that the [LocalServer] does not have access to the [RemoteServer]'s statistics.
You might also read the Guidelines for Using Distributed Queries at: http://technet.microsoft.com/en-us/library/ms175129(v=sql.105).aspx In part, the document says: "To create the best query plans ..., the query processor must have data distribution statistics from the linked server."
Using OPENQUERY to get the results from a remote server will often result in better performance. When using OPENQUERY, the query is sent to the REMOTE server and is executed there in order to return data to the local server. (You will notice that in OPENQUERY the query uses a three-part name, since it runs directly on the remote server.)
Likewise, at times creating a stored procedure to run on [RemoteServer] would allow processing to happen on that server first, then return a more limited data set. After that data is returned, the code on the [LocalServer] can complete the process.
Movement of data is costly to overall performance and moving data between servers is even more costly.