Performance problems with TOP and user defined functions in views
4
votes
1
answer
360
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 > 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.

Asked by MSM
(113 rep)
Aug 12, 2015, 12:56 PM
Last activity: Aug 12, 2015, 06:24 PM
Last activity: Aug 12, 2015, 06:24 PM