cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
opax
Level: Powered On

Does everybody else have slow SQL Azure experience with PowerApps as well?

Hi,

 

I'm working on a PowerApp that uses SQL Azure database as a data source. I'm experiencing slow connection to the data source. The database is located on West Europe (which is the nearest one available for the client location in Finland) and is S0 Standard without geo replication. 

 

What I am interested in is:

1. Is anyone else experiencing same kind of slowness?

2. Is there somebody who does no experience this slowness at all? 

3. Do PowerApps SQL connections work better in US than in Europe?

4. Is there something I could fix to make it work better?

 

Here are some examples of slow queries, in general everything is slow and these are just some of the more slower ones I currently have:

1. In a screen there is a list of records linked to the main record which are queried with

SortByColumns(Filter('[dbo].[LockRow]',SafetyManagementPlanId=SafetyManagementPlanGallery.Selected.Id), "LockNumber",Ascending) 

- If I change the main record (SafetyManagementPlan) to a record with 17 LockRow items, it will take about a minute. Fetching a single row takes about a second (you can see them appearing one by one) but the whole process starts over multiple times.

- Here is how the table is created, you can see that I have index for both SafetyManagementPlanId and LockNumber:

CREATE TABLE [dbo].[LockRow]
(
	[Id] INT IDENTITY(1,1),
	[TargetName] VARCHAR(50) NULL, 
    [Position] VARCHAR(50) NULL, 
    [PreferredLockState] VARCHAR(20) NULL, 
    [SafetyManagementPlanId] INT NULL, 
	[LockNumber] INT NULL, 
    [Marked] BIT NULL, 
    [MarkHistory] VARCHAR(4000) NULL, 
    CONSTRAINT [FK_LockRow_SafetymanagementPlan] FOREIGN KEY ([SafetyManagementPlanId]) REFERENCES [SafetyManagementPlan]([Id]),
    PRIMARY KEY (Id)
)

GO

CREATE INDEX [IX_LockRow_LockNumber] ON [dbo].[LockRow] ([LockNumber])

GO

CREATE INDEX [IX_LockRow_SafetyManagementPlanId] ON [dbo].[LockRow] ([SafetyManagementPlanId])

GO

2. Inserting multiple records to a table based on existing records on another table takes a bit more than 1 second per row.

Collect('[dbo].[DangerAssesmentRow]',DropColumns(AddColumns(Filter('[dbo].[DangerAssesmentBaseRow]',Active=true), "SafetyManagementPlanId", SafetyManagementPlanGallery.Selected.Id), "Active","Id"))

In this case the DangerAssesmentBaseRow table has about 35 active rows, and seemingly simple operation of copying them into another table (DangerAssesmentRow) takes very long time.

 

Any comments are welcome.

 

Cheers!

 

// Olli

2 REPLIES 2
Highlighted
Microsoft Employee

Re: Does everybody else have slow SQL Azure experience with PowerApps as well?

Hi Olli,

To fix slow performance with azure SQL database, you could tune your queries as much as possible to improve the performance. For more detailed information, you could refer to the article below.

Fixing slow performance with Azure SQL database.

http://capesean.co.za/blog/fixing-slow-performance-with-azure-sql-database/

And you also could follow the guidance for azure SQL database in the articles below.

Azure SQL Database performance guidance for single databases.

https://azure.microsoft.com/en-us/documentation/articles/sql-database-performance-guidance/

Improve the performance of your Azure SQL Databases using Index Advisor.

https://azure.microsoft.com/en-us/blog/optimize-database-performance-using-index-advisor-7/

Best regards,

Sara Fan

opax
Level: Powered On

Re: Does everybody else have slow SQL Azure experience with PowerApps as well?

Thank you Sara. Following your advice I'm currently trying to optimize the indices in my database.

 

As a side note: I tested the connection between West Europe and West US as database locations when the client is located in Finland (Nordic/Easter Europe) and found out that there is no real difference in query speed when filtering on text field with a table containing about 8000 rows.

Helpful resources

Announcements
firstImage

Microsoft Business Applications Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

Top Kudoed Authors
Users Online
Currently online: 101 members 3,850 guests
Please welcome our newest community members: