I've got a customer with an application that's based on microsoft access 2003 and has a backend that stores data of sql 2008 r2. It has performance problems which I can confirm, but as I'm not an access specialist, here's my question: Is there something like settings to tune in ms access to speed up data access?
Here's the background:
- Database size is ~5 GB, SQL Server has 32 GB of memory
- I've checked and corrected the obvious: missing primary keys, missing indexes
- SQL Server is tuned as far as I know: Optimize for adhoc workload (97% is adhoc), data files, log files, backup files and tempdb are on different drives and raid arrays, sql server is configured to consume 28 GB of ram
- SQL Server is running constantly at or below 5% CPU, disk IO is always close to 0, disk queue length is 0, sometimes 0.05. network IO is sometimes close to 25% outgoing, but normally 4-5%
Here's the problem:
If I check profiler some queries have up to 350000 ms to complete when coming from ms access. If I run the same queries again from ssms, I'm in the range of ~100 ms. Queries returns up to 20000 rows, which are total up to 3 MB in size. Even if I add some round trip times to the total time, I'm nowhere close to 350 seconds. I see also lots of ASYNC_NETWORK_IO waits which are coming from ms access.
For me it seems that access cannot receive data fast enough, even if sql serevr could send it faster.
Many thanks in advance
Edit: I've forgot to mention that data access provides is SQL serevr native client 10