CRUD operation with webapi is slow

(2) Share

Report

Posted on by CU31070723-0

Hello everybody,

the website that I'm building uses virtual tables from SQL. I use webapi to perform CRUD operations but I noticed that it's very slow. A patch operation to update a column of a row in a table can take from 6 to 16 seconds. Some pages need to load data from several tables when open, which can take very long if open for the first time. Is it supposed to take this long? Is it this slow because I use virtual tables? Is there any way to reduce the loading time?

Thanks in advance for your answers.

Categories:

General topics

I have the same question (0)

All responses (4)

Answers (0)

Suggested answer

Michael E. Gernaey 53,960 Moderator on at

Like (0)

Report
Copy link

Link copied!

There isn't any way for us to tell you.

We have no access to your servers, or the networks or the gateway etc.

I will see that YES virtual tables, especially if through gateways with lots of data can be slower, so you would need to enhance them to perform better

If this helps please mark as yes

Was this reply helpful? Yes No
Suggested answer

Inogic 1,191 Moderator on at

Like (1)

Report
Copy link

Link copied!
Hi,

When using Power Pages with the Web API to perform CRUD operations on virtual tables, the performance can indeed be slower due to the real-time nature of virtual tables and the overhead introduced by the Power Platform’s API layer.
Below are a few steps to help reduce the load time of API events

Batch API Requests:

Group multiple API operations into a single request to reduce the number of API calls.

Reduce Data Fetched:

Use $select to return only the necessary fields and $filter to retrieve only the needed rows.

Caching for Read-Heavy Operations:

Use server-side or client-side caching to avoid frequent API calls for static data.

Asynchronous (Lazy) Loading:

Load essential data first, and fetch additional data asynchronously as needed.

Background Processing for Long Operations:

Use background jobs or Power Automate for time-consuming updates like PATCH.

Optimize SQL Queries:

Ensure efficient SQL queries by using proper indexing and avoiding complex joins.

Server-Side Pagination:

Implement pagination using $top and $skip to load data in smaller batches.

Monitor API Performance:

Use tools like Application Insights to track and analyze API performance.

Preload Critical Data:

Pre-fetch key data in the background and store it locally for faster access.

Optimize Power Pages Configuration:

Minimize unnecessary page interactions that trigger Web API calls and reduce dependencies on virtual tables.

Hope this helps.

Thanks!
Inogic Professional Services: Power Platform/Dynamics 365 CRM
An expert technical extension for your techno-functional business needs
Drop an email at crm@inogic.com
Service: https://www.inogic.com/services/
Tips and Tricks: https://www.inogic.com/blog/

Was this reply helpful? Yes No
CU31070723-0 25 on at

Like (0)

Report
Copy link

Link copied!

Could you tell me how to batch API requests?

I have been using api commands in this format

webapi.safeAjax({

type: "PATCH",

url: "/_api/{table logical name}s({GUID of row})",

contentType: "application/json",

data: ...

});

I found information on this page Execute batch operations using the Web API (Microsoft Dataverse) - Power Apps | Microsoft Learn but don't know how to write the code there in the "webapi.safeAjax({...})" format that I have been using.

Was this reply helpful? Yes No
CU31070723-0 25 on at

Like (0)

Report
Copy link

Link copied!

We abandoned the virtual SQL tables to use only Dataverse tables and it's much much quicker and simpler!

Was this reply helpful? Yes No