Using static list of values in a IN clause fluent nhibernate

Question

I have to write a select query with around 10,000 id's in a in clause. Query is very slow as i have to write 10 in clause with 1000 ids in each. One way i found on internet is that before firing a select, add these 10000 ids in a table and do a join on it and roll back the insert. Does any one have tried this approach as how it will work with transaction in nhibernate. Different users using this feature should not see others data ( it may get handled b read comitted isolation level).

Answer 1

Writing the values to a table - potentially a Global Temporary Table (GTT) is a much better approach than the big "in-list".