Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Remote Query to Oracle very slow Expand / Collapse
Author
Message
Posted Tuesday, February 12, 2013 11:07 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, April 23, 2014 3:06 PM
Points: 319, Visits: 757
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

Post #1419124
Posted Saturday, February 16, 2013 6:04 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 8:15 AM
Points: 3,068, Visits: 4,639
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



Please do...

1- Connect to the Oracle database and get an execution plan of the query on the Oracle side.
2- Execute query directly on Oracle and compare timing with what you get executing it remotely.
3- Ask Oracle DBA to trace/tkprof the remote query, then compare traced execution plan with the on you got on step #1

Thank you.


_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
Post #1420937
Posted Tuesday, March 5, 2013 6:07 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, August 28, 2014 8:53 PM
Points: 1,521, Visits: 3,039
Just guessing here, but this sounds a lot like what we found trying to select data from a Netezza data appliance (Netezza is a competitor of the better-known Teradata). It was taking f o r e v e r to get 180,000 rows back from a 3.5 million row table. Since Netezza is normally very fast for queries even with multi-table joins, it looked as though the trouble was in the way the MS SQL box and Netezza talked and matched data for the join, apparently sending data back and forth across our WAN. We ended up punting by establishing a new key table on Netezza, sending the 180,000 relatively small rows over en masse and doing the join entirely on Netezza. There may have been a more elegant solution, so if you've found one for your Oracle connection, I'd love to hear it.

Post #1427102
Posted Friday, March 22, 2013 2:58 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 7:58 AM
Points: 4,235, Visits: 4,282
For Remote Server whether it be Oracle AS400 DB2 or whatever the Linked Server can be terribly slow.

Depending upon the amount of data you are pulling it may be faster to pull the Data into a SQL Server Staging table and then JOIN with the SQL Server Tables in an EXEC Task.

I had a Contractor that was trying to load a 13 million record table using a linked server.

I tried to convince him to avoid using the Linked Server.

He attempted to the table all week and it did not finish.

Finally I created an SSIS Pacakge and I loaded the table in just over an hour.


For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1434520
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse