Forum Discussion
Improve performance Access frontend with Azure backend
I have the following question. I have migrated a "traditional build" Access frontend application with a SQL server backend to Azure SQL. The linking goes fine, however the performance often leaves...
> however the form is not updateable any more.
That is probably because you used a PT query. They are never updatable. Rather you would select 1 row from the linked table or a query based on a linked table.
Thank you for your help Tom, data caching is definitely something I will dive into.
Two other things that can improve performance are NOT to bind forms and reports directly to tables, and making sure that all tables are appropriately indexed.
Part of the performance problem with a remote back end like Azure is that calls to the database for data go out over the internet. Pulling large datasets across that connection is inefficient and slow. Therefore, you want to minimize the number and size of those recordsets. That's where local caching shines. Also, if you want to edit a single record, use a form bound to a query with a filter that returns only that one record. Obviously, efficient indexing can make tasks like filtering and sorting faster. That is probably more of an art to get it right, but it should be included in your preparations.
Part of the performance problem with a remote back end like Azure is that calls to the database for data go out over the internet. Pulling large datasets across that connection is inefficient and slow. Therefore, you want to minimize the number and size of those recordsets. That's where local caching shines. Also, if you want to edit a single record, use a form bound to a query with a filter that returns only that one record. Obviously, efficient indexing can make tasks like filtering and sorting faster. That is probably more of an art to get it right, but it should be included in your preparations.
