Connection Speed between Excel | Access as data source

[Anonymous]

Hello Fellows,

I have some tables stored in MS Excel and considering migrate to MS Access for improving performance.

However, the performance is almost the same for a 200k rows sample data set (Both Import and Linked for Access).

Is there going to be a big difference when the data set is growing or they are just almost the same?

Best Regards,

[lbendlin · Accepted solution]

this made me chuckle "migrate to MS Access for improving performance"  - hadn't heard that before.

Is it  a simple table spool or are you doing any logic on the connection?  While you are performance testing - see what the performance is with a CSV file.

If they are all the same then most likely the issue is your network connection.

[v-eachen-msft · Accepted solution]

Hi @Anonymous ,

Even if you use the Access database, it only supports the import mode. This means that it is less efficient for large amounts of data. You could upload your excel files into Sql Server or other database who supports direct query mode. The “DirectQuery” feature lets you connect live to your data source, which should make the whole process faster as you don’t need to import data from database to Power BI.

[lbendlin · Accepted solution]

this made me chuckle "migrate to MS Access for improving performance"  - hadn't heard that before.

Is it  a simple table spool or are you doing any logic on the connection?  While you are performance testing - see what the performance is with a CSV file.

If they are all the same then most likely the issue is your network connection.

[Anonymous]

Alright..... thank you @lbendlin I thought a database should at least be faster than Excel.

I have excel file over 400k and it goes really slow in Power BI Query Editor, I am trying to find a method to acclerate. 

Anyway, at least I know it is not possible.