Hi all I would like to know if anyone knows how to solve problems related to "Parameter sniffing" using MSSQL 2005, NHibernate (version 2.0.0.4000), and ICriteria.
I have a quite complex search routine constructed using ICriteria. this search routine works fine as long as I don't put more than approx. 100.000 rows in it. However the table needs to hold several million records, and a request now easily takes 60 sec in order to return data. Using Sql Profiler I copy the querystring generated by NHibernate. running this query manually still takes up the same amount of time. by manually adding the "option( optimize for( @p0 = 0 ,...))" statement to the end of the query, the server now responds within a second. Does anybody know if it is possible to set this option within a NHibernate ICriteria? the only solution I have been able to come up with is to write the sql my self (including paging routines) and use an ISQLQuery to get my results. I think that this solution really sucks, as the whole idea of using an ORM is to let the ORM handle the sql... I really can't find any information of this matter anywhere. Help is very much appreciated. kind regards Michael --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "nhusers" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [email protected] For more options, visit this group at http://groups.google.com/group/nhusers?hl=en -~----------~----~----~----~------~----~------~--~---
