Search Unity

  1. Unity 6 Preview is now available. To find out what's new, have a look at our Unity 6 Preview blog post.
    Dismiss Notice
  2. Unity is excited to announce that we will be collaborating with TheXPlace for a summer game jam from June 13 - June 19. Learn more.
    Dismiss Notice

Bug UGS Analytics query performance

Discussion in 'Unity Analytics' started by AlkisFortuneFish, Feb 13, 2024.

  1. AlkisFortuneFish

    AlkisFortuneFish

    Joined:
    Apr 26, 2013
    Posts:
    977
    We are in the process of migrating to UGS analytics from deltaDNA and have run into very severe query performance issues, both directly on the portal and with direct Snowflake access.

    Any query that filters on the event_json variant column is extremely slow, slow enough to not be viable.

    The issue would appear to be the use of secure views. After much documentation reading, I found that querying a variant column of a secure view does not pass the filter down to the underlying table, which of course results in all rows being returned and filtered after the fact. Looking at the query profile on Snowflake confirms that, a simple well targetted query returned over two billion rows that got filtered down to a few hundred results.

    This means that any queries against the analytics on anything that isn't one of the regular columns of the events table basically cannot be run.

    As an example:

    select count(distinct user_id) from EVENTS where CLIENT_VERSION = '1.25.2'
    -- Executes in 3-4s

    select count(distinct user_id) from EVENTS where event_json:clientVersionNumber:integer = 1250002
    -- Executes in 4-5 minutes

    This is the same with queries that are more targetted than that.

    I can make queries execute in more reasonable time by targetting them more aggressively (like selecting specific events and time periods), but this does mean that queries that used to run super fast on deltaDNA now need special treatment to even run at all, and probing queries where we look for things across multiple events cannot work at all.

    We are not sure how to proceed, any comments @Laurie-Unity?

    PS: There really needs to be a version integer in UGS Analytics, this has been a problem in deltaDNA that we have had to resolve with our clientVersionNumber parameter for the past decade, a string makes version range checks impossible.