I have a stored procedure in which a linked server is addressed only under certain conditions. Especially on slow network connections, it becomes clear that the execution of the stored procedure is slowed down simply by naming (and not using) the linked server (runtime greater about 5 seconds).
DROP PROC IF EXISTS dbo.slowMystery;
GO
CREATE PROC dbo.slowMystery
AS
BEGIN
DECLARE @innerTimestamp DATETIME = GETDATE();
IF 1 = 0
BEGIN -- this will never be executed!
SELECT TOP 1
*
FROM
[remoteSqlInstance].someDb.dbo.someTable;
END;
ELSE
BEGIN
PRINT 'Hello world!';
END;
PRINT CONCAT('inner runtime: ', DATEDIFF(MILLISECOND, @innerTimestamp, GETDATE()));
END;
GO
Now, give it a try:
DECLARE @outerTimestamp DATETIME = GETDATE();
EXEC dbo.slowMystery;
PRINT CONCAT('outer runtime: ', DATEDIFF(MILLISECOND, @outerTimestamp, GETDATE()));
Result:
Hello world!
inner runtime: 0
outer runtime: 3733
As you can see in the example, the code regarding the linked sever is never executed. The (inner) runtime of the stored procedure is therefore 0 milliseconds. However, the period for the entire execution is around 4 seconds (connection to the linked server via a slow vpn), although the linked server is de facto not addressed.
If you remove the SELECT-statement, it quickly becomes clear that the outer runtime is not determined by other factors:
...
IF 1 = 0
BEGIN -- this will never be executed!
PRINT 'This will never be executed!'
END;
...
Result:
Hello world!
inner runtime: 0
outer runtime: 0
I think this is because the local server checks if the linked server is available before executing the stored procedure.
Is there an option to turn off this check or some other way to get around these delays?
Thanks for any input. Robert