+ Reply to Thread
Results 1 to 4 of 4

Excel Power Query Refresh or Access Query - 2nd Query Run is faster

  1. #1
    Forum Contributor
    Join Date
    10-02-2013
    Location
    Harrisburg, PA
    MS-Off Ver
    Office 365 MSO (16.0.10730.20264) 32-bit
    Posts
    103

    Excel Power Query Refresh or Access Query - 2nd Query Run is faster

    Hi All.
    1st... In Excel: My power query connections are ODBC for Denodo and SQL Server. I use the advanced editor to enter my custom SQL.
    2nd... In Access: My connections are DNS less pass thru queries to the servers for Denodo and SQL Server. For sharepoint, the tables are direct linked.


    3) When querying against Denodo or SQL Server or a Sharepoint List, the 1st time I run a query (Power query refresh or MS Access) it runs much slower than when I repeat it immediately after???
    4) With vba, I have tried to Ping the server before the 1st run to "wake it up". Does not help. Maybe the ping does not use the same connection as the Power Query connection
    I can't remember where I got this code...
    Please Login or Register  to view this content.
    5) With vba, I tried to open a connection to the server (ADODB.Connection), not run anything, and close the connection to "wake it up". Does not help. Maybe an ADODB connection is not the same as power query connection or Access Pass thru.
    Please Login or Register  to view this content.
    6) It does seem that if I open a 1 line table (sharepoint list with VBA) before I run a query (against the same sharepoint list site) the query runs faster that 1st time.
    Please Login or Register  to view this content.
    Any Ideas?

    Thanks.
    Steve
    Harrisburg, PA USA

  2. #2
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,637

    Re: Excel Power Query Refresh or Access Query - 2nd Query Run is faster

    3) That's just nature of how query works for most db. First pass will leave query plan etc in memory and will execute faster the second run.

    4) Ping has nothing to do with query execution.

    5) Read 3). Opening connection doesn't do anything to query execution speed.

    6) Not sure on this one. May be by querying single table, it stores schema info in memory?

    In general, for large data set, I store my data in MSSQL (Express is fine) and query from it. Allowing me to take advantage of Views, Temp Table etc to speed up query.
    There are other things to consider, but without knowing your data bit hard to say.

    For SharePoint, if you do have access to backend db directly. Query data from the db, rather than going through web service.
    “Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.”
    ― Robert A. Heinlein

  3. #3
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    5,124

    Re: Excel Power Query Refresh or Access Query - 2nd Query Run is faster

    Hi,

    the second run is obviously faster as data are already in the hard disk buffer/cache memory …

  4. #4
    Forum Contributor
    Join Date
    10-02-2013
    Location
    Harrisburg, PA
    MS-Off Ver
    Office 365 MSO (16.0.10730.20264) 32-bit
    Posts
    103

    Re: Excel Power Query Refresh or Access Query - 2nd Query Run is faster

    Thanks CK76. You are always a help.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Power Query: Updating a query based on changing tables
    By cheesehead101 in forum Excel General
    Replies: 1
    Last Post: 12-16-2019, 06:54 PM
  2. [SOLVED] Power Query - load to workbook and create query table
    By jaryszek in forum Excel General
    Replies: 1
    Last Post: 12-10-2019, 11:09 AM
  3. Refresh all Power Query Pivots performing only 1 Query and stops
    By jaryszek in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-04-2019, 04:49 AM
  4. [SOLVED] Power Pivot/Query Convert Existing Table to Query
    By trisoldee in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 08-23-2019, 04:27 PM
  5. Custom Permutations Column in Power Query from two columns in the same Query
    By PaintPaddy in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-11-2019, 02:48 PM
  6. Replies: 1
    Last Post: 09-08-2015, 08:12 PM
  7. Importing Access query into Excel:Microsoft Query
    By ge0rge in forum Excel General
    Replies: 3
    Last Post: 04-02-2009, 10:51 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1