Forum Discussion

Tim's avatar
Tim
Data Integration
01-13-2025
Solved

Query Plan Analyzer - is it reliable?

Looks to me like the Query Plan Analyzer incorrectly labels some joins as "many" when they should be "one". Also, it seems to analyze the SQL constructed by Sisense, not the database's query plan. Is that correct, or am I missing something? (My goal with this post is to check my understanding of Sisense and the Query Plan Analyzer, and to point out potential issues.)

 

1) Incorrect "many"

See the attached "QPA-graph2.png". Looks like it's saying "Sum(Table1.Field1) Cross Join Sum(Table2.Field2) yields >1 rows". But the sums are not being sliced or grouped; it's just a sum of every row in the table. How can that possibly yield >1 rows?

 

2) Sisense SQL vs database query plan

See attached "QPA order of operations.png". Looks like it's saying that the plan is to:
[1] Select a row from the Table1 (Episode dim table). (1 row.)
[2] Join Table2 (Dates) to Table3 (Patient/Episode fact table) on DateID. (80mil rows.)
[3] Join the results of [1] and [2]. (1 row.)

It would be more efficient to:
[1] Select a row from the Table1 (Episode dim). (1 row.)
[2] Join [1] to Table3 (fact) on EpisodeID. (1 row.)
[3] Join [2] to Table2 (dates) on DateID. (1 row.)

That way, it doesn't need to load all the Table2 and Table3 rows. The query planner knows that it can safely rearrange the joins this way, and it has cardinality estimates so it can see which order will result in less work.

  • Sisense's R&D team analyzed this issue and clarified that model statistics is not updated after every build.
    There is a configuration that defines how long collected statistics are considered valid.
    Please go to Admin tab - Configuration - -5 clicks on Sisense logo - Base Configuration - base.modelStatistics.freshHoursInterval.value.
    Value in this field is the amount of hours the model statistics is valid. In case you need model statistics to be updated after every build, set this value to 0.

    I guess the only downside to updating statistics after every build is that it makes the build take a little longer. I guess it usually only matters if you've been modifying the underlying data or views in a way that might change whether a member of a dimension has many or one rows.

2 Replies

  • Hi Tim

    Thank you for reaching out. I reached out to folks internally to answer your questions.

    1) Can you submit a support case for this? It looks like it might be a bug, and they will be able to dig into it deeper than we can here.

    2) It's true that we are providing analyzing according to Sisense generated SQL, we don't get it from the DB.

    Does that help? Please let us know if you need any additional info.

  • Tim's avatar
    Tim
    Data Integration

    Sisense's R&D team analyzed this issue and clarified that model statistics is not updated after every build.
    There is a configuration that defines how long collected statistics are considered valid.
    Please go to Admin tab - Configuration - -5 clicks on Sisense logo - Base Configuration - base.modelStatistics.freshHoursInterval.value.
    Value in this field is the amount of hours the model statistics is valid. In case you need model statistics to be updated after every build, set this value to 0.

    I guess the only downside to updating statistics after every build is that it makes the build take a little longer. I guess it usually only matters if you've been modifying the underlying data or views in a way that might change whether a member of a dimension has many or one rows.