Parallel ForEach with SQL inserts c#

Question

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>

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.

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

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

Parallel executions of SQL need their own connection and transaction. If you need to contain everything in one transaction you might look into TransactionScope as well. — Lasse V. Karlsen
– Lasse V. Karlsen, Commented Jun 22, 2018 at 21:14
Don't use @@IDENTITY. 99% of the time you wanted SCOPE_IDENTITY learn.microsoft.com/en-us/sql/t-sql/functions/… — TyCobb
– TyCobb, Commented Jun 22, 2018 at 21:16
And parallel foreach might not help you if your bottleneck is on the DB side. Instead it might even generate more overhead and slows you down. — Steve
– Steve, Commented Jun 22, 2018 at 21:37
@paparazzo, it's about the idea, not the code ... I pointed to simple code scenarios, I am just thinking about the idea and the approach which I should go through — Ahmed Negm
– Ahmed Negm, Commented Jun 22, 2018 at 21:49
Use SCOPE_IDENTITY(), if that doesn't solve your problem you will need to rewrite your stored procedure as then it must be inserting into multiple tables. But as others have said, you really should post some code. The idea is sound, the implementation might be flawed but we have no way of knowing this, so we're left with guessing. — Lasse V. Karlsen
– Lasse V. Karlsen, Commented Jun 23, 2018 at 13:53

Larissa Savchekoo · Accepted Answer · 2019-01-18 13:23:25Z

1

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

answered Jan 18, 2019 at 13:23

Larissa Savchekoo

7,9021 gold badge15 silver badges7 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Parallel ForEach with SQL inserts c#

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related