BusinessObjects Board

Result set latency and Snowflake

general discussion
#1

We have a weird problem with query performance against Snowflake -
It sounds like this query actually performs much better with query caching disabled (they issue an alter session statement to set USE_CACHED_RESULT = FALSE) than it does using query caching enabled. This is somewhat counterintuitive. I am not sure that we have confirmed it uses query caching in the first place. (seems like if it were not, then issuing the alter session statement to bypass snowflake’s query caching feature would result in no change in performance).

The problem is, we can’t disable query caching, as when this query moves to production it will be using a much bigger volume of data / plus it would affect all queries running against the corresponding universe.

It appears that in Snowflake the query resolves quite quickly, but the latency required for the bobj report to complete is really unacceptable.
Has anyone out there had experience with this kind of performance issue and what can be done to improve our throughput?

I don’t have the actual query text being constructed by bobj (and bobj can produce some pretty wild query syntax), but I am aware that there are some restrictions to query caching, and we are adhering to all of the qualifications for access to cached query result set in Snowflake. *however I have read that even satisfying these does not guarantee that query caching will be used.

  • The new query syntactically matches the previously-executed query.
  • The query does not include functions that must be evaluated at execution time (e.g. CURRENT_TIMESTAMP() and UUID_STRING()). Note that the CURRENT_DATE() function is an exception to this rule; even though CURRENT_DATE() is evaluated at execution time, queries that use CURRENT_DATE() can still use the query reuse feature.
  • The query does not include external functions.
  • The table data contributing to the query result has not changed.
  • The persisted result for the previous query is still available.
  • The role accessing the cached results has the required privileges.
  • If the query was a SELECT query, the role executing the query must have the necessary access privileges for all the tables used in the cached query.
  • Any configuration options that affect how the result was produced have not changed.
  • The table’s micro-partitions have not changed (e.g. been reclustered or consolidated) due to changes to other data in the table.

Any advice would be really helpful to us.

Thanks!
Dave Hays

#2

Are you comparing the time to return first row with the time it takes BO to display the report? BO timing is once all rows are returned and the results have been rendered. To this end, how many rows does the query return?

#3

Hi Mark,
I should have been more specific -
The query runs and we watch it complete in the snowflake database consistently in less than 2 seconds. (1.6 - 1.7 seconds).
It returns 37 rows.
But we wait for almost 3 minutes for BOBJ to present the report to the BI Launchpad Browser window.
We see exactly the same behavior if we create and run the query in IDT. I am at a complete loss to explain the latency. Other reports that use the same universe and connection, using some of the same snowflake tables do not exhibit this behavior.

Any thoughts?

-dave

#4

Don’t worry, there’s a standard set of things that I usually ask as part of a report performance triage.

The rows returned and timing comparison was the first step but we’re clearly discounting that.

Do the rows render correctly in a standard Snowflake SQL tool in a timely manner?

If I was trying to recreate, I’d build up the report table at a time to see what the tipping point is.

#5

did you run “the query” manually in Snowflake or do you watch the original BO-request ?

are there only these 37 rows returned/transfered to BO or is there a aggregation in SF when running the query there
and not when running from remote ?
→ check “when” and “how many” rows are finally transfered to BO