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

Hi,

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

Thanks CK76. You are always a help.