如何处理大型数据集 ssis 的增量负载
我有 2 个表（约 400 万行），我必须对匹配和不匹配的记录执行插入/更新操作。 我对增量加载必须使用的方法感到非常困惑。 我应该使用 Lookup 组件还是新的 sql server merge 语句？ 会不会有太大的性能差异？
I have 2 tables (~ 4 million rows) that I have to do insert/update actions on matching and unmatching records. I am pretty confused about the method I have to use for incremental load. Should I use Lookup component or new sql server merge statement? and will there be too much performance differences?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
我已经遇到过这个问题好几次了，而且我总是不得不通过 ETL 将完整的数据集加载到 SQLserver 中，然后使用存储过程进行操作。 在 SSIS 转换中动态更新数据似乎总是花费太长时间。
I've run into this exact problem a few times and I've always had to resort to loading the complete dataset into SQLserver via ETL, and then manipulating with stored procs. It always seemed to take way, way too long updating the data on the fly in SSIS transforms.
SSIS Lookup 具有三种缓存模式，这是获得最佳性能的关键。 如果您正在查找一个大表，FULL Cache 模式将占用大量内存并可能降低性能。 如果您的查找目标很小，请将其保留在内存中。 您还必须确定您正在查找的数据在处理数据时是否发生变化。 如果是，那么您不想缓存。
您能否向我们提供有关您的需求的更多信息，以便我可以制定更准确的答案。
The SSIS Lookup has three caching modes which are key to getting the best performance from it. If you are looking up against a large table, FULL Cache mode will eat up a lot of your memory and could hinder performance. If your lookup destination is small, keep it in memory. You've also got to decide if the data you are looking up against is changing as you process data. If it is, then you don't want to cache.
Can you give us some more info on what you are oding so I can formulate a more precise answer.
过早的优化是万恶之源，我不了解 ssis，但考虑这个总是太早了。
400 万行可能是“大”或“小”，具体取决于数据类型以及您使用的硬件配置。
Premature optimization is the root of all evil, I don't know about ssis, but it's always to early to think about this.
4 million rows could be "large" or "small", depending on the type of data, and the hardware configuration you're using.