Its not the same query. I see that in the 2nd query you do an order by on field 5. Field 5 is missing in the first query.
If you run these queries on the database is the performance ok then? You can use the sql profiler to see the actual query that is executed or enable sql logging in nhibernate. -- Ramon On Tue, Feb 11, 2014 at 2:49 PM, Luca Petrini <[email protected]>wrote: > I have a performance problem with the execution of a SQL query on > NHibernate. > > I tried to import the SQL code of a stored procedure in a SQL query, > finding a large performance deterioration. > > Here's an example of testing... > > In my mapping file I have these two definitions: > > <sql-query name="sp_Test1"> > exec MyStoredProcedure :Param1, :Param2, :Param3, :Param4 > </sql-query> > > <sql-query name="sp_Test2"> > <![CDATA[ > SELECT Field5 FROM MyTable WHERE Field1 = :Param1 AND Field2 = > :Param2 AND Field3 = :Param3 AND Field4 = :Param4 ORDER BY Field5 > ]]> > </sql-query> > > > The native code of sp_Test2 is exactly the same SQL code in the stored > procedure. > > Now if I run a test like this: > > int count = 2500; > System.Diagnostics.Stopwatch sw = new System.Diagnostics.Stopwatch(); > sw.Start(); > for (int i=0; i<=count; i++) { > //Execute query "sp_Test1" > } > sw.Stop(); > //Print sw.ElapsedMilliseconds > > sw.Reset(); > sw.Start(); > for (int i=0; i<=count; i++) { > //Execute query "sp_Test2" > } > sw.Stop(); > //Print sw.ElapsedMilliseconds > > I get to the execution of sp_Test1 a time of about 1 second, while for > the execution of sp_Test2 a time of about 20 seconds! > > Can anyone tell me what is causing this degradation of performance? And > how can I improve the performance of sp_Test2? > > Note: I use NHibernate 2.1.2 for this test. > > Thank you in advance for any useful information about. > > -- > You received this message because you are subscribed to the Google Groups > "nhusers" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected]. > To post to this group, send email to [email protected]. > Visit this group at http://groups.google.com/group/nhusers. > For more options, visit https://groups.google.com/groups/opt_out. > -- You received this message because you are subscribed to the Google Groups "nhusers" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/nhusers. For more options, visit https://groups.google.com/groups/opt_out.
