Very Computer

by **Tom Dierick** » Tue, 13 Apr 1999 04:00:00

ANY views (or queries in DTS) connecting to a Linked Server (via ODBC) are
painfully S-L-O-W!!! It's as if the linked tables have no indexes!! Yet,
from within Ms-Access, the linked tables (via ODBC) to the Sybase database
are quick??

We use ODBC to connect to a Sybase System 11 Data Warehouse. I THOUGHT we
wanted to migrate to SQL Server 7.0, but not if it's going to be like this!!

For example,
SELECT *
FROM [LINKED SERVER].DATABASE.dbo.TABLE as TABLE
WHERE IDNUM = '555555555'

freezes SQL Server up, But:

SELECT *
FROM dbo_TABLE
WHERE IDNUM = "555555555"

in MS-Access retrieves the correct record in 2 seconds!!!!!

by **Gert Draper** » Tue, 13 Apr 1999 04:00:00

Use the OpenQuery, that way it will be passed through query and it will use
the index. You are retrieving the complete table to SQL Server and filter on
that side, causing a complete scan on the Sybase side.

The ODBC driver does not allow you to use indexes directly because of
limitations in the OLE DB Provider for ODBC Drivers. If you would use a
native OLE DB Provider towards Sybase which implements the IDBSchemaRowset:
INDEXES rowset and IRowsetLocate on base table rowset.

Cohesion Software
No live without an edge.

Very Computer

Linked (Sybase) Server (via ODBC) S-L-O-W

Linked (Sybase) Server (via ODBC) S-L-O-W

Linked (Sybase) Server (via ODBC) S-L-O-W