In c# we are building a query for NHibernate containing an "in statement". The number of expressions are crossing over 5000. If I execute the query I get an error.
I need to find a good way to break the large string builder and store them in an array of string builder and execute multiple queries if needed to get the desired output. We have only one account that has over 5000 records and the rest are all below 100. Can someone suggest a way to resolve this?
The solution I have used is to split the IN with OR.
where A in (a,b,c,d,e,f)
becomes
where (A in (a,b,c) OR a in (d,e,f)) ...
This is straightforward and imposes no special requirements to the format of the query.
Seems to me that this is easier to implement in your stringbuilder (SQLQuerybuilder or whatever it's called in your case) than some other suggested solutions.
You can convert IN to a JOIN using this trick
SELECT * FROM maintable
JOIN (
SELECT v1 a FROM DUAL UNION ALL
SELECT v2 a FROM DUAL UNION ALL
SELECT v3 a FROM DUAL UNION ALL
...
SELECT v2000 a FROM DUAL) tmp
on tmp.a = maintable.id
This query is identical to
SELECT * FROM maintable
WHERE id IN (v1, v2, v3, ...., v2000)
I know it increases the 'amount of text' in query but there isnt a performace impact as anyways both use temporary resultset without the hassle of creating global temp table.
The original post question is getting old, but I have met this problem several times lately and have found a solution that might be of help to others. And, as none of the answers above has given an answer with NHibernate code, I also think this is relevant to post.
In our software product, the users make a selection in the user interface. The front end then passes a list of the corresponding ids to the back end for data querying and manipulation.
First, I needed a function to split up the list of elements into paritions of 1000 elements.
Note, this is VB.NET, but the function itself was found elsewhere on StackOverflow in C#:
Public Shared Iterator Function Partition(Of T)(source As IList(Of T), Optional size As Int32 = 1000) As IEnumerable(Of List(Of T))
For i As Integer = 0 To CInt(Math.Ceiling(source.Count / CDbl(size)))
Yield New List(Of T)(source.Skip(size * i).Take(size))
Next
End Function
This function I have used in several ways. One way is a loop over the list partitions to modify a QueryOver to make a Union of all the part results, like this:
Dim allPartitions As IEnumerable(Of List(Of Integer)) = Partition(idList, SplitSize)
Dim foundObjects As IEnumerable(Of MyEntity) = New List(Of MyEntity)
For Each part As List(Of Integer) In allPartitions
foundObjects = foundObjects.Union(
_session.QueryOver(Of MyEntity) _
WhereRestrictionOn(Function(x) x.ID).IsIn(part).Future())
Next
Another way I have used this is to create an Restriction, that can be applied in QueryOvers. The following function creates such a restriction (ICriterion):
Public Shared Function GetRestrictionOnIds(ids As List(Of Integer), propertyName As String) As ICriterion
Dim allParts As IEnumerable(Of List(Of Integer)) = Partition(ids, SplitSize)
Dim restriction As Disjunction = Restrictions.Disjunction()
For Each part As List(Of Integer) In allParts
restriction.Add(Restrictions.In(propertyName, part))
Next
Return Restrictions.Conjunction().Add(restriction)
End Function
I call that function like this:
Dim wellIdRestriction As ICriterion = GetRestrictionOnIds(wellIdsList, "WellId")
Dim bleedOffs As IList(Of BleedOff) = _session.QueryOver(Of BleedOff)() _
.Where(wellIdRestriction) _
.Where(..... more restrictions...) _
.And(...yet more restrictions....) _
.List().GroupBy(...some function...) _
.ToDictionary(.. key-function, value-function...)
You could create a temp table, populate it with the items in the IN-list, and join against it.
You can union together the selects with 1000 of the IN in each select. Not as performant as creating a temp table, but for ad-hoc queries it works.
SELECT A, B, C from BLAH WHERE A IS IN (
0 - 999
)
UNION
SELECT A, B, C from BLAH WHERE A IS IN (
1000 - 1999
)
when you have that many items in a where clause, it might be time to consider refactoring.
you could create a stored proc that takes in a comma separated value string. the stored proc could then generate a table from that csv and you could do a inner join with your other table to produce the records.
take a look at this link to learn how to create a function that takes in a csv and returns a table so that you can query against it.
how to convert csv to table in oracle or How to best split csv strings in oracle 9i
