hello

been using NHibernate for a year and some change. it's great but has some quirks. in my quest to simplify coding i made an engine that is capable of building complex Criteria starting from one object root (aliases generation, including filtering, ordering and projections). managed to do that and it works correctly and made easier.

i have a problem though. i use sql 2005 and dialect sql2005. when Criteria launches the query against the DB it uses sp_executesql. i read about this behaviour and it's supposed to help as it treats the query as a stored procedure. very nice! ... i said. but i received a complaint saying that the old plain sql was working faster. i started profiler and got the queries (the ol one and the new one). the queries were ok and returning the same thing (the plain sql query and the query generated by Criteria). tested them.

the sp_execute sql query used almost 3 times the CPU and a bit less IOs compared to the sp_executesql version. then i copied the sq_executesql query and turned it in a plain query (without params). bingo! it worked as fast as the original query.

if i lost you then i'm sorry and i'll make it clearer. Criteria launches sp_executesql queries. if i turn it in a plain sql query then the performace is very different. does anybody have any suggestion to why this is happening? can i configure NHibernate to use plain sql queries instead of stored procedures (exec sp_executesql)?

Try adding the schema prefix to the configuration, for example:



Adding the schema prefix can have a positive performance impact on sp_executesql queries.

thanks for the response, but it made no difference.