SUMMARY:
My Live Model is slow. Source data is big, but a dashboard filter picks just one item, so I think it can be fast. This feels like a common use case for a Live Model.
DETAIL:
My dashboard is based on a live model that queries SQL Server. The user selects "Group.Name" and a "Date.Year" on the filters. Charts show "Member.Name" and "MemberPurchase.Date".
My live model is slow. I looked at the queries that Sisense sends to the server. The simplified version is:
Select Member.Name, MemberPurchase.Date
From Member
Inner Join MemberPurchase
Where Member.GroupID IN (Select GroupID From Group Where Group.Name = '<user selection>')
And MemberPurchase.DateID IN (Select DateID From Date Where Date.Year = '<user selection>')
The query is just as slow when I run it directly. When I replace the subquery with ...GroupID IN (1234)... then it runs fast.
I believe the difference is that SQL Server, when building the query plan, sees "...IN (<literal>)..." as low cardinality, whereas it sees "...IN (<subquery>)..." as unknown cardinality. For low cardinality, it first filters Member by GroupID to get a list of MemberIDs, then filters MemberPurchase by that list of MemberIDs. For unknown cardinality it performs the Member-MemberPurchase join first.
So, can I make the model fast? Does anyone have recommendations? My ideas:
Can I trick SQL Server into using the better query plan? Perhaps by manipulating views, indexes, or Sisense's table definition queries?
Can I make Sisense fetch the GroupID, then pass that as a filter on Member? I could do that by using javascript to check the Group.Name filter, then creating a new filter for MemberID. But problem then is applying a filter to a Fact table causes problems for other Fact tables.
