与SQL并行的ForEach插入C＃ [英] Parallel ForEach with SQL inserts c#

问题描述

我有一个像下面这样的对象，但是有大量数据，我们观察到插入SQL数据库需要很长时间，因为我们使用的是普通的 foreach ，主要思想是插入每个部门并获取生成的标识号，然后插入分配有该部门ID的嵌套员工。

I have an object like the below but with a huge amount of data, we observed that it takes a long to be inserted into our SQL database as we were using normal foreach, the main idea is to insert each Department and get the generated identity number, then insert the nested employees assigned with that department ID.

<Sample>
    <Departments>
        <Department>
            <Name>HR</Name>
            <Employees>
                <Employee>
                    <Name>Marco</Name>
                </Employee>
                <Employee>
                    <Name>John</Name>
                </Employee>
                <Employee>
                    <Name>Sarah</Name>
                </Employee>
            </Employees>
        </Department>
        <Department>
            <Name>IT</Name>
            <Employees>
                <Employee>
                    <Name>Ali</Name>
                </Employee>
                <Employee>
                    <Name>Roberto</Name>
                </Employee>
                <Employee>
                    <Name>Franco</Name>
                </Employee>
            </Employees>
        </Department>
    </Departments>
</Sample>

我们尝试使用 Parallel.ForEach 来可以提高性能，但确实如此，但是 @@ IDENTITY 引起了另一个问题，因为Parallel.ForEach中的运行任务之间存在重叠，因为我们发现分配了一名员工

We tried to use Parallel.ForEach to enhance the performance, and it does, but we got another issue with @@IDENTITY because there is an overlap between the running tasks in Parallel.ForEach as we found an employee is assigned to another department.

需要 ...我需要使用 Parallel.ForEach来加快流程速度。 或 foreach ...有什么想法吗？

The need ... I need to speed the process up either I use Parallel.ForEach or foreach ... any ideas?

BTW ...使用常规 ADO.NET

BTW ... we are calling a stored procedures which contains normal INSERT INTO command using classing ADO.NET

推荐答案

您可以将guid用作表的主键。这将帮助您避免@@ IDENTITY的问题。
首先，您应该生成新的Guid-identity，感谢将生成的值插入一行

You could use guid as primary key into your table. It would help you to avoid problem with @@IDENTITY. At first you should generate new guid-identity, thank insert the generated value into a row

这篇关于与SQL并行的ForEach插入C＃的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！