CODEWITHSUNDEEP
sqlsql-server
w0051977
w0051977

Reputation: 15817

Oracle Linked Server too slow

Say I have a query like this:

create table #Incident (id int)
insert into #Incident values (1)

    SELECT INC_EVNT_URN,INC_CR_DTE FROM OPENQUERY(incidents,
    'select * from incident')

Say incident has 800 million records in it and I just want to get a one of them. #Incident is stored in an SQL Server table. The Incidents linked server is an Oracle server.

I cannot do this:

select * from #Incident INNER JOIN (
SELECT ID FROM OPENQUERY(incidents,
'select * from incident')
) AS Incident ON #Incident.ID=Incidentid

The reason I cannot do this is because it takes about one day to run because it first gets all the incidents from the Oracle database before finding the one that I require. Is there a way to refactor the query to make it more efficient?

Upvotes: 0

Views: 1257

Answers (1)

user2858650
user2858650

Reputation:

This is actually less of an Oracle issue than a SQL Server one. OPENQUERY runs the SQL script identified in the second parameter directly against the linked database and returns the results. So "linked server" is really kind of a misnomer as you are really just making an isolated database call rather than referencing a foreign object in your current query plan. So every time you reference 

OPENQUERY(incidents,'select * from incident')

you are pulling in all 800 million records of that incident table into the cache and then running your SQL Server query against that enormous temp table.

To avoid this you are going to want to narrow down the query that you send to the Oracle Server so that the Oracle parser can run a cleaner plan. Check out this link which describes passing variables into an OPENQUERY.

Pass Basic Values

When the basic Transact-SQL statement is known, but you have to pass in one or more specific values, use code that is similar to the following sample:

DECLARE @TSQL varchar(8000), @VAR char(2)
      SELECT  @VAR = 'CA'
      SELECT  @TSQL = 'SELECT * FROM OPENQUERY(MyLinkedServer,''SELECT * FROM pubs.dbo.authors WHERE state = ''''' + @VAR + ''''''')'
      EXEC (@TSQL)

Using this information, I would try passing in the incident ID that you are searching for. Then all of the work is happening in Oracle and you are only pulling back the data you need (as opposed to the entire table).

Upvotes: 1

Related Questions