Dataverse Stored Proceedures

(0) Share

Report

Posted on by Gary_Eden

375

Hi
I've previously built several PowerApps and Power BI reports running off a common Azure SQL database.

To improve app & report performance I set up several Stored Procedures to carry out the heavy lifting... worked a treat bringing display time down from 'will this %*$& ever load..!' to, 'wow that was quick!'.

I'm now tasked with working with Dataverse with some tables expecting to hold over 10m rows and growing.

I've found a bit of info' on how Dataverse SQL differs from normal SQL here: https://docs.microsoft.com/en-us/powerapps/developer/data-platform/how-dataverse-sql-differs-from-transact-sql?tabs=supported which appears to offer a starting point.

However, there aren't any worked examples showing how to piece the 'stored procedures' together.

I'm assuming that there isn't anything as useful as SSMS available to use with Dataverse?

Any points, help, tips, etc. would be greatly appreciated.

Thanks in advance.

Categories:

Microsoft Dataverse with Power Apps

I have the same question (0)

All responses (5)

Answers (0)

Carlosr Microsoft Employee on at

Like (0)

Report

You can connect to DataVerse with SSMS but I don't think you can create stored procedures. You can execute T-SQL queries for read-only activities:
Use SQL to query data (Microsoft Dataverse) - Power Apps | Microsoft Docs

Was this reply helpful? Yes No
MayankP 464 on at

Like (1)

Report

There is XrmToolBox add-in called SQL4CDS, which allows you to write sql against data verse environment...... see if this helps in anyway!

https://www.xrmtoolbox.com/plugins/MarkMpn.SQL4CDS/

Was this reply helpful? Yes No
Gary_Eden 375 on at

Like (0)

Report

Hi @Carlosr & @MayankP
Thanks for your suggestions, both offer potential - I'll let you know how I get on.

Was this reply helpful? Yes No
EricRegnier 8,720 Most Valuable Professional on at

Like (2)

Report
Hi @Gary_Eden,

You can't create Stored Procedures with Dataverse, if you need to wrap some logic and increase performance you can have a wrapper web service (e.g Azure Functions) with caching (if required) to encapsulate some logic. In general, performance shouldn't be impacted too much for large scale volume if designed right obviously.

For instance, I’ve implemented a few of cases with over 400 entities/tables and over 1B records in total. There are some points to consider when working with these volumes and complexity like security constraints and performance considerations particularly around activities and activity pointers.

If you're having existing perf issues, Dataverse is a SaaS and DaaS (dev as a service) and therefore there's no direct database access and equivalent DB performance. Transactions will be passing through hoops of layers. The performance should be reasonable though, and I haven't gotten any major issues with many of my large-scaled implementations. Here are a few things you can try:

Check that the Dataverse environment is provisioned in the closest region

Try running to diagnostic tool to see perhaps where some of the issues are:
https://<crmorgname.crm>.dynamics.com/tools/diagnostics/diag.aspx
More info:
https://powerusers.microsoft.com/t5/Power-Apps-Community-Blog/Top-15-best-practices-when-configuring-Power-Platform-and/ba-p/850804 and
https://docs.microsoft.com/dynamics365/customerengagement/on-premises/developer/best-practices-sdk?view=op-9-1

Refer to the best practices to ensure some configurations/customizations do not impact performance:
https://docs.microsoft.com/dynamics365/customerengagement/on-premises/developer/best-practices-sdk?view=op-9-1
Note: even though is mentions "on-premise" the same applies for online

There used to be no performance differences between a Sandbox and Production environment, but it is no longer the case. Configure your environment to Production type of instance and test?

Try opening a Support Request at Microsoft via the Power Platform admin center portal:
https://admin.powerplatform.microsoft.com/support

Hope this clarifies...

Was this reply helpful? Yes No
EdZ 3 on at

Like (0)

Report

Very useful tool

Was this reply helpful? Yes No