Query execution time using an ORMapper

Hello there!
I have a strange problem regarding the query execution time of a
parameterised query executed over sp_executesql. We use an ORMapper
(NHibernate) which generates the query, so it is not necessary to discuss the
use of sp_executesql. If I run the query in SQL Server Management Studio it
is much faster than executing it over the application. Looking at Profiler
the same query needs in MMS 6000 reads and 500 ms and over the application
nearly 200.000 reads and 30.000 ms.
I have no idea why this happens because the database doesnâ't change at all
and I profile on database level. So Iâ'm confused that it depends on the
execution source. The count of database sites in the data cache is constant
and the query plan cache is hit also in both executions.
Any ideas? Thanks
Rob"RobRoma" <RobRoma@.discussions.microsoft.com> wrote in message
news:EB7FF480-3C92-48AA-8867-FF2C30E55767@.microsoft.com...
> Hello there!
> I have a strange problem regarding the query execution time of a
> parameterised query executed over sp_executesql. We use an ORMapper
> (NHibernate) which generates the query, so it is not necessary to discuss
> the
> use of sp_executesql. If I run the query in SQL Server Management Studio
> it
> is much faster than executing it over the application. Looking at Profiler
> the same query needs in MMS 6000 reads and 500 ms and over the application
> nearly 200.000 reads and 30.000 ms.
> I have no idea why this happens because the database doesn't change at all
> and I profile on database level. So I'm confused that it depends on the
> execution source. The count of database sites in the data cache is
> constant
> and the query plan cache is hit also in both executions.
>
sp_executesql is fine, the issue is that a parameterized query is just
different than a non-parameterized one. You should expect a different plan
for expensive queries.
Test, analyze and optimize the query in Management Studio using
sp_executesql.
David