February 12, 2013 at 11:07 am
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
February 16, 2013 at 6:04 pm
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.March 5, 2013 at 6:07 pm
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.
March 22, 2013 at 2:58 pm
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/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply