Performance problems with TOP and user defined functions in views 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 &gt; 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.
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.