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