All,
I have reviewed a lot of posts but can't seem to find the answer to this one:
I have a SQL 05 Enterprise server linked to an Oracle 10g Server to retrieve records from Oracle table (300K rows). The query is a simple select * from tbl openquery. (I know the implications of using * in a query, but bare with me through the explanation...)
1. Initially utilized the MSDAORA provider to link SQL to ORACLE:
Response time: 56 seconds (what???!!!)
2. Changed the provider to ORAOLEDB.ORACLE to link SQL to ORACLE:
Response time: 24 seconds (???!!!)
Here's the crazy thing:
3. Upon submitting the query directly to the Oracle Server, the response time is < 1 second (!!!) Of course I have no direct access to the Oracle server.
4. Upon copying the entire table from the Oracle DB to SQL and submitting the same query locally, the response time is still a very respectable < 2 seconds (!)
This leads me to believe that either I'm doing something wrong; not enabling a correct setting to either of the providers (most probable)
or
Both providers are incredibly inefficient;
MSDAORA adding 55 seconds of overhead
ORAOLEDB.ORACLE adding 23 seconds of overhead
on returning the data from a small 300K row table.
Has anyone encountered a solution to this issue?
Of course this is just the start, as the dataset will be utilized by a parameterized report...
Any help is GREATLY appreciated!
I have reviewed a lot of posts but can't seem to find the answer to this one:
I have a SQL 05 Enterprise server linked to an Oracle 10g Server to retrieve records from Oracle table (300K rows). The query is a simple select * from tbl openquery. (I know the implications of using * in a query, but bare with me through the explanation...)
1. Initially utilized the MSDAORA provider to link SQL to ORACLE:
Response time: 56 seconds (what???!!!)
2. Changed the provider to ORAOLEDB.ORACLE to link SQL to ORACLE:
Response time: 24 seconds (???!!!)
Here's the crazy thing:
3. Upon submitting the query directly to the Oracle Server, the response time is < 1 second (!!!) Of course I have no direct access to the Oracle server.
4. Upon copying the entire table from the Oracle DB to SQL and submitting the same query locally, the response time is still a very respectable < 2 seconds (!)
This leads me to believe that either I'm doing something wrong; not enabling a correct setting to either of the providers (most probable)
or
Both providers are incredibly inefficient;
MSDAORA adding 55 seconds of overhead
ORAOLEDB.ORACLE adding 23 seconds of overhead
on returning the data from a small 300K row table.
Has anyone encountered a solution to this issue?
Of course this is just the start, as the dataset will be utilized by a parameterized report...
Any help is GREATLY appreciated!