We got a table with 3 mio rows. when executing a nhibernate query its using sp_executesql which do no use any indexes to lookup the record.

Is there a way to bypass this for specific queries where the table sizes require e.g a stored proc in order to utilize indexing on the keys?

jaded[/b]

Your assumption about sp_executesql is incorrect. It uses indexes in the same way as, for example, query executed in the Query Analyzer. Perhaps you have to investigate your index more closely - maybe it isn't good enough for certain query, maybe statistics are out of date.

If the query will still be too slow, then you can write your own query which returns identifiers of objects, execute it manually and instantiate object with ISession.Get.

AFAIK, support for stored procedures is presented only in Hibernate 3.0.

From my experience, this is not true. In the past I've had some troubles where the same query executed via sp_executesql() resulted into a complete different execution plan. There was a 'WHERE field = @value OR field IS NULL' construction in the query that made the query optimizer go mad with sp_executesql().

Well, there are cases and there are cases. I would not stake on SQL Server to always behave like it's stated in the documentation. :) But, as i noticed ADO.NET internally use sp_executesql to execute queries, created with SqlConnection.CreateCommand. So it is at least expected to work well.

In case of NHibernate, there is no direct usage of sp_executesql in the code. It's ADO.NET's work. If you would write the same command and execute it manually (using ADO.NET) you most likely will get the same call with sp_executesql in it.