SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Remote Query to Oracle very slow


Remote Query to Oracle very slow

Author
Message
newbieuser
newbieuser
SSC Eights!
SSC Eights! (839 reputation)SSC Eights! (839 reputation)SSC Eights! (839 reputation)SSC Eights! (839 reputation)SSC Eights! (839 reputation)SSC Eights! (839 reputation)SSC Eights! (839 reputation)SSC Eights! (839 reputation)

Group: General Forum Members
Points: 839 Visits: 915
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
PaulB-TheOneAndOnly
PaulB-TheOneAndOnly
SSCertifiable
SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)

Group: General Forum Members
Points: 5343 Visits: 4639
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.
john.arnott
john.arnott
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1968 Visits: 3059
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.
Welsh Corgi
Welsh Corgi
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10152 Visits: 4894
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/
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search