July 13, 2006 at 7:10 pm
Hi,
I got a linked server to Oracle.
If I run a SELECT statement using OPENQUERY, it took 14 mintues to return 2000 records.
Well, if I run the same SELECT statement in PL/SQL Developer, it took less than 30 seconds.
Does anyone know where is the possible bottleneck?
Thanks.
Leo
July 17, 2006 at 8:00 am
This was removed by the editor as SPAM
July 18, 2006 at 9:55 am
you need to be careful with how queries across linked servers deal with the data. It is possible that in your linked server environemnt the entire Oracle table is being brought to sql server before executing the query. Try it with a rpc call instead.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
July 28, 2006 at 4:08 am
Hi Colin,
I have this exact problem and would like to try rpc but don't know how to. I have a select statement using openquery. It queries an oracle view using a linked server in SQL Server. How do I re-write this to be rpc?
Many thanks,
Paula
July 28, 2006 at 9:32 am
sorry I don't have an answer regarding oracle - an rpc call is where you return the result set as a procedure call - e.g.
insert into mytable
exec remoteserver remoteproc
now do sql stuff with mytable
Allan Mitchell ( from UK SSUG ) did some good presentations on this sort of thing which is how I remember it.
If you go to http://www.sqlserverfaq.com and search for linked servers you'll Allan's article called don't get bitten by linked servers. This should help explain.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
November 12, 2008 at 6:08 pm
Hi,
I have same problem has anyone resolved it.
Thanks
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply