is it faster to separate queries into workbooks based on frequency of refresh needed?

[joe1250]

Hi all, I am currently working on a pretty "Get and Transform" heavy project and I've noticed that when i'm building or modifying my queries, it will often need to refresh my queries right from the beginning. The initial queries are the most intensive and only really need to be done sporadically as once i get that data formatted to be useful with the rest, it will rarely if ever change. so i'm wondering if I were to separate these queries into their own workbook and then then load to table, if I would be able to save myself the frequent refreshes by simply pointing to that table as the source for the rest of my queries that will need to be refreshed more frequently? Right now i just have that base table loaded as a connection only, but it still seems to go through the whole re-querying process frequently and since many of the source documents are on another server, can take a while...

Are there any tips for breaking up or otherwise dealing with queries based on the required frequency of that data being refreshed when it is used in conjunction with other queries? or a setting to tell it not to refresh so often?

Thanks

 

[Rudi]

Hi Joe,

This is just my 2 cents contribution as I am certainly not a PQ expert with insights into load times and optimization.

There is a lot of info out there on tips and best practices for query optimization; things like query folding, using (or avoiding) the Buffer functions, importing from text instead of workbooks, being keenly aware of the order of your steps in the transformation process, etc. I'm certainly interested in your theory to split the query across multiple workbooks, and (in my limited experience) I can see some benefits in it. In other words, if the initial large queries only run once and then you refer to the 'static' output of these large queries in queries from other workbooks, this can (should?) make a difference in the transformation times of those queries further downstream.

I'll be keeping my eye on the thread so I can learn more from any expert advice or from your experiments and observations. If you learn anything new (or if you experiment and can see the outcomes), please post your findings. It will be much appreciated.

Good luck!!

 

[Iloveexcel&excelloveme]

Hi Joe,

I've the same issue as you whereby my data are up to 2mil and i've to merge, append and etc so any changes will take a long time.

What i did was;
simplify some of my queries (meaning i've to relook and rewrite after i think i complete the entire steps)
i did each queries on a separate workbook and load it onto a worksheet and bring those into a "Main" Query or PowerBI (Seem slightly faster for me but might be me)
Use Add.Key for merge (read it on some blog, cant remember where the source)
or you can try Ken's blog on "Reduce Development Lag"

Have fun