On stored procedure exec I pass only the input parameters... Field5 is
simply a selected field. The inner query is exactly the same.
In any case, to be more precise, you can also write ...
<sql-query name="sp_Test1">
<return-scalar column="Field5" type="Int32" />
exec MyStoredProcedure :Param1, :Param2, :Param3, :Param4
</sql-query>
<sql-query name="sp_Test2">
<return-scalar column="Field5" type="Int32" />
<![CDATA[
SELECT Field5 FROM MyTable WHERE Field1 = :Param1 AND Field2 = :Param2 AND
Field3 = :Param3 AND Field4 = :Param4 ORDER BY Field5
]]>
</sql-query>
but the result is always the same...1sec for "sp_Test1" e 20sec for
"sp_Test2"
On Wednesday, February 12, 2014 8:40:06 AM UTC+1, Ramon Smits wrote:
>
> 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]<javascript:>
> > 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] <javascript:>.
>> To post to this group, send email to [email protected]<javascript:>
>> .
>> 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.
