NHibernate限制.具有数百种价值

问题描述

我应该关注数组中具有数百个值的IN表达式吗?例如:

myCriteria.Add(Restrictions.In("Id", myArrayOfHundredsOfItems);

哪个会导致sql:

select * from MyTable where Id in (1,2,3,4, ...etc .. 900)

MSDN说 "IN子句中的值(成千上万)会消耗资源并返回错误8623或8632."

好的，所以成千上万"是不可能的.是否有任何理由避免使用可能包含数百个项目的数组?

解决方案

还有另一个限制. IN子句中的值作为单个参数传递.参数有限制. 与存储过程相同为2100.

我遇到类似的情况，我通过了大量的Guid.我将其分为几个查询，每个查询最多包含1000个参数.这些查询将作为多个条件批量执行，因此只有一次数据库往返.

与联接相比，使用IN时，性能可能会受到影响.在我的方案中，因此没有任何性能问题.如果您没有那么多参数，那么对您来说就很可能不是问题.

顺便说一句，通过id获取对象也可以通过以下方式实现:

• Get<Entit>(id)，它只需要一个查询，但仅当实体尚未在缓存中时才可以.
• Load<Entity>(id)，如果实体不在缓存中并且仅在访问属性时才加载，它将创建一个代理.它很可能使我们具有批处理大小，并一次加载多个实例(预取).

不幸的是，没有未来的收获.

Should I be concerned about IN expressions with hundreds of values in the array? For example:

myCriteria.Add(Restrictions.In("Id", myArrayOfHundredsOfItems);

Which results in the sql:

select * from MyTable where Id in (1,2,3,4, ...etc .. 900)

MSDN says "Including an extremely large number of values (many thousands) in an IN clause can consume resources and return errors 8623 or 8632."

OK, so 'many thousands' is out of the question. Is there any reason to avoid an array of maybe a few hundred item?

解决方案

There is another limit. The values in the IN clause are passed as single parameters. There is a limit of parameters. It is probably the same as for stored procedures which is 2100.

I have a similar situation where I pass a large amount of Guids. I split it up in several queries, each of them getting up to 1000 parameters. The queries are executed as multi criteria in a batch, so there is only a single database roundtrip.

Performance may suffer when using IN compared to joins. In my scenario I don't have any performance issues because of that. If you don't have that many parameters, it most probably won't be an issue for you.

By the way, getting objects by id could also be implemented by:

• Get<Entit>(id), which requires a single query, but only when the entity isn't in the cache yet.
• Load<Entity>(id), which creates a proxy if the entity isn't in cache and only loads in when accessing properties. It most probably makes us of batch-size and loads several instances at once (pre-fetching).

Unfortunately, there isn't a future-get.

这篇关于NHibernate限制.具有数百种价值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！