title: OpenQuery from SQL Server 2008R2 to Oracle 11g Slow but same query run on the Oracle server is fast
body: <p>Code:</p>
  
  <pre><code>WITH GTransNums
  AS (
  SELECT /*+ INDEX (GTRANS_DEFS_24) */ gtrans_num
  FROM pro.gtrans_defs
  INNER JOIN pro.loc_defs ON (
          loc_defs.loc_num = gtrans_defs.gdest_num
          AND loc_defs.loc_type = '' JLP ''
          )
  WHERE gdest_num != 99999
  )
  SELECT /*+ INDEX (GTRANS_ITEMS_1) */ Gtrans_items.season
  ,Gtrans_items.sty_num
  ,Gtrans_items.sty_qual
  ,Gtrans_items.bf_mat_char_val
  FROM pro.gtrans_items
  WHERE gtrans_num IN (
      SELECT gtrans_num
      FROM GTransNums
      )
  GROUP BY Gtrans_items.season
  ,Gtrans_items.sty_num
  ,Gtrans_items.sty_qual
  ,Gtrans_items.bf_mat_char_val
  </code></pre>
  
  <p>The code pasted above runs very quickly when run directly on the Oracle server but when we wrap this into an Openquery on Microsoft SQL Server it simply hangs.  It pulls back about 40000 rows.</p>
  
  <p>We have assessed the formatting of the Openquery when it hits the Oracle box and all appears to be exactly the same as when you run it directly.</p>
  
  <p>The Openquery is being run with pretty much god permissions on the Microsoft SQL Server and Oracle boxes.</p>
  
  <p>Provider: Oracle Provider for OLE DB</p>
  
  <p>We have created a view from the code on the Oracle box and queried the view via Openquery from Microsoft SQL Server and it was super quick.</p>
  
  <p>Possible thoughts:</p>
  
  <ul>
  <li>Openquery is not accessing either indexes, statistics or keys when passing the query to Oracle.</li>
  <li>The driver/connection manager is causing the issue.  We tried an ODBC driver with no success.</li>
  <li>Some strange networking that is sending the query round the houses.  Can’t test for this as packet sniffers are forbidden on the network.  Both boxes are housed on the same site.</li>
  </ul>
  
  <p>I have found similar threads on this but they all appear to tail off without conclusion.  This is frustrating as I cannot explain why exactly the same query runs remarkably at different speeds.</p>
  
  <p>Any help on this would be appreciated and if you need any more info then please just ask.</p>