本文介绍了在SQL查询参数最大数量的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我不使用LINQ实验,因为一段时间。
典型的方法通过集合枚举,改变它的一些在我的代码性能将类似于:

  ATDataContext DC =新ATDataContext(Settings.connection_string); 

INT []列= ListViewClass.getListViewSelectedPositionTags(ListView控件);


{
的foreach(山坳中的VAR项)
{
VAR CTX =(从R IN dc.MailingLists
,其中r .ID ==项目
选择R)。单();

ctx.Excluded ='Y';
ctx.ExcludedComments =原因;
}

dc.SubmitChanges();
}



一段时间已经得到了一个建议通过做这个...好像更聪明的方式:

  VAR CTX =从dc.MailingLists 
r其中col.Contains(r.ID)
选择R;

的foreach(在CTX VAR项)
{
item.Excluded ='Y';
item.ExcludedComments =原因;
}

dc.SubmitChanges();



本品系让这么多层次感,我很喜欢这种解决方案。它的聪明,比第一次更快。



我已经用在生产环境中一段时间​​此解决方案。



的传入表格格式数据流(TDS)远程:p>什么,这是搜索应用程序日志文件,看的时候我数周后惊喜过程调用(RPC)协议流不正确。太多的参数,在此RCP请求中提供,最高2100。



LINQ to SQL的转换其中col.Contains(r.ID)条款看起来像:

WHERE ID IN(@ P1,@ P1,@ P2 ...)



山坳收集达(在我的情况),超过2100元,并且查询未能履行。我做了一些研究的问题,我结束了是:



......在SQL查询参数的最大数目为2100有是更多的限制,喜欢的事实,整个查询字符串不能超过8044个字符。



我爱第二个解决方案,因此许多。我与SQL Server的这些硬编码的限制太失望了。



我错过了什么?
是有什么我可以做的是能够使用,其中col.Contains(r.ID)的版本?



问候
马里乌什



PS。 (我用Win XP,C#与LINQ和2005年的SQL Express)


解决方案

该限制的:




  • 参数在每个存储过程2100

  • 每用户自定义函数2100
  • 参数



我写了一些代码之前那一刹那的包含查询转换成批次,合并结果...的。


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:

"The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Too many parameters were provided in this RCP request. The maximum is 2100."

The LINQ to SQL converts where col.Contains(r.ID) to INclause looking something like:
WHERE ID IN (@p1, @p1, @p2 … )

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:

"… Maximum number of parameters in the sql query is 2100. There is more limitations, like the fact that the whole query string cannot be longer than 8044 characters."

I have loved the second solution so much. I am so disappointed with these hard-coded limitations of the SQL Server.

Did I miss something?Is there anything I can do to be able to use the "where col.Contains(r.ID)" version?

RegardsMariusz

ps. (I use Win XP, C# with LINQ and SQL 2005 Express).

解决方案

The limits are hard-coded:

  • Parameters per stored procedure 2,100
  • Parameters per user-defined function 2,100

I wrote some code before that split the Contains query into batches and combined the results... see here for more.

这篇关于在SQL查询参数最大数量的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

08-19 02:11
查看更多