Performance tuning tipps

  • Hi there

    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

    Christian

    Edit: I've forgot to mention that data access provides is SQL serevr native client 10

  • Is this a database that has been in use for some time? And was it originally upsized from Access to SQL Server? Finally, is this a .mdb format Access application or a .adp application?

    Assuming that is is the .mdb format, and and using ODBC linked tables, databases of the size you are dealing with usually are better off linking to SQL Server views rather than trying to run multi-join queries from Access. The problem with complex join queries is that Access tries to bring all the entire tables across to the workstation. For simple table views and for one or two join queries, the ODBC driver will translate the query into T-SQL and return just the desired records. Another option if you need to do dynamic queries is to use Pass-Through queries. That's a subject that can cover several pages, but the basic idea is that you write a query in T-SQL and it is saved that way and passed to SQL Server.

    If you are running a .adp application, the performance should be equivalent to what you see on the SQL Server side, as that method uses an OLE DB connection and runs sprocs and views in native mode.

    Post back with some of the details, and hopefully we can help you boost the performance.

    Wendell

    Wendell
    Colorful Colorado
    You can't see the view if you don't climb the mountain!

  • Hi Wendell

    Thanks for your input, I'll clarify this, as this access db was not written by us, we just provide the infrastructure and the db server. I'll come back as soon as I have find out.

    Regards, Christian

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply