0

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

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.

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.

The Openquery is being run with pretty much god permissions on the Microsoft SQL Server and Oracle boxes.

Provider: Oracle Provider for OLE DB

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.

Possible thoughts:

  • Openquery is not accessing either indexes, statistics or keys when passing the query to Oracle.
  • The driver/connection manager is causing the issue. We tried an ODBC driver with no success.
  • 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.

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.

Any help on this would be appreciated and if you need any more info then please just ask.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Danny McGreevy
  • 121
  • 7

1 Answers1

0

I used to do similar things with SQLServer 2008 and Oracle 8. I never got good performance out of it.

The queries ran fine on Oracle - after all, Oracle is running them, not the driver. What happens is that network round trips kill it. The path the query and data take is:

  1. Client sends query to SQLServer
  2. SQLServer sends query to Oracle Server
  3. Oracle Server executes query.
  4. Oracle Server sends query to SQLServer
  5. SQLServer sends query to client

I think that step 5 doesn't actually start until all the data has been received from step 4 - which makes sense when you think it may be being used in a join with SQLServer tables.

Anyway, you have three network trips for the data instead of one, plus the last trip not completing until the second is complete.

Also, if the full result set is held on SQL Server then it will be using more memory than a query usually does. SQL Server will start sending data to the client as soon as it is available. You can see this in SSMS if you run a query which takes more than a few seconds: the results get shown whilst the query timer in the status bar at bottom right is still running. So the SQL Server may be having to assign extra memory causing possible paging etc etc.

It's a fairly useful feature, but I've never seen it be fast.

simon at rcl
  • 7,326
  • 1
  • 17
  • 24
  • Hi Simon, thank you for your response. The reason i raised this is because I've never seen openquery perform so badly compared to "natively" run SQL. – Danny McGreevy Apr 30 '15 at 15:07
  • .....me and the team are perplexed as to the disparity in performance. The performance we have had thus far has been good from openquery. This one is puzzling us. Any further titbits would be most welcome. Thanks Danny – Danny McGreevy Apr 30 '15 at 15:09
  • I don't really have much more to contribute. The only times I've done it are a couple of one-offs where it was easier than the alternatives, and once when I tried to join a set of Oracle tables to a set of SQL tables as a long-term solution to a requirement. In the end I didn't use it as it was too slow. I don't remember the one-offs being particularly slow, but then I only ran the once or twice. You could try putting the SQL Profiler on when running it and see what happens when; you've obviously done something similar with the Oracle end: tying them up together might help. Cheers - – simon at rcl Apr 30 '15 at 15:17