如何将.NET对象（父子）层次结构集合传递给SQL Server存储过程 [英] How to pass .NET collection of objects (parent-child) hierarchy to a SQL server stored procedure

问题描述

您好，



我需要将.NET Collection传递给存储过程。我的收藏包含1000条记录。每条记录包含2-3个其他类型的子对象。



我需要通过调用.NET中的存储过程立即插入它们。



我已经尝试了一个TVP（表值参数），但这根本无法满足我的需求。



For每个根级别记录，我需要通过传递根记录和它的相应子记录来调用存储过程。这使得整个过程非常缓慢。



这就是为什么我正在寻找一种方式，以便我可以立即传递整个分层列表，并且在存储过程中，通过循环和迭代子记录我可以插入父子记录。



我怎样才能实现这一点？



我尝试过：



只需一个存储过程，需要两个TVP，一个用于父母，另一个用于相关的孩子

Hello,

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?

What I have tried:

Just a stored procedure taking two TVP , one for parent and other for it's related children

推荐答案

所以，你所说的是你有由父记录和子记录组成的结构化数据，而父模式是静态的，孩子们有不同的模式。



您是否打算使用单个存储过程粉碎数据并将其跨越数据库中的多个表？在单个存储过程中执行此操作的唯一可靠方法是将数据序列化为XML或JSON（如果您正在使用Sql Server 2016）并将存储过程切碎并将数据加载到各个表。

So, what you are saying is that you have structured data that consists of parent and child records, and while the parent schema is static, the children have differing schemas.

Is your intent to shred the data and span it across multiple tables in the database using a single stored procedure? The only reliable way to do this in a single stored procedure would be to serialize the data to either XML or JSON (if you are working with Sql Server 2016) and have the stored procedure shred and load the data to the various tables.

我从未遇到过sql server的性能问题，这些问题并非由我所做的事情引起的。我不使用表值参数。



您可以迭代列表并一次保存一个onbject，或者像建议的第一个答案一样，并序列化将所有数据转换为xml并将XML传递给存储过程。我喜欢自己单独调用，但这只是我。

I've never had performance issues with sql server that were not caused by something I've done. I don't use table valued parameters.

You can either iterate the list and save the onbjects one at a time, or do like the first answer suggested, and serialize all the data into xml and pass the XML to the stored proc. I prefer separate calls myself, but that's just me.

以前我使用实体框架在数据库中保存100K rocords，其中每个recod还包含子记录。



我的对象层次结构是：



公共类寄售



{



....



List< consignmentaddress>地址{get;设置;}



列表< consignmentline>线条{得到;组; }



}



我可以预期货物数量= 100K，每个货物可以包含2个ConsignmentLines平均3个寄售地址。



所以这大致= 100K + 200K + 300K = 600K记录。



为了插入所有这些600K记录，我使用了实体框架，这本来很慢。然后我将文件分块并解析1000条记录，并通过每次重新创建上下文使用EF插入。这是非常好的。但仍然没有完全满足我的要求（因为在未来我可以期待500K根级别记录，每个进一步可以在每个列表中包含3-4个子记录。这可以在数据库中创建1200K记录。）



现在我需要快速有效的解决方案。



这就是为什么我想立刻发送整个层次结构，以便我可以迭代SQL中的那个集合并插入父子记录。由于这将接近金属，它将比实体框架快10倍。

但是目前，我正在迭代这个List< consignment>收集来自.NET C＃in循环和每个寄售，以及它的子项（即List< consignmentaddress>，List< consignmentline>），我正在调用存储过程并将这3个作为TVP发送（1用于寄售，第2用于列表< consignmentaddress> ;，第3列表< consignmentline>）。这个程序也很慢。因为，对于100K根记录，100K存储过程调用正在进行。



现在我需要一个解决方案，它需要整个层次结构或至少一些块并推送所有通过保持整个父子关系完好无损的数据。

Previously I used entity framework for saving 100K rocords in database where each recod further contains child records.

my object hierarchy is :

public class Consignment

{

....

List<consignmentaddress> addresses {get; set;}

List<consignmentline> lines { get; set; }

}

I could expect Consignments Count = 100K and each Consignment could contain 2 ConsignmentLines and on average 3 Consignment Addresses.

So this all makes roughly = 100K + 200K + 300K = 600K records .

For inserting all these 600K records, I used entity framework and this was damn slow. Then I devided the file in chunks and parse 1000 records and inserted using EF by recreating context each time. This was very improved . But still that does not full fill my requirement ( as in future i could expect 500K root level records and each further could contain 3-4 child record in each list. That could make 1200K records in database).

Now I need fast and efficient solution.

That's why I want to send the whole hierarchy at once, so that i can iterate that collection inside SQL and insert the parent child records. As this would be close to metal , it would be 10 times faster than entity framework.
But at the moment, I am iterating this List<consignment> collection from .NET C# in loop and for each consignment, along with it's children ( i.e List<consignmentaddress>, List<consignmentline> ), I am calling stored procedure and sending these 3 as TVP ( 1 for Consignment, 2nd for List<consignmentaddress>, 3rd for List<consignmentline> ). This procedure is also very slow. Because, for 100K root records, 100K stored procedure calls are going.

Now I need a solution, which takes the whole hierarchy or at least some chunk and push all the data at once by keeping whole parent child relationship intact.

这篇关于如何将.NET对象（父子）层次结构集合传递给SQL Server存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！