How to pass .NET Collection of objects (Parent-Child) hierarchy to a SQL Server stored procedure

Question

I need to pass a .NET Collection to a stored procedure. My collection contains 1000 records. Each record contains 2-3 child objects of other types.

All I need to insert them at once by calling stored procedure from .NET.

I already have tried a TVP (table-valued parameter), but that simply doesn't fulfills my needs.

For each and every root level record, I need to call stored procedure by passing root record and it's corresponding child records. This is making the whole process very slow.

That's why I am looking a way, so that I can pass whole hierarchical list at once, and inside stored procedure, by looping and iterating child records I can insert parent child records.

How can I achieve this?

Try to use XML and unpack it to temp table in stored procedure. It works pretty fast for me. — Yuriy Tseretyan, Dec 23 '16 at 18:08
Table valued parameters are a nice way to do so. Just pass 2 tables, one for parent other for child, at once and some key to associate them. — Alejandro, Dec 23 '16 at 18:24
@Alejandro : Currently , I am passing in almost same way. But rather passing whole parent as one table and whole children as other, I am passing one parent record along with its correspnding list of children. But this is really slow, because, I need to insert 100K parent record. Furthermore, for each single parent there are 3-4 children associated with it. — Usman, Dec 23 '16 at 19:11
That's a possible source of problems. Try doing them all at once, instead of going one by one. I suppose that most of the overhead comes from the repeated network roundtrips instead of the query themselves. — Alejandro, Dec 23 '16 at 19:32
Yeah that's what I know.So, the solution then to pass whole hierarchy at once and then let the SQL do it's stuff internally. So the problem just boils down : How to pass the whole hierarchy at once (e.g List consignments, where each consignment contains List) — Usman, Dec 23 '16 at 19:48

score 3 · Accepted Answer · edited May 23 '17 at 12:01

3

I actually just did this 3 weeks ago.

create a "temp" association key (I used a GUID) to link the parent and children together because you don't have database ids (the temp key is never saved in the database)
Call a TVP stored procedure. Pass the all of the parents and all of the childred in two separate table vars
create a temp table or table var to store the tempid/database id relationship
Use the merge statement with OUTPUT to get the Inserted.Id value and temp id into the temp table created in step 3
use a CTE and merge statement to insert the child records using the actual DB id's from the parent record

Here are a couple of links about the process with "real" examples:

merge parent and child SQL Server tables

T-SQL - Insert Data into Parent and Child Tables

edited May 23 '17 at 12:01

Community

1
1

answered Dec 23 '16 at 18:09

Keith

1,119
2
12
23

Keith : Could you just let me know how to pass Parent-Child hierarchical list from .NET to Stored procedure? I don't want to break parent child hierarchy while passing whole list. Because, later on it would be difficult to associate each children with its corresponding parent entity. – Usman Dec 23 '16 at 19:03
I've written code samples in my answer (the second link in this one), but only to the sql part. Do you need the .net side sample? – Zohar Peled Dec 24 '16 at 12:35
Yeah if you could manage detail answer , would be really nice :-) – Usman Dec 25 '16 at 00:46
@Usman Do you know how to populate data tables from your list? – Zohar Peled Dec 25 '16 at 06:47
Yes I know how can I populate datatable in c# for passing it to stored procedure as TVP – Usman Dec 25 '16 at 09:08
@Usman Ok, so what exactly do you need an example of? If you can create data tables from your list and understand how to treat the 2 table valued parameters inside the stored procedure, then where exactly is the problem? – Zohar Peled Dec 25 '16 at 09:30
Zoher : Problem is, I cannot pass each root level entity along with it's children. I have 100K root level entities and then for each one root level entity you could imagin 3 children of ine type and 3 for another type. So, If I employee a loop like foreach(Consignment c in List) // will run for 100K times { children_1 = c.ConsignmentLines children_2 = c.ConsignmentAddress Call_Sp ( c, children_1,children_2 ) } This is damn slow appraoch. – Usman Dec 25 '16 at 10:38
I need something following : Call_Sp ( AllConsignments ) at once. Internally SQL server should iterate all consignments one by one, for each consignment should build consignment lines and other children. – Usman Dec 25 '16 at 10:42
But you don't need to run this procedure for each root level object. the entire point of this answer (as well as the one I wrote in the link) is to pass all the roots and all the children at once to the sql server using table valued parameters... – Zohar Peled Dec 25 '16 at 10:52
Ok . so from post http://stackoverflow.com/questions/34826450/fastest-way-to-perform-nested-bulk-inserts-with-scope-identity-usage/34832231#34832231 I have got an impression before passing parent and children to TVP, in application you already kept a temporary id both at base and child type so that you can associate themselves later in sql while doing merge. right? – Usman Dec 25 '16 at 11:55
Without keeping these temporary id, would be entirely difficult. So, I think how can I keep these id's at both side for associating them in SQL. – Usman Dec 25 '16 at 12:04
for the TVP pass the root objects as one parameter, pass all child objects that would end up in the same table as a 2nd parameter, and so on. If you are passing just the hierarchy into your C# method call, its easy enough to use LINQ to project out what you need into different IEnumerables. I could write a C#/SQL example, but it would take ~2 hours and it would be enough code that it would probably be hard to fit/organize on the site. It might be a slow week for me, so if you haven't figured it out, let me know and I'll try to put something together. – Keith Dec 27 '16 at 14:55
Keith and Zohar : Thanks guys for your value able time and comments. You were extremely right !! I did the same , all I managed temporary unique keys inside child records those are somehow exist in parent. Later I used in sql , MERGE statement and was able to insert 900K records within 8-9 minutes. Lastly ! Could you let me know what is the difference between SELCT/INSERT and MERGE? I mean MERGE is upsert but if we are just inserting, why to use MERGE? Is it more fast and optimized than simple select/Insert? – Usman Dec 27 '16 at 20:18
I figured out myself : I used existing column from my base type and generated tempId and held that there. So, didn't require tempId any more at TVP side for parent udt. – Usman Dec 28 '16 at 16:43
The reason you use the merge statement instead of insert is because it can output both source and target values. Thus you can create a "Rosetta stone" mapping between temp ids and generated db ids. With a regular insert statement you cannot do that. BTW 8-9 minutes seems absurdly long. I regularly do ~10-20K parent records and ~100K child records, and it takes 4-8 SECONDS to both parent and children. – Keith Dec 30 '16 at 20:38
Keith : My parent level record has 80 columns. Let me share my parameters and stats. Parent-collection no. records - 130606, child-one no of records : 132077, child-second no of records : 261565 , child-third no of records : 391818, child-fourth no of records : 130606 and last-child : 117021 While, I am reading 1000 records from excel file in single chunk, while having 131 chunks altogether. After inserting each chunk, I am disposing corresponding data table object and creating again for next chunk and so on. Because, I would like to balance memory and CPU in the whole process – Usman Jan 02 '17 at 10:35
ok, so you are making ~130+ round trip calls to the database. My volume isn't that high (30K Parent, 50K Child, 100K grand child, 30K great grand child none of which have more than 20 columns), but I use IEnumerable when creating the SQL Parameters and it streams out to the table var, so my memory consumption has never been an issue (it grows by about 1-3MB). You might want to try that and see if it makes a difference. – Keith Jan 04 '17 at 21:19

Yuriy Tseretyan · Answer 2 · 2016-12-23T18:34:27.780

You can use XML to pass records as well as their children to stored procedure. For example

DECLARE @xml XML = '<root>
<parent value="213">
    <child childValue="1111"></child>
    <child childValue="1112"></child>
</parent>
<parent value="2313">
    <child childValue="3333"></child>
    <child childValue="3332"></child>
</parent>
</root>'

Then you can do many ways, one of them is to denormalize records and save to temp table for further processing

    SELECT
    T.c.value('parent::*/@value', 'INT') AS ParentValue,
    T.c.value('@childValue', 'INT') AS ChildValue
    FROM @xml.nodes('/root[1]/parent/child') T(c)

Result will look like

    ParentValue  ChildValue
    213          1111
    213          1112
    2313         3332
    2313         3333

I am sorry but I can't use XML at all. This is extra over head in the application. I am working with 100K records at a time and constructing in memory xml with Single parent records consisiting of 80 columns containing 10 column children would be extremely bad idea. — Usman, Dec 23 '16 at 18:47

How to pass .NET Collection of objects (Parent-Child) hierarchy to a SQL Server stored procedure

2 Answers2