J
Jon M
I manage a SQL Server data warehouse and my customers use Access to create
ad-hoc queries of the data. A customer is creating a local table in Access to
store some 200-300 serial numbers and then joining that to a remote table
that's linked in via ODBC that contains 1,000,000+ records. If I perform the
query using SQL Server it returns in under 2 seconds (there is an index on
the serial number field) but if I mimic his query it takes forever (cut it
off after 20 minutes or so). I suspect that it's sending down all 1,000,000+
records and letting the local computer process the query. Is there any way in
Access to force the query to process on the server? The database is a
read-only data warehouse so making a table on the server for him to load with
the records he's searching for isn't an option. Thanks!
ad-hoc queries of the data. A customer is creating a local table in Access to
store some 200-300 serial numbers and then joining that to a remote table
that's linked in via ODBC that contains 1,000,000+ records. If I perform the
query using SQL Server it returns in under 2 seconds (there is an index on
the serial number field) but if I mimic his query it takes forever (cut it
off after 20 minutes or so). I suspect that it's sending down all 1,000,000+
records and letting the local computer process the query. Is there any way in
Access to force the query to process on the server? The database is a
read-only data warehouse so making a table on the server for him to load with
the records he's searching for isn't an option. Thanks!