newbieuser (2/12/2013)

---

Hi friends,

We are troubleshooting performance on a process that is taking too long. I ran a trace using sql server profiler and it looks like a select query is causing the slowness.. Below select is a join to Oracle remote table using linked server.

SELECT localtab.field1 FROM LINKEDSERVER..REMOTEUSER.TABLE remtab, localtab 
WHERE ((remtab.rfield1 = ''value'' OR remtab.rfield1 = ''value'' ) 
OR (remtab.rfield2 = ''value'' ))
 AND remtab.rfield3 = localtab.rfield3'

execution plan:

select cost: 0 %
MErge join (inner join) Cost: 6 %
Compute Scalar Cost:0 %
Remote Query Cost: 85 %
Index Scan [localdb].[dbo].[localtab].[rfield3.idx] Cost: 10 % (non clustered)

I analyzed the query in tuning advisor. There are no recommendations for this sql.. not sure if it is because it's remote table.

Below are few lines with the highest duration from the sql server profiler trace when the process ran.
There are about 25000 records in both the local and remote tables. Process runs this select query in a loop with application logic and other sqls inside the loop. Other sql's seem to execute faster
with less duration in the profiler trace. Every occurrence of this select query has high duration time. Select query has bind variables in the process code..

TextData:

declare @p1 int
set @p1=1602323
declare @p3 int
set @p3=1
declare @p4 int
set @p4=1
declare @p5 int
set @p5=1
exec sp_cursoropen @p1 output,N'SELECT localtab.field1 FROM LINKEDSERVER..REMOTEUSER.TABLE remtab, localtab WHERE ((remtab.rfield1 = ''value'' OR remtab.rfield1 = ''value'' ) OR (remtab.rfield2 = ''value'' )) AND remtab.rfield3 = localtab.rfield3'',@p3 output,@p4 output,@p5 output
select @p1, @p3, @p4, @p5

Duration: 382
CPU: 0
Reads:49
Writes:0

TextData:

exec sp_cursorfetch 1602323,2,1,1

Duration:302
CPU:15
Reads:7
Writes:0

TextData:

exec sp_cursor 1602323,40,1

Duration:290
CPU:0
Reads:7
Writes:0

TextData:

exec sp_cursorclose 1602323

Duration:0

Please help..THanks a lot