none
Slow Query Using MS Access Front End RRS feed

  • Question

  • Hi,

    I'd appreciate some help in troubleshooting an issue I have found when using Microsoft Access 2010 as a Front End to an Azure SQL Database.

    The Access database was build to a backend .accdb file which does work fine with fast query performance.

    I have used the Microsoft Sql Server Migration Assistant for Access to upscale the accdb file to a local SQL 2012 Express install which I then created a script to export the Schema and Data to import to a database that I created as a Windows SQL Azure Database. ( I realise the local SQL step is not necessary, but I wanted a local copy to test too.)

    I created an ODBC DSN to the Azure database, re-linked the tables to the Azure DSN and found the performance of the Access Front End to be very slow in most operations. I picked a particular query to work with and found that it takes around 15 seconds to return the data as a select query.

    If I change the query to a Pass-Through then the result is returned instantly. I can observe the same result by running the query in SQL Server Management Studio.

    I created a new DSN to the local SQL Express install and tested the performance of the Access Front and found the performance of the slow example query to be instant.

    I'm now stuck as to where to go;
    1) Access Pass-Through query/Management Studio query is quick - proving SQL Azure Performance
    2) Access Select Query to local SQL Express 2012 is quick - proving MS Access query design/Jet performance
    3) Access Select Query to SQL Azure Performance is slow taking around 15 seconds.

    This leads me to the fact that something is slowing down MS Access to SQL Azure, but why would the behaviour be any different to a local SQL Express install when both are connecting through a DSN?

    I have tried creating both Web and Business Edition databases to the same effect.

    Any ideas or suggestions gratefully recieved,
    Thanks,
    Paul

    Monday, November 11, 2013 11:23 PM

All replies

  • There is nothing wrong with your Azure SQL Database. It is working exactly as expected, it just takes longer to get data that way. The network or internet is the bottleneck that is causing the slow performance. Plus regular Access Queries are often not optimized for use with the SQL Database and can result in very poor performance. You cannot expect to use an Azure SQL Database or SQL Server database that is not local that same as you would using a local back ends whether it be Access or SQL Server.

    But you can improve the performance when you learn more about Access and SQL Databases. Mainly, you should never load all the records in a Form's RecordSource. You can usually only work on one record at a time and that is all you need. If you want to link to something it is best both for performance purposes and security to link to SQL Views that you create which are similar to Queries. You can use a Where Clause in Views to limit the number of records that are displayed.

    I normally manage data by using the fast and powerful SQL Stored Procedures to which allows you to supply parameters and manage data by calling the SQL Stored Procedure using a Pass-through query in code. Many prefer to use ADO. Once you learn how to do these things working with a SQL database using Access becomes much easier.

    I created a Developer Center for those who use Access with Azure SQL Databases that can be very helpful. http://gainingaccess.net/SQLAzure/AccessAndSQLAzureInfo.aspx  


    Patrick Wood,
    Founder, Gaining Access Technologies http://gainingaccess.net/


    Tuesday, November 12, 2013 1:41 AM
  • Thanks Patrick.

    I will take some time to look through your site which seems to have a lot of helpful information.

    I still cannot get my head around the performance problem though!

    Running exactly the same query;
    In SQL Server Management Studio to Azure Database = Fast
    In Access connected to local SQL Express = Fast
    In Access connected to Azure SQL = Slow

    If the problem was the speed of the connection to Azure this would surely show in SQL Server Management Studio..

    If the problem was Access to SQL then this would surely show connected to local SQL Express..

    As both of these are OK, why is Access to Azure slow??

    Thanks,
    Paul

    Wednesday, November 13, 2013 12:48 AM