I have installed Oracle server and SQL Server on separate machines which cause me a time delay of 21 seconds for each execution. Why executions delay? I have set RPC out (true).
SQL Server against Microsoft OleDB Provider for Oracle provider an Oracle DB Using Linked server.
Note: My main concern is either if the query is correct/incorrect it executes for 21 seconds.
Another case when I have both servers on the same machine it executes in milliseconds.
I have tried Following methods in SQL SERVER.
1, Using OPENQUERY:
SELECT * From OPENQUERY(Linked Server Name,’Select * from OracleTableName ‘)
2, Using Exec:
DECLARE @sql NVARCHAR(MAX);
SET @sql =(’Select * from OracleTableName ‘);
EXEC (@sql) AT Linked Server Name ;
How to reduce the time delay caused for the execution?
Thanks in advance
You first need to figure out where the time is spent to get a better idea how to fix it. I'd start with client sqlnet tracing (with timestamps enabled), and take a look at the timestamps of socket communication, packet sizes, things like that.
If you have a support contract, go ahead and open a SR. We're pretty good at looking at sqlnet traces and better defining the behavior.