January 26, 2018 at 7:58 am
If a client Access database links to SQL Server tables does it change the performance?
Thanks
Val
January 26, 2018 at 8:05 am
That entirely depends on what the access database is doing with them, but yes potentially.
January 26, 2018 at 8:11 am
I wouldn't expect an impact on CPU, but I would expect it to negatively impact practically every other aspect of your SQL Server instance in a negative way. The main problem with remote table joins is that it causes full table scans and client side row-by-row cursor processing, especially when the table is being joined with remote client side data using a tool like MS Access. What MS Access does with linked tables is pull all the rows client side and perform the filtering and processing there. So, on the source server I'd expect you'll see increasing I/O reads, escalated locking, and network traffic. It could potentially cause CPU spikes as well. Keep at eye on it and see what it's doing.
I'd recommend you create stored procedure to serve the application the data it needs. You want row filtering to be parameter driven and processed server side.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
January 26, 2018 at 8:20 am
If the MS Access developer really knows what they are doing, they can reference linked tables in a way that doesn't hit your server hard. Then again, what percentage of MS Access user base really know what they are doing? If MS Access is a self-serve BI tool for non-IT users, allowing them free reign to build reports against the production database, then the DBA is in for some trouble.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
January 26, 2018 at 4:00 pm
I'm trying to persuade them not to use linked tables but write a query and return a recordset (via ADO). I suspect that Access treats linked tables as dynasets (that's what it's doing with its own tables) significantly increasing network traffic. And risk to change data in the server table. Especially with big tables.
Thanks to everybody
January 26, 2018 at 5:08 pm
I had some Access users who were downloading the entire 120 million rows from a table just to get 100+ rows of data out of it. They did everything right - the queries were perfect with a very narrow date range - except, the WHERE clause didn't get passed to SQL Server. After they changed to use pass-thru mode, they get their results within a few seconds.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply