data export to azure - adding indexes

(0) Share

Report

Posted on by jsteelecrm

365

I use the data export function in Dynamics 365 to export selected entities to an Azure SQL database. I however would like to add my own indexes to the tables in this database to improve query speed.

I fear however that this might break the export as I have had issues in the past.

Is it possible to add new indexes to tables in this exported database that the export process will then carry on using? i.e. if I add an index to the contacts table i Azure, will new contacts added to it the next day now be using that new index?

Thanks

James

Categories:

Other

I have the same question (0)

All responses (3)

Answers (0)

LeoAlt 16,331 Moderator on at

Like (0)

Report
Copy link

Link copied!

Hi partner,

According to this article, you should create a SQL premium database to use indexes.

You could also refer to this similar post.

https://community.dynamics.com/365/financeandoperations/f/765/t/295832

In addition, I recommend that you could move your post to Finance and Operations Forum for more professional answers.

Hope it helps.

Best Regards,

Leo

Was this reply helpful? Yes No
Abby Kong 6 on at

Like (0)

Report
Copy link

Link copied!

Hi James,

Adding index didn't break the data export for us. I think it's safe to add non-clustered indexes, I haven't tried clustered index.

Another suggestion is to explore auto-tuning of Azure DB: With auto-tuning, indexes are created/dropped automatically based on the query you run. Over time, we experience significant improvement on those frequently used queries in query speed against Azure DB from data export service.

This is great if you are not SQL Server expert. However, it does take a bit of time with auto-tuning to have proper indexes created.

Regards,

Abby

Was this reply helpful? Yes No
David Jennaway 14,065 on at

Like (0)

Report
Copy link

Link copied!

You should avoid creating a Unique index, as that could cause failures in there are duplicates, but all other types of index are fine.

When you create an index, all existing records (with non-null values in the indexed field(s)) are indexed, and any future updates are necessarily indexed as the record is created/updated

Was this reply helpful? Yes No