Why did I choose 10 GB?
One of many key ways in which BI Engine hurries up queries on small tables is that it caches the tables in reminiscence. It additionally does different speedups, however that is the primary one. So, you wish to present it sufficient reminiscence to comfortably maintain the tables you may be querying. BI Engine will routinely handle the reminiscence for you.
In my case, the Chicago taxicab question entails 3GB, so I used 10 GB. For the opposite two queries, 1 GB would have been sufficient. You may go as much as 100 GB, one thing I’d advocate when you’ve got many concurrent queries on small tables. You would possibly be capable to get away with much less as a result of BI Engine shops knowledge compressed, however it’s higher to supply some further room.
You may activate BI Engine from a script utilizing the Reservations API. Notice, nevertheless, that it takes a couple of minutes for the reminiscence to turn out to be obtainable, so that is one thing you need to contemplate doing for a number of hours a minimum of, not on a per-query foundation.
Turning on BI Engine
Right here’s how the three queries compared with and with out BI Engine: