[SOLVED] Snowflake database: Question on table performance which is stored in snowflake

Issue

We have a continuous insert, update, and delete in a table that is in snowflake DB, can this slow down the performance of a table in snowflake over the period of time?

Solution

Yes. For two reasons.

because the changes of the INSERT, UPDATE, & DELETE alter the fragment the partition data, thus even if the same number of ROW are present after N hours/days, the layout of the rows can become unaligned to the affinity of queries you run, thus your performance profile can go from highly prunes partition reads, to full table reads.

Also with large number of changes, even if the data is all perfectly ordered after then, the share fact many changes are being made with mean you end up with way too many partitions, which slows down you SQL compilations.

You also can have bad performance if you are INSERT, UPDATE, & DELETE to the same table at the same time, as the second operation will be blocked by the former. This can waste wall clock, and credit allocation (if they are different warehouses)

Some things you can do to avoid this, is run clustering, rebuild the tables in "down time". Not delete the data, but insert into "delete tables" and then left join and exclude matches. We have done all the above.

Answered By – Simeon Pilgrim

Answer Checked By – Marie Seifert (BugsFixing Admin)

Leave a Reply

Your email address will not be published. Required fields are marked *