雪花 sproc 与独立 sql [英] snowflake sproc vs standalone sql
问题描述
我正在考虑为我们的 BI 目的创建非规范化表.
I am thinking to create denormalized table for our BI purpose.
在从多个表创建业务逻辑时，我注意到当使用如下合并语句批量更新非规范化表(具有多个业务逻辑 SQL 的 sproc)时，查询性能更好.
While creating business logic from several tables i noticed queries perform better when denormalized table is updated in batches(sproc with multiple business logic SQL's) with merge statement as below.
例如:sproc 包含多个 SQL's like
eg: sproc contains multiple SQL's like
- 合并 denormalized_data(选择 businesslogic1)
- 合并非规范化数据(选择businesslogic2)
等
是将业务逻辑包含在庞大的 SQL 中还是将其拆分以便每个查询处理的行数更少?
Is it better to include business logic in huge SQL or divide it so that each query handles less number of rows?
如果我使用 sproc 会有任何开销吗?
Is there any overhead if I use sproc?
推荐答案
非常笼统.Snowflake 经过优化，可大批量工作.例如，我遇到过插入 1 条记录所需的时间与 100,000 条记录一样长的情况.所以插入 1 条记录 100,000 次会慢很多.
Speaking very generally. Snowflake is optimized to work in large batches. For example, I've had cases where it takes about as long to insert 1 record as 100,000 records. So inserting 1 record 100,000 times is a LOT slower.
肯定会有一些限制.应该拆分 1TB 批次.您的里程可能会因方式/时间/等而异.您正在更新表格.但总的来说，您会发现批处理的性能更高.
There is certainly going to be some limit. A 1TB batch should be split up. And your mileage may vary depending on how/when/etc. you are updating the table. In general though, you'll find batches are more performant.
我所知道的唯一真正的过程开销与将数据类型从 SQL 转换为 Javascript 并再转换回来有关，以及您必须如何管理输出.在大多数情况下，这并不重要.
The only real overhead that I know of for procedures has to do with converting data types from SQL to Javascript and back again, and then how you have to manage the output. In most cases, this won't be significant.
这篇关于雪花 sproc 与独立 sql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
