none
Data from MS ACCESS - 30x slower than LEGACY from Access RRS feed

  • Question

  • Hello,

    My Power Query is very slow compared to the old/legacy from Access.

    I created a blank new DB with onle table/10 rows.

    I compared 3 load methods (background refresh and adjust columns set to FALSE).

    Via VBA I refreshed the query 50 TIMES:

    • LEGACY

    Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=C:\Users\xxx\slozka\TEST.accdb;Mode=Share Deny Write;Extended Properties="";Jet OLEDB:System database="";Jet OLEDB:Registry Path="";Jet OLEDB:Engine Type=6;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False;Jet OLEDB:Bypass UserInfo Validation=False;Jet OLEDB:Limited DB Caching=False;Jet OLEDB:Bypass ChoiceField Validation=False

    Command type: Table

    Command text: Table1

    LOAD TIME: 2 SECONDS - absolutely amazing, but cannot be referenced in other PQ queries

    • PQ_ACCESS

    let
        Source = Access.Database(File.Contents("C:\Users\xxx\slozka\TEST.accdb"), [CreateNavigationProperties=false]),
        _Table1 = Source{[Schema="",Item="Table1"]}[Data]
    in
        _Table1

    TIME: 62 SECONDS

    • PQ_OleDb

    let
        Source = OleDb.Query("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=""C:\Users\xxx\slozka\TEST.accdb"";", "Select * From Table1")
    in
        Source

    TIME: 31 SECONDS - better, but cannot benefit from native query in more complex solutions

    How to achieve better times using Power Query?

    Warm regards, 

    Jakub Dušek








    Thursday, May 2, 2019 2:02 PM

Answers

  • Thanks Jakub, I got your files. But unfortunately, I am not able to repro, the refresh takes about 3 or 4 seconds, which is pretty comparable with legacy access.

    You can follow the instruction at my reply email to collect trace. Also, please make sure your Access driver is latest:

    https://www.microsoft.com/en-US/download/details.aspx?id=13255

    Thanks

    Xin Guo

     

    Friday, July 19, 2019 10:22 PM

All replies

  • Do you get the same results on your computer with the described scenario?

    Jakub

    Friday, May 3, 2019 3:43 PM
  • Hello Jakub, are you able to share your TEST.accdb with us? If so, please send it to xinguo@microsoft.com

    Monday, May 6, 2019 8:30 PM
  • Hello,

    email sent.

    The TEST.accdb is a fresh one-table/10-rows database - so it should not be corrupted.

    Tuesday, May 7, 2019 11:31 AM
  • Hello, still no answer here nor in email.

    I am still getting very bad loading times for Power Query from Access database - 100x SLOWER than legacy

    Fresh Benchmark on a fresh database-one table-one row = no corruption, no compact needed.

    100x consecutive refreshes (via VBA).

    Results:

    1.  POWER QUERY from Access - 5:27
    2. POWER QUERY OLEDB - 2:08
    3. LEGACY from ACCESS: 0:03 - only 3 SECONDS

    This really slows down complex queries where multiple tables are loaded from Access DB.

    I would really appreciate any conclusion please.

    Warm regard,

    Jakub 

     
    Friday, July 19, 2019 12:16 PM
  • Sorry Jakub. I guess the email was buried at spam folder. Could you send it again to:

    guo.xin@microsoft.com 

    I will send confirm email back when I received.


    Friday, July 19, 2019 6:25 PM
  • Thanks Jakub, I got your files. But unfortunately, I am not able to repro, the refresh takes about 3 or 4 seconds, which is pretty comparable with legacy access.

    You can follow the instruction at my reply email to collect trace. Also, please make sure your Access driver is latest:

    https://www.microsoft.com/en-US/download/details.aspx?id=13255

    Thanks

    Xin Guo

     

    Friday, July 19, 2019 10:22 PM