Hi,
I developed a Power BI data model with data from Playfab via Snowflake add-on 2-3 weeks ago. It worked perfectly and 2,8M rows were loaded in 4min roughly (XS size). I wanted refresh it today, but the query didn't finish (I stopped it in Snowflake after 20min). Size of warehouse doesn't influence the performance (tried XS,S,M). It loads quite quickly 2,7M rows (3-4min), but then it nearly stop to work (eg. 100 rows in 10min). Is the add-on working correctly?
I'm going to follow up with our analytics team and investigate. Can you let me know which title you're working on? Also, could you share the query you're running?
It's title_ID 'A71E'. Query is generated by PBI Desktop through ODBC driver. In Snowflake is visible as:
select "EVENT_ID",
"EVENT_NAMESPACE",
"EVENT_NAME",
"ENTITY_ID",
"SOURCE_TYPE",
"TS",
"HISTORY",
"CUSTOM_TAGS",
"RESERVED",
"P",
"TITLE_ID"
from "PLAYFAB_SHARED"."PLAYFAB_SHARED"."PLAYFAB_ARCHIVE_SHARED"
where "EVENT_NAME" = ? and not "EVENT_NAME" is null
? is bind variable, should be 'player_logged_in'. Making explicit view with title_id in where clause as recommended in your docs ("TITLE_ID" = 'A71E') causes extremely slow query anyway - 20k rows in 7mins. And adding "TS" > '2018-08-14' leads to 100 rows in 5mins.
We're looking into the clustering of the master playstream table right now. We've seen a steady increase in bad partitions over the last couple days. It's not affecting large titles, but could have some impact on smaller ones. I'll get back to you when I know more.
It's definitely best practice to include both a TitleId and a timestamp range on queries. If you're seeing poor behavior with those included, I consider it a bug on our side.
We found and corrected the issue. Everyone should see query times more in line with expectations today.
Hi Andy, worked perfectly on Friday (August 24), but poor performance as before today unfortunately.
Thanks for letting us know. The additional alerts we're putting in place for snowflake performance aren't quite ready and it looks like this is a recurrence of the same problems we faced last week. We'll try to get the response times back in line with expectations ASAP.
I've been doing some testing on this over the last couple days. I'm currently able to load the last day of your login data in about 90 seconds. If I expand to the last month, it goes up to 15 minutes. Speaking with the analytics team, they seem okay with these numbers.
I encourage you to continue to use a TitleId and TS filter on queries. When the system is behaving properly, it should help query times.
Hi Andy, running just now this query:
select "EVENT_ID",
"ENTITY_ID",
"TS",
"P",
"TITLE_ID"
from "PLAYFAB_SHARED"."PLAYFAB_SHARED"."PLAYFAB_ARCHIVE_SHARED"
where "EVENT_NAME" = 'player_logged_in' AND "TITLE_ID" = 'A71E' AND "TS" > '2018-08-16'
and stopped after 10min as it retrieves 1,3K rows only. Which size of WH did you use that you finished in 90s? Thanks and regards
2 People are following this question.
