Sql-server – Performance problems with TOP and user defined functions in views

functionsnhibernateperformancequery-performancesql serverview

I have problem with performance of queries on simple query on view.

To keep things simple, assume my view is defined as follows:

CREATE VIEW [mon].[ViewDevicesWithGroups]
AS
SELECT     Id, Name, Deleted, mon.SlowFunction(Id) AS Groups,
FROM         mon.Devices

And query I'm trying to optimize looks like this:

SELECT TOP 10 
Id218_0_, Groups, Name218_0_, DeletedD6_218_0_ FROM (
    SELECT this_.Id as Id218_0_, this_.Name as Name218_0_, this_.Deleted as Deleted218_0_, Groups, ROW_NUMBER() OVER(ORDER BY this_.Name) as __hibernate_sort_row
    FROM mon.ViewDevicesWithGroups this_
    WHERE (this_.Deleted = 1 and not (1=0))) AS query
WHERE query.__hibernate_sort_row > 100000
ORDER BY query.__hibernate_sort_row

My problem is, SQL server keeps executing this query in REALLY wrong order – SlowFunction is really slow, and should be delayed as long as possible:

As you can see, this query was generated by ORM (NHibernate in this case), and I can't change it directly (otherwise this problem would be trivial to solve by just moving function call to outermost select).

Can performance of my query be improved without changes in application code (maybe I kind of hint on view/function)? Because that query is abstracted deeply inside DAL in my application, and changing it would require A LOT of work.

Best Answer

Why don't you call a stored procedure via NHibernate instead of asking it to query the view? That would allow you to write your own query and tune as needed....

I don't know NHibernate very well but this blog post looks like a reasonable resource. In case that link dies here are the basic steps:

Write your stored procedure to select the data in the most efficient manner.
Write a class that represents the data you're selecting (a DTO would be perfect).
Write a NHibernate mapping that tells it how to associate the fields
Specify the name of the stored procedure to use and any parameters when you query the database via NHibernate.

It might be a good idea to check out this article on StackOverflow related to tuning performance when using NHibernate.

Related Solutions

Sql-server – Window functions cause awful execution plan when called from a view with external parametrized ‘where’ clause

This appears to be a long standing issue that keeps resurfacing in one form or another and is still present in SQL Server 2012.

Some posts discussing it are

All current versions of SQL Server up to and including 2012 are not able to push the filter on a partitioning group past the sequence project for a parameterised predicate except if option(recompile) is used (if 2008+).

An alternative to the recompile hint would be to rewrite the query to use a parameterised inline TVF as suggested by @a1ex07)

SQL Server Linked Server – Query Performance Tips

Your problem begins and ends with statistics and estimates. I have reproduced your situation on my servers and found some interesting hints, and a workaround solution.

First things first, let's take a look at your execution plan:
When a view is used we can see that a filter is applied after the Remote Query is executed, while without the view there was no filter applied at all. The truth is that the filter was applied inside the Remote Query, at the remote server, before retrieving the data over the network.
Well, obviously applying the filter at the remote server and thus retrieving less data is a better option, and obviously that only happens when not using a view.

So... what is so intersting...?

Surprisingly, when I changed the filter from cognome = 'test' to cognome = N'test' (unicode representation of the string) the view used the same execution plan as the first query did.
I guess the reason is that somehow when using the view SQL Server estimated that there will be a small number of rows returning from the (remote) query, and that a local filtering will be cheaper, but when SQL Server had to implicit convert NVARCHAR to VARCHAR, statistics could no longer be used and the decision to filter locally was not taken.
I have looked for the statistics locally, but the view had no statistics, so my guess is that the view uses the remote statistics in a way that ad-hoc query does not, and than takes the wrong decision.

OK, so what solves the problem?

I stated earlier that there is a workaround (at least until someone comes up with a better solution), and no, I don't mean using unicode for your strings.
I wanted to give an answer first, I still have to find why, but when using an Inline Function SQL Server behaves exactly the same as with the query (without view), so replacing the view with the function will give the same result, in a simple query, and with good peformance (at least in my environment).

My code suggestion for you is:

CREATE FUNCTION fn_anagrafiche2()
RETURNS table
AS
RETURN 
(
    SELECT * 
    FROM dolph2.agendasdn.dbo.vistaanagraficagrp
    UNION
    SELECT * 
    FROM dolph2.acampanet.dbo.vistaanagraficagrp
    UNION
    SELECT * 
    FROM municipio2.dbnet.dbo.vistaanagraficagrp
)
GO

The query will then be:

SELECT * 
FROM fn_anagrafiche2()
WHERE cognome = 'prova'

This works on my servers, but of course test it first.
Note: I do not recommend using SELECT * at all, as it is prone to future errors, I simply used it because it was in your question and there was no need for me to change that when I can add this remark instead :)

Best Answer

Related Solutions

Sql-server – Window functions cause awful execution plan when called from a view with external parametrized ‘where’ clause

SQL Server Linked Server – Query Performance Tips

Related Question