Snowflake connect fetching data to table is pretty slow. Am i doing this right?

Question

question

herzock asked Jun 1, '18

Snowflake connect fetching data to table is pretty slow. Am i doing this right?

Hello,

i connected my playfab account to Snowflake to do data analysis.

I am running this query:

create or replace table LevelProgressTable as 
(
    select 
    event_id,
    title_id,
    ts,
    to_timestamp(p:Timestamp) as exact_ts,
    entity_id,
    to_varchar(p:appversion) as appversion,
    to_varchar(p:levelName) as levelName,
    TO_NUMBER(p:levelId) as levelId,
    to_varchar(p:spirit) as spirit,
    TO_NUMBER(p:spiritLevel) as spiritLevel,
    TO_NUMBER(p:damLevel) as damLevel,
    TO_BOOLEAN(p:won) as won,
    TO_NUMBER(p:score) as score,
    TO_NUMBER(p:movesLeft) as movesLeft,
    TO_NUMBER(p:triesNeeded) as triesNeeded,
    TO_BOOLEAN(p:extraMovesBought) as extraMovesBought,
    TO_NUMBER(p:playThroughs) as playThroughs
    from PLAYFAB_SHARED.PLAYFAB_SHARED.PLAYFAB_ARCHIVE_SHARED
    where event_namespace = 'title.XXXX' and event_name = 'player_completed_level'
    and ts >= dateadd(day, -1, current_date)
    and title_id = 'XXXX'
)

The query runs over 8 minutes although i only have 23 events of that type. I am using an S Warehouse.

Is it normal that it takes that long? Will it be even longer if i have more events? Or am i using the wrong keys maybe?

Greetings

Player Data data

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2018-06-02T02:46:59Z

brendan answered Jun 2, '18

One thing to know is that creation of tables will always take more time than a simple Select query. Can you try it as a Select and see how long it takes? If that's more than a few seconds, can you provide the Title ID, so that we can have a look?

3

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

herzock commented · Jun 03, 2018 at 02:16 PM

Hello, thank you for your answer. The titleid is: 7051.

The select took 9:23 minutes.

It seems it parses way more than data necessary for some reason.

0 ·

brendan herzock commented · Jun 04, 2018 at 01:40 AM

Hm. So I just tried the query as simply:

select * from PLAYFAB_ARCHIVE_SHARED where TITLE_ID = '7051' and EVENT_NAMESPACE = 'title.7051' and EVENT_NAME = 'player_completed_level' AND ts >= dateadd(day, -1, current_date)

And that only took just over a minute to run, which is about what I'd expect, given the size of the overall database. What size warehouse are you using, though? I'm using Large. A smaller warehouse will take longer.

0 ·

herzock commented · Jun 04, 2018 at 08:37 AM

Hmm okay, today the exact same query took only 2.49 minutes.

Which is ok for an S warehouse i guess. Maybe last week Snowflake was slow or something. The saving of worksheets was also broken last week.

I hope the query times will be more consistent in the future in snowflake otherwise it will be hard to optimize performance. :D

Thank you very much for your answer and the test query!

0 ·

question