Steen Schlüter Persson (DK)
3/28/2007 7:23:00 PM
M A Srinivas wrote:
> On Mar 28, 7:12 pm, "Steen Schlüter Persson (DK)"
> <steen@REMOVE_THIS_asavaenget.dk> wrote:
>> Hi
>>
>> We are facing a quite strange performance issue when using a linked
>> server to an Oracle server.
>>
>> When we run a query containing a WHERE clause using a variable, it takes
>> ages for SQL server to return the data. When we instead just type in the
>> value for the WHERE clause it returns the data as fast as expected.
>>
>> The queries we have used for testing is these:
>>
>> 1:
>>
>> select PERIODSTART from MACONOMY..RAMAIN60.TIMESHEETLINE where
>> EMPLOYEENUMBER = '06131'
>>
>> This query returns 6165 rows in 5 seconds which is normal performance.
>>
>> 2:
>>
>> declare @medanr varchar(255)
>> set @medanr = '06131'
>>
>> select PERIODSTART from MACONOMY..RAMAIN60.TIMESHEETLINE Where
>> EMPLOYEENUMBER = @medanr
>>
>> This query runs for approx. 14 minutes and returns the same number of rows.
>>
>> We can see that the difference in execution times increases with the
>> number of rows the table contains. Without being sure, we suspect that
>> the problem is that Oracle can't use an index when the query contains a
>> WHERE clause using a variable, but why?
>> It have worked for us before (actually the code giving problems has been
>> running for years).
>>
>> What has happened recently, is that the SQL server has been upgraded
>> from SQL2000 SP3a (8.00.818) to SQL 2000 SP4. This could of course be
>> the reason, but to verify this we have installed a new server with
>> SQL2000 SP3a + MS03-031 Security Fix (which ends up with the same
>> version as we have on the affected server - 8.00.818) but we are facing
>> the same problem with this new server.
>> It could of course also be caused by the Oracle server, but we have
>> tried it against 2 different Oracle databases (different applications)
>> running on different physical servers and in both cases the problem are
>> the same.
>> Furthermore we have also tried it from a SQL2005 server against the same
>> Oracle databases but with same problem.
>>
>> Right now we are running out if ideas and don't really know what to go
>> for.............
>>
>> Has any one seem similar issues or has any good ideas?
>>
>> --
>> Regards
>> Steen Schlüter Persson
>> Database Administrator / System Administrator
>
> We had the same problem . If you check dynamic Properties and nested
> query in provider options , it may improve query performance for
> variables . These options are available when you create link server
>
Thanks for all the inputs. We have already been down the route with
the various providers options, and as far as I remember we have already
tried with the "Nested queries" option but to no avail.
Using OPENQUERY could be an option if it was a new Stored Proc or code
we where writing, but at the moments we have hundreds of SP's where
those linked servers are involved so it's not that easy to rewrite the
all these SP's.
Russels suspicion about how the where clause is being evaluated, is
along the lines we have found out as well based on the execution plan
for the query. I don't know if we can rely on that, but the execution
plan also indicates that when we are using a variable, all the rows from
the Oracle table is being retrieved and then the filter is being applied
on the SQL server side. This still doesn't help us a lot because it's
not that easy to rewrite the code and also it puzzles me why it has
happened in the first place. Especially because we have now set up a new
server with the same SQL server version/SP level as the affected server
had before we applied SP4, but that still causes the same problem.
I'll look into some of your ideas/suggstion tomorrow to see if anything
can point us in he right direction.
--
Regards
Steen Schlüter Persson
Database Administrator / System Administrator