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
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.