Forum Discussion
Access and Azure SQL Database
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 ...
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).
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.