locked
Slow Query Speed from Exchange Mailbox RRS feed

  • Question

  • My report is taking over an hour to complete.  The queries are pretty straightforward and are described below.  If there anything I can do to improve my report speed?

    I am using Excel 365's power query to aggregate survey response results.  The results are stored in an xlsx which are in turn stored in an O365 Exchange mailbox.  There are currently 52 messages/spreadsheets in the mailbox and I expect many more.  

    Q1 scopes the mailbox to valid attachments.

    Q2 references Q1, is where the transform results is expanded, and is what is ultimately loaded into my data model for reporting.



    Wednesday, February 12, 2020 5:13 PM

Answers

  • Jeffrey,

    If I understand you correctly, it sounds like you are storing XLSX files in attachments in your Exchange mailbox, which you then query from PowerBI using our ExchangeOnline connector. From the sound of it, you are querying your Inbox and filtering by messages with attachments.  How do you determine if they are 'valid' attachments?

    I would say that an Exchange attachment may not be the fastest backend to use for data storage of this manner. I'd recommend SharePoint (you might have that already with your O365 subscription) or even better - Azure Blob or ADLS Gen2, in order of performance quality for this particular scenario.

    However, there may be some things you can do to optimize your current query. Can you place the messages in question into a single subfolder, either manually or using an Outlook rule? Avoiding a full inbox filter will help a great deal. I would try that first.  If performance doesn't improve enough by doing that, please send me a copy of your Mashup Query and we can look deeper.

    Tuesday, February 18, 2020 5:38 PM