Sample Header Ad - 728x90

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: slow query 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