Hi there - I am using NHibernate 1.2.1 from a web application against SQL Server 2005.

When using a profiler I can see the SQL being sent to the SQL Server.

I can see that NHibernate is using sp_executesql with some very long sql strings.

Does anyone have any knowledge about the performance of this compared to the same request being executed with a dedicated stored procedure.

I seem to recall something about that the first time a new SQL request arrives, SQL server will compile the request. The next time the same request arrives the compiled SQL will be reused. Can anyone confirm this?

Cheers,
Thomas

sp_executesql is a dynamic way to build and execute queries, since queries can change I doubt that SQLServer can make many optimizations..

Hi Becio - thanks for the reply.

I have been looking at the SQL sent to sp_executesql, and I can see that the SQL is using named parameters. I would expect NHibernate to generate the identical SQL when performing identical request, thus it should be possible for the database to cache the execution plan since only the values of the parameters change.

When reading the evaluation FAQ for the java version of hibernate I find this Q and A:

Q: Aren't stored procedures always faster than dynamic SQL?

A: No. Hibernate always executes SQL statements using a JDBC PreparedStatement, which allows the database to cache the query plan. There is no reason to avoid the use of generated SQL in modern applications.


Does anyone know if something similar is possible happening for NHibernate?

Cheers,
Thomas

sp_executesql generates execution plans, they are likely to be reused when the query does not change.

This is true, however if you use parameterized queries you can change the values in your where clause without a new query plan being generated.

NHibernate performs very well with parameterized queries in my experience as long as you have good indexes.

Cheers,

Symon.

he syntax and converts the query into relational algebric expressions. Then the query optmizer constructs the execution plan based on several rules and cost of executing the query. Once the execution plan is generated, action switches to the storage engine where query is actually executed, according to the plan.

There are two distinct types of execution plan. The plan that outputs from optimizer is known as estimated execution plan and the other plan represents the output from actual query execution.It is expensive for the Server to generate execution plans so SQL Server will keep and reuse plans wherever possible .As they are created, plans are stored in a section of memory called the plan cache.

The optimizer compares this estimated plan to actual execution plans that already exists in the plan cache . If an actual plan is found that matches the estimated one, then the optimizer will reuse the existing plan, since it's already been used before by the query engine. This reuse avoids the overhead of creating actual execution plans for large and complex queries or even simple plans for small queries.


http://www.mindfiresolutions.com/How-to-reuse-query-execution-plan-116.php