cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
opax
Advocate IV
Advocate IV

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
Sara_fan
Microsoft
Microsoft

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

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
PA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

PWREduCon768x460.png

Join us at PWR EduCon - A Power Platform Conference

Learn from the top Power BI, Power Apps, Power Automate & Power Virtual Agents experts!

Top Solution Authors
Top Kudoed Authors
Users online (3,380)