Answered

How to create a database index?

(0) Share

Report

Posted on by Tim Robinson

I have added a new entity to common data services, and I want to create an index on a specific field to ensure quick look-ups on that field value. I can see the ability to define keys but this doesn't apply to me because my field value is not unique. How do I do this?

Categories:

Microsoft Dataverse with Power Apps

I have the same question (0)

All responses (4)

Answers (2)

EricRegnier 8,720 Most Valuable Professional on at

Like (0)

Report

Hi @TimRobinson33,
Unfortunately the only way to create custom indexes is via alternate keys but as you said, they are created as unique indexes. If you have performance issues or wish to create other indexes, the only way is to submit a Microsoft support ticket of category performance and supply as much info about your performance issues and index request. Hopefully they would be able to support you from their end. You can create a support ticket at https://admin.powerplatform.microsoft.com/support.
Also, that would be a great feature to have, suggest to submit the idea at: https://powerusers.microsoft.com/t5/Power-Apps-Ideas/idb-p/PowerAppsIdeas
Cheers,

Was this reply helpful? Yes No
Tim Robinson 50 on at

Like (0)

Report

Thanks for the response. I'm not specifically having a performance problem but I'm a software developer building an app that uses CDS as its data store, so I was assuming I needed to define my indexes as part of creating the data model.

I have seen on another Dynamics forum that database indexes are automatically created based on usage patterns so I guess I can just forget all about it unless I start getting performance problems. I would be interested if you know anywhere that describes how this works though.

Was this reply helpful? Yes No
Verified answer

EricRegnier 8,720 Most Valuable Professional on at

Like (0)

Report

Yes, Microsoft monitors the usage and optimizes the databases and whatnot based on the patterns. That's one of the beauties of SaaS or SDaaS (Software Dev as a Service) is that we don't necessarily need to worry about those underlying concerns and expect the provider to manage it at a certain extent. From what I know Microsoft has not officially released those algorithms and patterns, you can always try to contact them but I wouldn't count on it to provide you with those details.

Was this reply helpful? Yes No
Verified answer

Ben Thompson 1,400 on at

Like (1)

Report

@EricRegnier Can I correct you slightly there.

The core part of all CDS records are stored in Azure SQL with autotuning enabled - https://docs.microsoft.com/en-us/azure/azure-sql/database/automatic-tuning-enable shows what is happening behind the scenes.

When auto-tuning is enabled Azure SQL continually watches for expensive (long running, resource intensive) queries and automatically creates and modifies indexes to maximise SQL performance.

Was this reply helpful? Yes No