January 3, 2017 at 6:15 am
SQL Server Linked Server to Oracle literal Query is taking so much time and But Variable is working fast.
I have production Server : SQL Server 2008 R2 SP2 64 Bit and Oracle 11G Server.
Configured the Linked Server between the SQL Server to Oracle using Oracle Provider for OLE DB 64 bit Driver.
Query 1 With Variable Declaration is fast less than 2 Seconds.
Declare @Accno varchar(20)
set @Accno='06559703000100'
SELECT * FROM [LINKEDServer]..[XXXXX].[XXXX_XXXX_XXX] Where XXXX_number=@XXXno
Is fast and return results less than 2 Seconds.
In the Execution plan the Remote Query property the Query is having Where Clause XXXX_number
Query 2 With Literal Query very slow.
SELECT * FROM [LINKEDServer]..[XXXXX].[XXXX_XXXX_XXX] Where XXXX_number='XXXno'
Is very taking more than 1 hour and In Execution Plan the Remote query is not taking the where clause, and in from Oracle its observed same.
Is there any way to improve the Literal Query or changes required please suggest.
Thanks in advance.
January 3, 2017 at 12:08 pm
If the query works with a variable - then why would you be concerned about using the literal value? What is happening here is that SQL Server uses the version with the variable to pass that information to Oracle - which allows Oracle to filter based on the where clause.
The version without the variable - SQL Server doesn't know how to pass the parameter to Oracle so it decides to pull all data in that table locally then apply the parameter(s).
Just one more reason to avoid using linked servers if there are better options available. You have no control over how SQL Server will parse the query and determine what it sends across the linked server to be processed.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply