Access vs Excel Tables - Query Speed?

[Erycus]

Hello,

I learned the basics of power query yesterday and am now trying to optimize the speed of a query refresh.

I'm wondering if anyone could tell me which is faster to use for simple query and load to purposes:

• External MS Access database (.accdb)
• Table within the same MS Excel Workbook

This for data of approximately 10,000 rows (at the moment), with the possibility to grow by about 40,000 rows per year.

Could anyone tell me which would likely be faster in this case (I'm thinking access) and also why?
I understand that Excel worksheets can only handle so many rows of data, but shouldn't this not really affect power query because it works in the .net framework?

 

[Ken Puls]

Hi Erycus,

Interesting question... I've set up some tests pulling 61,166 rows of identical data from an Access Database, Excel Table and an Excel Named Range. In each test, the ONLY transformation I did was to set the data types, so it's consistent. I then ran the refresh a few time to ensure the numbers were polling consistently. Here's the results:

Source	Seconds
Access	2.26
Excel Table	1.04
Excel Named Range	0.96

It doesn't surprise me at all that Access would take longer though. Given that it needs to reach out to another program to collect the data, I would expect some extra overhead there.

I will admit, however, that the Named Range really surprised me here... Given that it takes an extra step in Power Query to promote the header, I would have expected that to take longer. I guess it has less overhead than pulling in a properly formatted table.