Hello,
Can anyone tell me if they know which method would perform faster? Power Apps to On-Prem SQL using a Gateway? or Power App to Azure SQL?
I have an app using the first method (Power Apps to On-Prem SQL using a Gateway). On the app start up it runs a few collections (some concurrent, some not). In some cases depending on the WiFi connection, internet speed, etc.. it can take up to 2 minutes to complete.
Here is an example of my code:
Set(
varSpinner,
true
);
Concurrent(
//Collect current completed issued audits
ClearCollect(
colCompIssuedAudits,
'[dbo].[rprCompIssuedAudits]'
),
//Collect current audit schedule with count of completion frequency
ClearCollect(
colAuditSchedule,
AddColumns(
'[dbo].[rprAuditSchedule]',
"IssueCount",
LookUp(
'[dbo].[rprCompIssuedAudits]',
Id_AuditSchedule = '[dbo].[rprCompIssuedAudits]'[@AuditSchedule_Id],
IssueCount
),
"Audit",
LookUp(
'[dbo].[rprAudits]',
Audit_Id = '[dbo].[rprAudits]'[@Id_Audit],
Audit
)
)
),
//Collect restricted auditors
ClearCollect(
colRestricted,
'[dbo].[rprRestricted]'
)
);
//Check if user is a restricted auditor
If(
CountRows(
Filter(
colRestricted,
Auditor = User().FullName
)
) = 0,
//If true, select random schedule audit Id (Id_AuditSchedule) from audit colAuditSchedule filtering by IssueCount
Set(
varRandomAudit,
First(
Shuffle(
Filter(
colAuditSchedule,
Or(
Frequency > IssueCount,
IsBlank(IssueCount)
)
)
)
)
),
//If false, select random schedule audit it (Id_AuditSchedule) from colAuditSchedule filtering by area
Set(
varRandomAudit,
First(
Shuffle(
Filter(
colAuditSchedule,
Cell = lblRestrictedAuditArea.Text
)
)
)
)
);
Concurrent(
//Patch random audit schedule Id to issued audit table (rprIssuedAudits) for historical purposes
Set(
varIssuedAuditId,
Patch(
'[dbo].[rprIssuedAudits]',
Defaults('[dbo].[rprIssuedAudits]'),
{
AuditSchedule_Id: varRandomAudit.Id_AuditSchedule,
Auditor: User().FullName,
IssuedDateTime: DateTimeValue(
Text(
Now(),
DateTimeFormat.UTC
)
)
}
).Id_IssuedAudit
),
//Collect questions from random scheduled audit varible (varRandomAudit)
ClearCollect(
colQuestions,
Filter(
'[dbo].[rprAuditQuestions]',
Active = true,
Audit_Id = varRandomAudit.Audit_Id
)
)
);
If(
User().FullName = "AUG iPad",
Set(
varShowAuditInfo,
true
),
Set(
varShowAuditInfo,
true
)
);
If(
User().FullName = "AUG iPad",
Set(
varShowAuditInfo,
false
),
Set(
varShowAuditInfo,
true
)
);
Set(
varSpinner,
false
)
Would it be better to move this to Azure SQL?
Thank you,
-Justair007
