Access and Azure SQL Database

Copper Contributor

Hello, I tell you that I have the following problem, I connected my access database with an SQL database in Azure, but the communication is very slow, for example one of my forms takes 15 seconds to open. My database is basic, it has 10 GB and only 10 DTU, but to see if it improved, probe increased to 100 DTUs, but even so, the speed did not improve at all. Finally I tried changing the location of the server, from the Center of the USA to the South of Brazil, (Because I am from Chile) this did produce an improvement passing from 15 to 8 seconds in opening the form, but even so it is very slow and even gets stuck when generating some reports. I even tried Sharepoint and sharepoint the same form opens it in about 5 seconds, but I need access to work with Azure in order to connect my mobile application with access through Azure SQL database. Thank you in advance.

10 Replies

@JoseAntonio110 This is a very well known problem, unfortunately. 

 

Depending on how you designed the Access forms, you MIGHT be able to improve performance by adopting a few practices.

 

One, if you are loading the forms in the traditional "Access way", you are binding the form to a table and applying filters to it to select or navigate to specific records. If that is how this was designed with Access tables, it works well, but when you are pulling records from a remote SQL Server, i.e. from Azure, it is really inefficient, and therefore, slow to do it that way. Instead, all forms should be designed with single record queries. Apply criteria that select and return one record and a time and requery that one-record recordset in order to navigate to other records.

 

A second factor that can make a big difference is having a lot of combo or list boxes on the form. Each of those has its own recordset to pull down from the SQL Azure database, and that also slows things considerably. 


The same is true of subforms. Each subform, in turn, also has its own recordset, and again, that adds to load times.

 

In short, interface design for Access relational database applications linked to remote databases, including SQL Azure, depends on pulling the least possible traffic across the wire from that remote database. 

Focus on that first.

 

Other things that can sometimes help might include using passthru queries whereever possible for rowsources in combo and list boxes. Although they are not significantly better in all cases, Passthrus do tend to perform better.

 

 

Dear,@George Hepworth  thank you very much for the answer, I am very clear.

@JoseAntonio110 

 

Hello Jose - thank you for your post.  I'm experiencing similar issues, but with an MS Access backend on azure with a frontend linked to those tables.  It's not a bandwidth issue because even a small form that is not bound to a table takes upwards of a minute to open up in design view.  (Although it's not recommended, I tried everything on sharepoint and it is very fast - however, that will most likely corrupt the database as it tries to make copies of the file as different users update the db.)

 

At this point, I'm considering setting up a cheap server at the company to host the database backend and put the frontend on a network, locally or even on sharepoint.

 

Have seen similar issues about speed on azure even with MS SQL server, but I can't imagine that to be the case since there must be commercial applications running on it all the time.

 

Thanks,

 

Chris.

Did you optimize your forms to only retrieve single records rather than entire tables? Did you apply dynamic loading to listboxes, combo boxes, subforms, ...? Have you switched to using pass through queries and views wherever possible? Have you done any analysis to validate your indexing of table/fields?

 

Also, nothing will ever be as fast as a web system where both the backend and front-end are on the same system.  Hybrid databases are pushing/pulling the data continually over the Internet, this will always be a relatively slow process.  The true solution is to use proper web technologies:. PHP, .net, MySQL, Azure, ...  I have migrate several Access database to such platforms and the difference is staggering.  It's a question of picking the right to for the job and Access was never meant for anything WAN related.

When you are working with remote databases then you need to work with passthrough queries in order to utilize the power of SQL.

@tsgiannis 

It's certainly true that using Pass-Thru queries can help, at least in part and at least in some aspects, but it's not quite that simple over-all.

For one thing, Pass-Thru queries are read-only in Access. That means they can be effective in retrieving large or complicated recordsets for reports, and for places in forms where you do NOT need to update the recordset. It also means you can't use them for data entry operations without a lot of additional processing, which is generally not all that fun to do.

 

For another, the problem of performance against a remote SQL Server or SQL Azure database is not limited to queries per se. Other kinds of commonly implemented "Old School" Access relational database application designs are problematic when the Accdb BE is replaced by the remote database. Multiple combo or list boxes on a form, for example, and multiple subforms in a form also drag down performance regardless of the use of local or Pass-Thru queries.


While it can be a very effective way to deploy an Access solution for remote users, using a hosted SQL Server or SQL Azure database requires a total rethink of the ways we design the interface.


In short, the goal of "New School" Access relational database application design should be to limit --to the extent possible-- both the absolute number of records in any recordset and the number of recordsets that must be returned. 

@George Hepworth Well there are many solutions and workarounds for this case..passthrough are read only but you can work with unbound forms and push the data to the server.

And there are others...beyond the scope of a simple question

@tsgiannis 

"...but you can work with unbound forms and push the data to the server...."

Right, that's pretty much what I said. "It can be done but it's ugly."

 

If performance becomes a work-stop issue, it's worth pursuing such options, but the amount of additional work required is a serious consideration as well. Besides, in my experience, the use of "heavy" forms laden with combo and list boxes and multiple subforms is a more common source of performance lags. That and the old method of binding a form to a table and then searching through that form's recordsource to find one record, that's a killer in terms of performance against a remote database.

 

Pass-Thrus have a place, along with other design considerations.

There seem to be other considerations that apply here. I had the same problem - started with a complex form that had seven sub-forms and six combo boxes, and upon migrating to Azure it suddenly took two minutes to open the form. I rewrote all the form and subform queries to return single records, and moved the combo box tables back to Access, and that got it down to one minute.
Then I investigated further and found that all the delay was caused by just one of the seven subforms. I checked the subform query and it was something like
SELECT C.R, C.P, M.Q, P.S., C.F, C.S, P.X, P.D, C.E
FROM C LEFT JOIN (M LEFT JOIN P ON M.ID = P.ID) ON C.E = M.E
WHERE (((C.R)='ABC') AND ((C.P)=12345) AND ((C.Rx)='DEF'))
OR (((C.R)='ABC') AND ((C.P)=12345) AND ((M.ID) Is Not Null) AND ((C.Rx)='IDEF'))
ORDER BY P.D;
(that is the way Access had created it from the design view).
I tried rewriting it to:
SELECT C.R, C.P, M.Q, P.S., C.F, C.S, P.X, P.D, C.E
FROM C LEFT JOIN (M LEFT JOIN P ON M.ID = P.ID) ON C.E = M.E
WHERE C.R='ABC' AND C.P=12345 AND
(C.Rx='DEF' OR (M.ID Is Not Null AND C.Rx='IDEF'))
ORDER BY P.D;
and the time to open the form went to 7 seconds.
I'm not a SQL expert and have no idea why such a minor change would make such a big difference, but it consistently does. (Only in Access with ODBC: both queries run efficiently in SSMS).
Thanks for the update. Indeed, you got a 50% gain in the first step, and the rest of the way to a "reasonable" performance with more sophisticated improvements in the query. That's exciting and encouraging for others wanting to move to the cloud.