We have a large number of databases and tables, and to ensure no accidents occur, we use a large string as the ID for each record, something like a GUID.

However, we now find we're experiencing problems when we have code like this:



where listIDs is a List<string> that contains something like 3000 items, each string being 20 characters long.

When I do a trace on SQL Server, these queries don't even make it to the server itself.

Please note that we managed to circumvent the problem by creating our own select query where we add line breaks and even split up the param list, but this leaves us vulnerable to sql injection -- and we'd rather have NHibernate automatically handle these kind of cases, instead of us going through all our apps and adding code that would tackle this issue just in case it ever arises there too.

Does anybody have an idea of why this happens? Is this an SQL Server limitation?

Yes, this is a SQL Server problem. SQL Server only allows 2100 parameters. When using an IN query every item in the provided list is turned in to a parameter. Therefore once you have more than 2000 or so items in the collection an error will be returned from SQL Server.

For a reference see the Microsoft Maximum Capacity Specifications at:

http://msdn2.microsoft.com/en-us/library/ms143432.aspx

and pay special attention to the section: "Parameters per stored procedure"

Note that sp_executesql is the stored procedure that NHibernate calls. The first two parameters are always the SQL and specification of parameters to be passed, so in reality your collection can't exceed 2098 items.

Hope that helps.

Doh, didn't think of that. Of course, that's most likely the issue.

It would be nice if this was detected and handled by NHibernate itself, of course.

I actually disagree with this statement. The parameters is how ADO.NET protects itself from SQL Injection attacks. If NHibernate didn't make use of these parameters you would have to duplicate all SQL Injection checks which could be a cause of concern.

I actually prefer the way it works. If you're performing a query with an In list like this you're probably negatively effective performance anyway. If there is an alternative set of parameters to query on you would be much better off. At this point SQL Server will basically need to scan the entire table and keep the records it finds. It could alternatively seek to the records, but it would have to perform thousands of seeks. SQL Server would then need to determine if thousands of fast operations is faster than a single slow operation.

There's another solution I've used which might be relevant. You concatenate all of the strings into a comma-separated list and then use a table-valued function to join against the original table.

The function for SQL Server looks like this...

ALTER FUNCTION [dbo].[Split]
(
@List varchar(MAX), --##PARAM @List The list of values to split.
@Delimiter char(1) --##PARAM @Delimiter The character to use as the delimiter
)
RETURNS TABLE
AS
RETURN
-- ##SUMMARY Converts a delimited list of values into a table.
/* ##REMARKS This technique allows a list of values to be supplied to a stored procedure
and for the query to be constructed explicitly rather than using sp_executeSql
which results in much better query plan evaluation.*/
SELECT LTRIM(SUBSTRING(string, n, CHARINDEX(@Delimiter, string + @Delimiter, n) - n)) AS Value
FROM (SELECT @List AS string) AS d
INNER JOIN nums ON n < LEN(string)
AND SUBSTRING(@Delimiter + string, n, 1) = @Delimiter


So your SQL query looks something like

SELECT *
FROM dbo.Order o
INNER JOIN dbo.OrderLine ol ON o.OrderId = ol.OrderId
INNER JOIN dbo.Product p ON p.ProductId = ol.ProductId
INNER JOIN dbo.Split(@ProductNames, ',') pn ON pn.Value = p.ProductName

Haven't had to try incorporating a custom function into HQL before, but I think it is possible.

Yes, I know, but my point it that I wish NHibernate would handle this: detect there are too many params and
- either throw a relevant error,
- or split up the query and do multiple ones with a smaller sublist and then join the results together aftwards, without me noticing that is what happened (until I'd do a trace on sql server).

Well you could always submit a patch :-)