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.