link table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

link table

Author

Topic

k_sekhar_rao
Starting Member

8 Posts

Posted - 2003-05-20 : 00:39:10

Does anybody has tried out Microsoft access link table (option) to link Oracle tables using ODBC connection? Did you face any performance issue?

Thanks in advance,
sekhar

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-05-20 : 07:52:46

With any ODBC link, make sure you send as much filter information and/or do as much grouping as soon as possible when you access the table, and before joining to any local access table. If you are careful, you'll have great performance.

Also, don't include any non-ODBC compliant or VBA-only functions on the data UNTIL it is fully filtered and summarized.

Things to do EARLY:

1) Joins between ODBC tables from the same datasource -- Access will convert them to 1 big SQL statement (usually -- experiment)
2) GROUP BY and use aggregate functions as ealry as possible on the ODBC tables
3) FIlter as much as possible RIGHT AWAY when you access ODBC -- always filter the LARGEST POSSIBLE tables as well.*

Things to do at the LAST POSSIBLE MOMENT:

1) VBA functions (custom or otherwise)
2) Joins to local Access Tables
3) Formatting

Minor tweaks in how you get your data can make HUGE differences! This applies to all ODBC datasources and linked tables from Access in general. Follow the above guidelines and you'll be in pretty good shape. Access actually does a fairly good job of sending SQL Statements to ODBC efficiently.

--------------------------

* If you have a query like this:

Select C.CustomerName, S.Sales
FROM Customers C
INNER JOIN Sales S
ON C.Customer=S.Customer
WHERE C.Customer = '1234'

it can really help to do this (surprisingly) :

Select C.CustomerName, S.Sales
FROM Customers C
INNER JOIN Sales S
ON C.Customer=S.Customer
WHERE s.Customer = '1234'

because this guarantees access will filter the larger table (Sales) first, just in case it does the join locally.

Let me know if this is helpful.

- Jeff

Edited by - jsmith8858 on 05/20/2003 08:56:04

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources