Access and Azure SQL Database

%3CLINGO-SUB%20id%3D%22lingo-sub-1519920%22%20slang%3D%22es-ES%22%3EAccess%20and%20Azure%20SQL%20Database%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1519920%22%20slang%3D%22es-ES%22%3E%3CP%3EHello%2C%20I%20tell%20you%20that%20I%20have%20the%20following%20problem%2C%20I%20connected%20my%20access%20database%20with%20a%20SQL%20database%20in%20Azure%2C%20but%20the%20communication%20is%20very%20slow%2C%20for%20example%20one%20of%20my%20forms%20takes%2015%20seconds%20to%20open.%20My%20database%20is%20basic%2C%20it%20has%2010%20GB%20and%20only%2010%20DTU%2C%20but%20to%20see%20if%20it%20improved%2C%20increased%20probe%20to%20100%20DTUs%2C%20but%20even%20so%2C%20the%20speed%20did%20not%20improve%20at%20all.%20Finally%20I%20tried%20changing%20the%20location%20of%20the%20server%2C%20from%20the%20Center%20of%20the%20USA%20to%20the%20South%20of%20Brazil%2C%20(Because%20I%20am%20from%20Chile%2C%20this%20did%20produce%20an%20improvement%20passing%20from%2015%20to%208%20seconds%20in%20opening%20the%20form%2C%20but%20even%20so%20it%20is%20very%20slow%20and%20even%20gets%20stuck%20when%20generating%20some%20reports.%20I%20even%20tried%20Sharepoint%20and%20sharepoint%20the%20same%20form%20opens%20it%20in%20about%205%20seconds%2C%20but%20I%20need%20access%20to%20work%20with%20Azure%20in%20order%20to%20connect%20my%20mobile%20application%20with%20access%20through%20Azure%20SQL%20database.%20Thank%20you%20in%20advance.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1519920%22%20slang%3D%22es-ES%22%3E%3CLINGO-LABEL%3EAccess%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1520010%22%20slang%3D%22en-US%22%3ERe%3A%20Access%20and%20Azure%20SQL%20Database%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1520010%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F727456%22%20target%3D%22_blank%22%3E%40JoseAntonio110%3C%2FA%3E%26nbsp%3BThis%20is%20a%20very%20well%20known%20problem%2C%20unfortunately.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDepending%20on%20how%20you%20designed%20the%20Access%20forms%2C%20you%20%3CEM%3E%3CSTRONG%3EMIGHT%26nbsp%3B%3C%2FSTRONG%3E%3C%2FEM%3Ebe%20able%20to%20improve%20performance%20by%20adopting%20a%20few%20practices.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOne%2C%20if%20you%20are%20loading%20the%20forms%20in%20the%20traditional%20%22Access%20way%22%2C%20you%20are%20binding%20the%20form%20to%20a%20table%20and%20applying%20filters%20to%20it%20to%20select%20or%20navigate%20to%20specific%20records.%20If%20that%20is%20how%20this%20was%20designed%20with%20Access%20tables%2C%20it%20works%20well%2C%20but%20when%20you%20are%20pulling%20records%20from%20a%20remote%20SQL%20Server%2C%20i.e.%20from%20Azure%2C%20it%20is%20really%20inefficient%2C%20and%20therefore%2C%20slow%20to%20do%20it%20that%20way.%20Instead%2C%20all%20forms%20should%20be%20designed%20with%20single%20record%20queries.%20Apply%20criteria%20that%20select%20and%20return%20one%20record%20and%20a%20time%20and%20requery%20that%20one-record%20recordset%20in%20order%20to%20navigate%20to%20other%20records.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EA%20second%20factor%20that%20can%20make%20a%20big%20difference%20is%20having%20a%20lot%20of%20combo%20or%20list%20boxes%20on%20the%20form.%20Each%20of%20those%20has%20its%20own%20recordset%20to%20pull%20down%20from%20the%20SQL%20Azure%20database%2C%20and%20that%20also%20slows%20things%20considerably.%26nbsp%3B%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EThe%20same%20is%20true%20of%20subforms.%20Each%20subform%2C%20in%20turn%2C%20also%20has%20its%20own%20recordset%2C%20and%20again%2C%20that%20adds%20to%20load%20times.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20short%2C%20interface%20design%20for%20Access%20relational%20database%20applications%20linked%20to%20remote%20databases%2C%20including%20SQL%20Azure%2C%20depends%20on%20pulling%20the%20least%20possible%20traffic%20across%20the%20wire%20from%20that%20remote%20database.%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3EFocus%20on%20that%20first.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOther%20things%20that%20can%20sometimes%20help%20might%20include%20using%20passthru%20queries%20whereever%20possible%20for%20rowsources%20in%20combo%20and%20list%20boxes.%20Although%20they%20are%20not%20significantly%20better%20in%20all%20cases%2C%20Passthrus%20do%20tend%20to%20perform%20better.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New 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.

2 Replies
Highlighted

@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.

 

 

Highlighted

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