我有一个流程可以抓取一堆记录(1000 条)并对它们进行操作,完成后,我需要将其中的大量记录标记为已处理。我可以用一大串 ID 来表明这一点。我试图避免“循环更新”模式,所以我想找到一种更有效的方法将这个 ID 包发送到 MS SQL Server 2008 存储过程中。
建议 #1 - 表值参数。我可以定义一个表类型,只包含一个 ID 字段,然后发送一个充满 ID 的表来更新。
建议 #2 - proc 正文中带有 OPENXML() 的 XML 参数 (varchar)。
建议 #3 - 列表解析。如果可能的话,我宁愿避免这种情况,因为它看起来笨重且容易出错。
这些中的任何偏好,或者我错过的任何想法?
Erland Sommarskog 撰写的关于此问题的最佳文章:
他涵盖了所有选项并且解释得很好。
很抱歉答案很短,但是 Erland 关于数组的文章就像 Joe Celko 的关于树和其他 SQL 处理的书 :)
There is a great discussion of this on StackOverflow that covers many approaches. The one I prefer for SQL Server 2008+ is to use table-valued parameters. This is essentially SQL Server's solution to your problem--passing in a list of values to a stored procedure.
The advantages of this approach are:
However, take note: If you call a stored procedure that uses TVPs via ADO.NET or ODBC and take a look at the activity with SQL Server Profiler, you will notice that SQL Server receives several
INSERT
statements to load the TVP, one for each row in the TVP, followed by the call to the procedure. This is by design (link broken), it uses an efficient insert similar to BCP (see also Dan Guzman on this). This batch ofINSERT
s needs to be compiled every time the procedure is called, and constitutes a small overhead. However, even with this overhead, TVPs still blow away other approaches in terms of performance and usability for the majority of use cases.If you want to learn more, Erland Sommarskog has the full skinny on how table-valued parameters work and provides several examples.
Here is another example I concocted:
The entire subject is discussed on the definitive article by Erland Sommarskog: "Arrays and List in SQL Server". Take your pick of which version to choose.
Summary, for pre SQL Server 2008 where TVPs trump the rest
The article is worth reading anyway to see other techniques and thinking.
Edit: late answer for huge lists elsewhere: Passing array parameters to a stored procedure
I know I am late for this party, but I had such a problem in the past, having to send up to 100K bigint numbers, and did a few benchmarks. We ended up sending them in binary format, as an image - that was faster than everything else for up to 100K numbers.
Here is my old (SQL Server 2005) code:
The following code is packing integers into a binary blob. I am reversing the order of bytes here:
我在将您推荐给 SO 或在这里回答之间犹豫不决,因为这几乎是一个编程问题。但是因为我已经有了一个我使用的解决方案......我会发布它;)
这个工作的方式是将逗号分隔的字符串(简单拆分,不进行 CSV 样式拆分)作为 varchar(4000) 输入存储过程,然后将该列表输入此函数并返回一个方便的表格,一张只有 varchars 的表。
这允许您只发送您想要处理的 id 的值,然后您可以在此时进行简单的连接。
或者,您可以使用 CLR DataTable 执行某些操作并将其输入,但这需要更多的支持,并且每个人都理解 CSV 列表。
I regularly receive sets of 1000s of rows and 10000s of rows sent from our application to be processed by various SQL Server stored procedures.
To meet the performance demands, we use TVPs, but you must implement your own abstract of the dbDataReader to overcome some performance issues in its default-mode of processing. I will not go into the hows and whys as they are out of scope for this request.
I did not considered XML processing as I have not found an XML implementation which remains performant with more than 10,000 "rows".
List processing can be handled by single-dimension and double-dimension tally (numbers) table processing. We have used successfully used these in various areas, but well-managed TVPs are more performant when there are more than a couple hundred "rows".
As with all choices regarding SQL Server processing, you have to make your choice selection based on the usage model.
I finally got a chance to do some TableValuedParameters and they work great, so I'm going to paste a whole lotta code that shows how I'm using them, with a sample from some of my current code: (note: we use ADO.NET)
Also note: I'm writing some code for a service, and I've got lots of predefined code bits in the other class, but I'm writing this as a console app so I can debug it, so I ripped all this from the console app. Excuse my coding style (like hardcoded connection strings) as it was sort of "build one to throw away". I wanted to show how I use a
List<customObject>
and push it into the database easily as a table, that I can use in the stored procedure. C# and TSQL code below:Also, I'll take constructive criticism on my coding style if you have that to offer (to all readers that come across this question) but please keep it constructive ;) ... If you really want me, find me in the chatroom here. Hopefully with this chunk of code one can see how they can use the
List<Current>
as I have it defined as a table in the db and aList<T>
in their app.我要么选择提案#1,要么创建一个只保存已处理 ID 的暂存表。在处理过程中插入该表,然后一旦完成,调用类似于下面的 proc:
你会做很多插入,但他们会在一张小桌子上,所以它应该很快。您还可以使用 ADO.net 或您正在使用的任何数据适配器批量插入。
The question title includes the task to transmit data from an application into the stored procedure. That part is excluded by the question body, but let me try to answer this too.
In context of sql-server-2008 as specified by the tags there is another great article by E. Sommarskog Arrays and Lists in SQL Server 2008. BTW I found it in the article Marian referred to in his answer.
Instead of just giving the link, I quote its list of content:
Beyond the techniques mentioned there, I have the feeling that in some cases bulkcopy and bulk insert deserve to be mentioned to scope with the general case.
For MS SQL 2016 latest version
With MS SQL 2016 they introduce a new function : SPLIT_STRING() to parse multiple values.
This can solve your problem easily.
For MS SQL Older Version
If you are using older version, than follow this step:
First Make one function:
After making this, Just pass your string to this function with separator.
I hope this is helpful to you. :-)