Results 1 to 2 of 2

Thread: Access vs Excel Tables - Query Speed?

  1. #1

    Access vs Excel Tables - Query Speed?



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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?

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,453
    Articles
    44
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    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.
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •