Improving Query Performance

[CPGDeveloper]

Hi All ~

I administer an Access based application -- and since moving our back end to Azure SQL, our initial front end form is taking about 30 seconds to load. It took about 5-10 seconds when using a split MS Access Back End.

The Control Source of the subform that loads upon open changes depending on who is using the application. I have code that determines who is using the application, and based on who that is, generates a different sql statement to populate the control source. I then change the control source of the subform, and requery. I'm wondering if there's a simpler, faster way to achieve this --

When stepping through the code, where it slows down are these two statements --

Forms!fMain.subformMain.Form.RecordSource = mysql 'mysql is a sql statement that i've previously defined. It runs in one second when I run it directly as a query
Forms!fMain.subformMain.Requery

Both of these code lines take about 10-15 seconds each to generate. Everything else appears to be instant. Is there another way to write this. This is executed on the Form Load Event of the Primary Form.

 

[welshgasman]

If you change the record source of a form, that in itself is a requery, so no use repeating it, especially if the dataset is large?

If it is one used a lot, perhaps try using it as a saved query?

HTH

 

[xenou]

5-10 seconds is also a long time (in the old split MSAccess BE). What is in this query?

 

[CPGDeveloper]

It's a SQL statement that is dynamically created depending on who the user is -- after some work on it, I created some views in SQL that load much much faster. Thanks for your help.