Why Is Parameterized SQL Generated by Nhibernate Just as Fast as a Stored Procedure

Why is parameterized SQL generated by NHibernate just as fast as a stored procedure?

I would start by reading this article:

http://decipherinfosys.wordpress.com/2007/03/27/using-stored-procedures-vs-dynamic-sql-generated-by-orm/

Here is a speed test between the two:

http://www.blackwasp.co.uk/SpeedTestSqlSproc.aspx

Query executed from Nhibernate is slow, but from ADO.NET is fast

I had the ADO.NET and NHibernate using different query-plans, and I was sufering the effects of parameter sniffing on the NH version. Why? Because I had previously made a query with a small date interval, and the stored query-plan was optimized for it.

Afterwards, when querying with a large date interval, the stored plan was used and it took ages to get a result.

I confirmed that this was in fact the problem because a simple:

DBCC FREEPROCCACHE -- clears the query-plan cache

made my query fast again.

I found 2 ways to solve this:

• Injecting an "option(recompile)" to the query, using a NH Interceptor
• Adding a dummy predicate to my NH Linq expression, like: query = query.Where(true) when the expected result-set was small (date interval wise). This way two different query plans would be created, one for large-sets of data and one for small-sets.

I tried both options, and both worked, but opted for the second approach. It's a little bit hacky but works really well I my case, because the data is uniformly distributed date-wise.

While calling SQL Server stored procedure from NHibernate can I pass list or custom data type as a parameter

In SQL Server, stored procedures can have parameters of type table that can be used to mimic the Oracle Associative Array feature. In your situation, you'd be sending a "table" with a single row and multiple columns. There is a good example for NHibernate here in the accepted answer.

Why this parameterized SQL takes forever when the same hardcoded one executes in no time

As already said by Sean Lange in his comment this behavior is very likely to be caused by parameter sniffing.

In my experience, it has always been solved by fixing the indexes. (Do not add indexes too quickly, having too many indexes may causes other performance issues. Like bad index choices by the query optimizer, leading to temp db spills by example.)

Parameter sniffing does not occur only on stored procedure. By examples, it occurs on sql queries executed through sp_executesql or EXEC(). It may even occurs with auto-parameterized scalar values founded in queries.

Parameter sniffing is an optimization fall-back used by SQL Server in case of missing indexes. It shapes a query plans generated for a first query with its specific parameters values, which then get cached in query plan cache. All subsequent call to the same query with different parameters values, with similar connection properties, will then use that query plan, whatever the parameters values are.

If the values of the first query call was corresponding to a corner case yielding a high filtering condition from one table, but others calls values do not cause the same high filtering, the cached query plan causes them to badly perform.

SSMS has rarely the same connection options than your application, causing it to not reuse the cached query plan used by the application. Another query plan gets generated, adapted to the query parameters values you are testing if you are lacking indexes. So SSMS appears to perform better... But no, it does just use a query plan tailored for the specific parameters values you are testing.

A more detailed, precise and adequate explanation can be read in Slow in the Application, Fast in SSMS? Understanding Performance Mysteries blog post.

Do not be deterred by its raw aspect, this blog is a great resource in my opinion. Do not either be fooled by the How SQL Server Compiles a Stored Procedure heading, he writes in the second sentence following it:

If your application does not use stored procedures, but submits SQL statements directly, most of what I say this chapter is still applicable.

This blog post will also give you guidance on how to resolve such issues.

Native Stored Proc v/s Hibernate

I think the approach you outlined is great.

That is exactly what I would do if I were in your position. (I'm on Hibernate backed by MySql also, and have considered doing this if needed for performance reasons.)

Stored procedures or OR mappers?

I like ORM's because you don't have to reinvent the wheel. That being said, it completely depends on your application needs, development style and that of the team.

This question has already been covered Why is parameterized SQL generated by NHibernate just as fast as a stored procedure?

Stored Procedure don't work in nHibernate

        IList<Produkt> sss = _session
                    .GetNamedQuery("sp_retrieveAllProductCategory")
                    .SetResultTransformer(
                            Transformers.AliasToBean(typeof(Produkt))).List<Produkt>();

https://www.simple-talk.com/blogs/2013/09/27/nhibernate-and-stored-procedures-in-c/

Stored Procedures and ORM's

Stored Procedures are often written in a dialect of SQL (T-SQL for SQL Server, PL-SQL Oracle, and so on). That's because they add extra capabilities to SQL to make it more powerful.
On the other hand, you have a ORM, let say NH that generates SQL.

the SQL statements generated by the ORM doesn't have the same speed or power of writing T-SQL Stored Procedures.
Here is where the dilemma enters: Do I need super fast application tied to a SQL Database vendor, hard to maintain or Do I need to be flexible because I need to target to multiple databases and I prefer cutting development time by writing HQL queries than SQL ones?

Stored Procedure are faster than SQL statements because they are pre-compiled in the Database Engine, with execution plans cached. You can't do that in NH, but you have other alternatives, like using Cache Level 1 or 2.

Also, try to do bulk operations with NH. Stored Procedures works very well in those cases. You need to consider that SP talks to the database in a deeper level.

The choice may not be that obvious because all depends of the scenario you are working on.

---

Related Topics