Queries Against Linked Server Time Out?

Ok, this is a little convoluted so bear with me.

I have a SQL 2000 SP4 box on Windows 2000 Server with the Oracle 11g Client installed.

I've created a linked server to an Oracle database that lives offsite (sites connected with a fairly high speed VPN link).

There's a view on the Oracle database I'm trying to access through the linked server using the four-part naming convention*.

If I execute a fairly simple query against that view, the query runs until it eventually times out two hours later. And by simple, I mean something like this:


select * from CGN_LMS..CGNLMS.V_PRODUCTION_SUM where FISCAL_PERIOD = 200803

That happens if I use the OLE DB provider from Oracle or the one from MS.

Now, if I run the same type of query against that Oracle DB from Excel or Access (using the ODBC driver), the query will complete in seconds, returning about 800 rows of data.

For fun, I created a blank Access database with one linked table that points to that V_PRODUCTION_SUM view on the Oracle database, and one Query within that Access database that SELECTs * FROM the linked table WHERE FISCAL_PERIOD=200803.

I then created a linked server on my SQL 2000 server that points to the Access database. Now, if I hit that saved Access query through the SQL 2000 linked server, it also returns the results in about 9 seconds. If I query the linked table in the Access database and specify WHERE FISCAL_PERIOD = 200803, it completes in about 6-8 minutes.

So I guess my question is... WTF?

How is it that this:


SQL 2000 ->OLE DB provider-> Oracle

Times out after 2 hours, while this:


SQL 2000 -> Jet driver-> Access -> ODBC driver -> Oracle

returns all my results in seconds/minutes?

Couple other side notes...

A lot of the queries that I test with using this linked server work just fine. In fact, with this particular query, if I were to just pull SELECT TOP 800 * with no WHERE clause, the query would run rather quickly (like in 4 seconds).

Also, if I use OpenQuery instead of the 4-part linked server name, the queries return results as quickly as I'd hope. That might work as a last resort, but we have existing applications that use the 4-part names already and we'd rather not have to suss out and change all those if we don't have to.




Any help or insight would definitely be appreciated.

Justin