Let's say we have one SQL Server instance on one side:
- SQL Server 2008 R2 SP3
- Oracle 11.2 client installed with OLEDB Drivers for ODBC
.. and an Oracle database instance on another side:
- Oracle Database 18.104.22.168 PSU 24
- InstanceName: MERCURE
I created two database links on the SQL Server instance:
- MERCURE (using ODBC Server DSN and MSDASQL Provider)
- MERCURE_OLE (using OraOLEDB.Oracle)
On the Oracle instance (with instance name
MERCURE), I created a schema and table as follows:
CREATE USER JEL_PERF_TST identified by "XXX"; GRANT CONNECT,RESOURCE,UNLIMITED TABLESPACE to JEL_PERF_TST; ALTER SESSION SET CURRENT_SCHEMA=JEL_PERF_TST; -- select DEFAULT_TABLESPACE from dba_users where username='JEL_PERF_TST'; -- USERS -- create a table for testing (Taken from https://use-the-index-luke.com/sql/example-schema/oracle/3-minute-test) CREATE TABLE TestTbl ( id NUMERIC NOT NULL, date_column DATE, a NUMERIC, b NUMERIC, text VARCHAR(255), state CHAR(1), PRIMARY KEY (id) ); INSERT INTO TestTbl SELECT level , SYSDATE - level , MOD(level,1234) , TRUNC(DBMS_RANDOM.VALUE(1, 10)) , DBMS_RANDOM.STRING('l', 20) , DECODE(MOD(level, 5), 'X', 'A') FROM dual CONNECT BY level <= 50000;
So, basically, I have 50.000 rows in my table. Now, let's run the following queries on SQL Server instance:
First, let's activate statistics:
set statistics io on; set statistics time on;
Then, run a first query using
SELECT * FROM [MERCURE_OLE]..[JEL_PERF_TST].[TESTTBL];
Here is what I got:
SQL Server parse and compile time: CPU time = 32 ms, elapsed time = 274 ms. (50000 row(s) affected) SQL Server Execution Times: CPU time = 2199 ms, elapsed time = 5769 ms.
Performances aren't pretty good (more tha 8.5 ms for a single row) but it doesn't matter here. We have 100% of rows returned.
Let's now run the query using OPENQUERY and same Linked Server:
SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 103 ms. (50000 row(s) affected) SQL Server Execution Times: CPU time = 2542 ms, elapsed time = 6716 ms.
Same here, performances are bad but we have 100% of rows returned.
Now, let's run the exact same query, but using MSDASQL provider:
select * from openquery(MERCURE,'select * from JEL_PERF_TST.TestTbl');
It results in:
SQL Server parse and compile time: CPU time = 15 ms, elapsed time = 107 ms. (**49901** row(s) affected) SQL Server Execution Times: CPU time = 1357 ms, elapsed time = 3932 ms.
Timing is better, but where are my 99 missing rows ??
I've tried many changes to my ODBC connection descriptor (Enable/disable result sets, raise the Fetch Buffer Size, Use MSDTC or not, etc.). Nothing worked.
Any clue ?