sql查询中的最大参数数 [英] maximum number of parameters in sql query
问题描述
一段时间以来，我一直在尝试 LINQ.在我的代码中枚举集合并更改其某些属性的典型方法如下所示:
I do experiment with LINQ since some time. Typical method to enumerate through a collection and change some of its properties in my code would look like:
ATDataContext dc = new ATDataContext(Settings.connection_string);
int[] col = ListViewClass.getListViewSelectedPositionTags(listView);
try
{
foreach (var item in col)
{
var ctx = (from r in dc.MailingLists
where r.ID == item
select r).Single();
ctx.Excluded = 'Y';
ctx.ExcludedComments = reason;
}
dc.SubmitChanges();
}
有一段时间，有人建议这样做……似乎是更聪明的方法:
Some time a have got an advice to do this by... seems like much smarter way:
var ctx = from r in dc.MailingLists
where col.Contains(r.ID)
select r;
foreach (var item in ctx)
{
item.Excluded = 'Y';
item.ExcludedComments = reason;
}
dc.SubmitChanges();
它在很多层面上都有意义，我喜欢这个解决方案.它比第一个更智能、更快.
Iit makes sense on so many levels and I love this solution. It’s smart and faster than the first one.
我在生产环境中使用这个解决方案有一段时间了.
I have used this solution in a production environment for some time.
几周后，当我搜索应用程序日志文件并看到这个时，我感到惊讶的是什么:
What was my surprise after few weeks when searching an application log files and see this:
传入的表格数据流 (TDS) 远程过程调用 (RPC) 协议流不正确.此 RCP 请求中提供的参数过多.最大值为 2100."
LINQ to SQL 将 where col.Contains(r.ID) 转换为 IN 子句，如下所示:
在哪里 ID (@p1, @p1, @p2 ... )
The LINQ to SQL converts where col.Contains(r.ID) to INclause looking something like:
WHERE ID IN (@p1, @p1, @p2 … )
col 集合达到(在我的例子中)超过 2100 个元素并且查询未能执行.我对这个问题做了一些研究，结果是:
The col collection reached (in my case) more than 2100 elements and the query failed to perform. I have done some research on the problem and what I ended up is:
…… sql 查询中的最大参数数为 2100.还有更多限制，例如整个查询字符串不能超过 8044 个字符."
我非常喜欢第二种解决方案.我对 SQL Server 的这些硬编码限制感到非常失望.
I have loved the second solution so much. I am so disappointed with these hard-coded limitations of the SQL Server.
我错过了什么吗?我能做些什么才能使用where col.Contains(r.ID)"版本?
Did I miss something? Is there anything I can do to be able to use the "where col.Contains(r.ID)" version?
问候马里乌斯
ps.(我使用 Win XP、C# 和 LINQ 和 SQL 2005 Express).
ps. (I use Win XP, C# with LINQ and SQL 2005 Express).
推荐答案
限制 是硬编码的:
- 每个存储过程的参数 2,100
- 每个用户定义函数的参数 2,100
我之前写了一些代码，将 Contains 查询分成批次并合并结果... 更多信息请看这里.
I wrote some code before that split the Contains query into batches and combined the results... see here for more.
这篇关于sql查询中的最大参数数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
