Linked server query performance

Question

I am trying to run a query that retrieves data from remote oracle server and then checks if the data retrieved already exists in my database then I will remove it from results.

SELECT DISTINCT 
            col1,
            col2, 
        col3, 
        col4 , 
        col5, 
        col6 , 
        col7, 
        col8 , 
        col9 , 
        col10, 
        col11
        FROM remoteserver.tab1 dist
        JOIN remoteserver.tab2 headers
        ON headers.id1 = dist.id2 
        JOIN remoteserver.tab3 vendors 
        ON headers.id1 = vendors.id3
        JOIN remoteserver.tab4 comb
        ON dist.id2 = comb.id4
          where
    cond1 and cond2 and cond3 and cond4
   SELECT DISTINCT
           col1, 
           col2,
           col3,
           col4,
           col5,
           col6,
           col7,
           col8,
           col9,
          col10,
          col11
   FROM myserver.tab1

This is just an elaboration about the size of the query I am running, the problem is that it takes very long time (around 20 minutes!) to get results. Any suggestions on how to improve performance or achieve same logic using different approach from linked server?

@Blam Yes, This is the query which takes 20 minutes to return results! — Mo T, Oct 08 '13 at 19:16
There are two select statements there. The answer is only as good as the question. Post the problem select and number of rows returned. — paparazzo, Oct 08 '13 at 20:48

score 0 · Answer 1 · answered Oct 08 '13 at 21:04

I solved the problem using openrowset, which highly improved performance.

select * from (linkedserver,
'SELECT DISTINCT 
            col1,
            col2, 
        col3, 
        col4 , 
        col5, 
        col6 , 
        col7, 
        col8 , 
        col9 , 
        col10, 
        col11
        FROM remoteserver.tab1 dist
        JOIN remoteserver.tab2 headers
        ON headers.id1 = dist.id2 
        JOIN remoteserver.tab3 vendors 
        ON headers.id1 = vendors.id3
        JOIN remoteserver.tab4 comb
        ON dist.id2 = comb.id4'
          where
    cond1 and cond2 and cond3 and cond4
   SELECT DISTINCT
           col1, 
           col2,
           col3,
           col4,
           col5,
           col6,
           col7,
           col8,
           col9,
          col10,
          col11
   FROM myserver.tab1

Linked server query performance

1 Answers1