I am having a SP that is doing a select union of many tables with no lock. This will do lot of calculations with many groupings and sumation and so will take about 2 min for its run. I need to run this for about 500 different set of data. So It totally takes about 1000 min.
我有一个SP，它正在执行许多没有锁的表的选择联合。这将会做大量的计算和许多分组和总结，所以将花费大约2分钟的运行。我需要运行大约500个不同的数据集。所以总共需要1000分钟。
To reduce this I need to run this calculation in parallel. So if i run for 4 set of data in parallel I should be done in about 250 min.
为了减少这一点，我需要并行运行这个计算。如果我同时运行4组数据我应该在250分钟内完成。
But the problem is that once i am done with this calculations I get a set of data that i need to store into a table which has some primary key constraints on 4 of its columns. So when running in parallel I am expecting some dead lock issues. So I was thinking of writing this data to a temporary table that will have the same columns but with no primary key or any constraints.
但问题是，一旦我完成了这些计算，我就会得到一组数据，我需要将它们存储到一个表中，这个表对它的4列有一些主键约束。因此，当并行运行时，我预计会出现一些死锁问题。所以我想把这些数据写到一个临时表中，这个临时表有相同的列，但是没有主键或任何约束。
So with this I am expecting there should be no dead lock. Please advice in this regards and let me know if my understanding is right. Thank you.
所以我希望不会有死锁。请在这方面给我一些建议，让我知道我的理解是否正确。谢谢你！
1 个解决方案
#1
4
INSERT cannot deadlock against another INSERT on the same table, because two INSERT statement on the same table will always acquire the locks in the same order. That being said, I must comment on your statement:
INSERT不能针对同一表上的另一个INSERT执行死锁，因为同一表上的两个INSERT语句总是以相同的顺序获取锁。尽管如此，我必须对你的发言作出评论:
if i run for 4 set of data in parallel I should be done in about 250 min.
如果我同时运行4组数据，我应该在250分钟内完成。
You have no reason to expect this. First and foremost such a claim denotes you ignore Amdahl's law. Second a SQL workload is already parallelism internally, the queries in your procedure use parallel plans whenever possible, see Parallel Query Processing, especially if it contains many 'groupings and summations'.
你没有理由期待这一切。首先，这种说法表明你忽略了Amdahl定律。其次，SQL工作负载在内部已经是并行的，过程中的查询尽可能使用并行计划，请参阅并行查询处理，特别是如果它包含许多“分组和汇总”。
Ultimately what you're facing is an optimization task. Approach it like any other optimization task, identify the bottlenecks first. Waits and Queues is a must read as you embark this journey.
最终你面临的是一个优化任务。与其他优化任务一样，首先识别瓶颈。等待和排队是你踏上这段旅程的必备品。
#1
4
INSERT cannot deadlock against another INSERT on the same table, because two INSERT statement on the same table will always acquire the locks in the same order. That being said, I must comment on your statement:
INSERT不能针对同一表上的另一个INSERT执行死锁，因为同一表上的两个INSERT语句总是以相同的顺序获取锁。尽管如此，我必须对你的发言作出评论:
if i run for 4 set of data in parallel I should be done in about 250 min.
如果我同时运行4组数据，我应该在250分钟内完成。
You have no reason to expect this. First and foremost such a claim denotes you ignore Amdahl's law. Second a SQL workload is already parallelism internally, the queries in your procedure use parallel plans whenever possible, see Parallel Query Processing, especially if it contains many 'groupings and summations'.
你没有理由期待这一切。首先，这种说法表明你忽略了Amdahl定律。其次，SQL工作负载在内部已经是并行的，过程中的查询尽可能使用并行计划，请参阅并行查询处理，特别是如果它包含许多“分组和汇总”。
Ultimately what you're facing is an optimization task. Approach it like any other optimization task, identify the bottlenecks first. Waits and Queues is a must read as you embark this journey.
最终你面临的是一个优化任务。与其他优化任务一样，首先识别瓶颈。等待和排队是你踏上这段旅程的必备品。