Priority filter for faster livemodel

Hi Johan-Bekker ,

I'm glad I'm not alone! I did build a workaround. It's nasty and counterintuitive, but it worked: To make it faster, I query in a way that blocks some indexes/foreignkeys and allows others.

 

First, build a table function like this:

TableFunction(UniqueID) = Select * From OriginalView Where UniqueID.

 

Then in Sisense, query like this:

Select 

From 

--(And Sisense will add a Where clause with your filters when it performs a query)

 

Fields that you select from OriginalView can use indexes. Take the fields that will reduce it to 4 partitions from OriginalView. Fields that you select from TableFunction can't use indexes. Take your other fields fields from here.

 

Result: Selecting from TableFunction can't use indexes to filter or foreign keys to join. So, the optimiser sees that it'll be very slow to join/filter TableFunction. So, it first applies all the filters you've given to OriginalView fields. Once it's got those results, it then calculates TableFunction for each UniqueID. That calculation is inefficient, but because you're already down to just a few rows/partitions, it'll run quickly.

 

Snowflake vs SQL Server:

I work on SQL Server; Snowflake might be different. In particular: I cross-apply to a Table Function, which is a way to join that prevents using foreign keys for the join and prevents using indexes to apply the filters from Sisense's Where clause. If Snowflake works differently, you might need to get creative (I came up with the table function idea because that's one of the "don't do this because it prevents indexes" tips for SQL Server; you might have to study how to write bad queries for Snowflake 😂...).

Another thing to look into might be "writing your own query plans"; that might be neater, but it sounded like even more hassle to learn, maintain, and teach others about than my index-blocking solution.