[dba-SQLServer] Linked Server - Stored Procedure Performance issue

HELLO ALL,

Summary: Pass through query in Access using DSN (Oracle is faster then
Linked Server in SQL Server using Stored procedure).


I currently have an application that pulls data from our ERP system
which is in Oracle 9i, Net8 client.  The current application has an
Access Front end (A2k2) and a SQL server back end.  What I have been
trying to do over the last 2 days is convert all PassThrough queries,
into stored procedures.  So far this has worked beautifully, and
eliminated a ton of code which I had developed to dynamically alter the
where clause and now only have to pass in a parameter for a stored
procedure.  

Anyway, I ran into a stumbling block.  The above approach helped in both
simplicity and speed for all information stored in SQL server.  However
the information stored in Oracle hasn't gone so easy.  I set up a linked
server, so that all authentication will come from the server and won't
have to worry about adding a new user, and having to make sure they have
access to the DNS.  Anyway, the current pass through query will run in
roughly 1-2 seconds.  When converted to a procedure to use the linked
server, it jumps up to 1 minute 47 seconds.  The fastest I could get it
down to is 1 minute 30 seconds.

Any tips/tricks/criticism welcome... 

Thanks in advance for any help to solve this issue.

Here are the basics of what I have tried...


======================================================================
ACCESS PASS THROUGH (1-2 seconds) connect via Oracle ODBC
SELECT A.ORDER_NUMBER, A.ORDER_LINE_NUMBER, B.PART_CODE, B.PART_DESC_1,
c.UNIT_OF_MEASURE , c.ORDER_QUANTITY FROM fin_Prod.SALES_ORDER_LINES A,
fin_Prod.PRODUCT_MASTER B, fin_Prod.SALES_ORDER_LINE_QTYS C Where
(A.COMPANY_CODE = B.COMPANY_CODE) And (A.ORDER_LINE_NUMBER =
C.ORDER_LINE_NUMBER) AND (A.ORDER_NUMBER = C.ORDER_NUMBER) AND
(A.DIVISION = C.DIVISION) AND (A.COMPANY_CODE = C.COMPANY_CODE) AND
(A.PART_CODE = B.PART_CODE) AND (A.Unit_Sales = c.UNIT_OF_MEASURE)  And
A.ORDER_NUMBER = '310376' AND C.ORDER_NUMBER = '310376'

======================================================================

======================================================================
SQL Server Stored Procedure(1) sql(1min 47 seconds) uses RENCS linked
server
SELECT 
A.ORDER_NUMBER, A.ORDER_LINE_NUMBER, 
B.PART_CODE, B.PART_DESC_1, 
c.UNIT_OF_MEASURE , c.ORDER_QUANTITY 
FROM 
RENCS..FIN_PROD.SALES_ORDER_LINES A, 
RENCS..FIN_PROD.PRODUCT_MASTER B, 
RENCS..FIN_PROD.SALES_ORDER_LINE_QTYS C
Where 
A.ORDER_NUMBER = '310376' AND C.oRDER_NUMBER = '310376' AND 
(A.PART_CODE = B.PART_CODE) AND (A.COMPANY_CODE = B.COMPANY_CODE) And 
(A.ORDER_LINE_NUMBER = C.ORDER_LINE_NUMBER) AND 
(A.ORDER_NUMBER = C.ORDER_NUMBER) AND 
(A.DIVISION = C.DIVISION) AND 
(A.COMPANY_CODE = C.COMPANY_CODE) AND 
(A.Unit_Sales = c.UNIT_OF_MEASURE) 
======================================================================

======================================================================
SQL Server Stored Procedure (2) sql (1min 30 seconds) use RENCS linked
server


SELECT     A.ORDER_NUMBER, A.ORDER_LINE_NUMBER, B.PART_CODE,
B.PART_DESC_1, C.UNIT_OF_MEASURE, 
                      C.ORDER_QUANTITY
FROM         
OPENQUERY(RENCS, 'select ORDER_NUMBER, ORDER_LINE_NUMBER, COMPANY_CODE,
PART_CODE, 
DIVISION, Unit_Sales from fin_Prod.SALES_ORDER_LINES') A 

LEFT OUTER JOIN

OPENQUERY(RENCS,'select UNIT_OF_MEASURE, ORDER_QUANTITY, 
ORDER_LINE_NUMBER, ORDER_NUMBER, COMPANY_CODE, DIVISION from
fin_Prod.SALES_ORDER_LINE_QTYS') C
ON 
A.ORDER_NUMBER = C.ORDER_NUMBER AND A.ORDER_LINE_NUMBER =
C.ORDER_LINE_NUMBER AND 
A.COMPANY_CODE = C.COMPANY_CODE AND A.DIVISION = C.DIVISION AND 
A.UNIT_SALES = C.UNIT_OF_MEASURE 

LEFT OUTER JOIN

OPENQUERY(RENCS, 'select PART_CODE, PART_DESC_1,COMPANY_CODE from
fin_Prod.PRODUCT_MASTER') B 
ON 
A.COMPANY_CODE = B.COMPANY_CODE AND A.PART_CODE = B.PART_CODE

WHERE     (A.ORDER_NUMBER = '310376') and (c.ORDER_NUMBER = '310376')
======================================================================


Rich