This is a really useful piece of functionality in Dynamics 365 Finance & Operations however I often see it causing serious performance degradation, so let's see what is going on and what we can do about it.

Current versions of Excel allow you to export 1 million records. That's great, you can now go to any list page and basically export all the records. However, remember this is a live connection from Excel on your computer to your D365F&O, in the cloud via OData.

The first point is that OData is a relatively slow protocol that is not well suited for working with large quantities of data. If you change some customer information in one system and the customer information gets updated in D365F&O almost immediately, it does not involve a lot of data so a second more or less per records is not a big deal.

Try even downloading 100 records and feel the pain. Now imagine downloading 100.000 records. There may be perfectly valid reasons to download 100.000 records in XLSX (Excel format) so you can use Excel to do some number crunching and analysis. However ask yourself, how likely is it that someone needs to crunch that kind of data unexpectedly. Not likely right? Would you even dare work on 100.000 records in Excel with a live connection to D365F&O and risk changes being pushed back en masse?

OK, so if you need to export a large quantity of data as indicated above, the solution is not for users to export to Excel from the UI. Instead use the DMF (Data Management Framework). Plan when you need to do your number crunching and use a job to export the data to a file where whoever needs it can then work with it. DMF is designed for large quantities of data, so much faster, and there is no live connection to D365F&O so no danger of pushing back changes en masse back.

Of course, just knowing it will not prevent users from exporting large amounts of data to Excel from the UI, so you need to figure out a sensible limit for your business needs and limit it. To do this go to System administration > Setup > Client performance options (I will get back to all the options here in another article). The default setting for the maximum number of records to export to excel is 0, meaning just go until the system crashes (in practice if it involves enough data it will use up all the memory in the AOS and cause it to crash). Set this parameter to a sensible value and you will have one less performance headache.   

As with any other performance improvement you can make, there are always trade offs between power, flexibility and performance, so make sure you involve the business, communicate, discuss, align your recommendations with them and management.