Performance Improvement Question

[Nick Burns]

I know this is going to be very generic and may not be able to get a clear answer without looking at the data (it's sensitive employee info) and the queries themselves, but what are some of the things I can do or check to improve my queries?
I'm reading an Excel file that is 5MB (67 columns X 19,387 rows) however, while watching my queries run, it's up into the 300MB range in reading the data.

 

[Ken Puls]

First thing I would try is to turn off privacy for the workbook:

• Data --> Get Data --> Query Options --> Current Workbook --> Privacy --> Ignore

In some workbooks I have seen the speeds drop DRASTICALLY by doing this. If you're combining data with sources outside of your org, you don't want to do this, but if it's all internal you shouldn't need to be as concerned with privacy settings.

In addition, while you're in the same dialog, go to the Global --> Data Load --> Fast Data Load. Check the box there. This may or may not help, but it's worth a shot as well. This prevents you from using Excel while the data is loaded, but can load faster.

After that we start looking at Table.Buffer(), but to do that we'd need a bit more information as to how your queries are structured.

 

[El Cid]

Hardware wise get an SSD. Microsoft likes their virtual memory pool pre-fetch crap and this helps speed all of that up. i don't have the lockups I used to have running more than two models simultaneously like I use to and everything is a lot faster.

 

[cyborgski]

I sometimes run into this problem when joining on a non key field. I will either perform a Table.Group or Table.AddKey on the table that I am looking up. This technique may stop query folding. However, I don't deal much with server based data and the performance improvement is substantial.