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
– Mo T, Commented Oct 8, 2013 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
– paparazzo, Commented Oct 8, 2013 at 20:48

Mo T · Accepted Answer · 2013-10-08 21:04:51Z

0

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

answered Oct 8, 2013 at 21:04

Mo T

4602 gold badges10 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Linked server query performance

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related