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]
SELECT     Id, Name, Deleted, mon.SlowFunction(Id) AS Groups,
FROM         mon.Devices

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

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.

  • What is SlowFunction()? Do you really need to run SlowFunction for every single row? – Mark Sinkinson Aug 12 '15 at 12:59
  • No, thats the problem - I only need to know SlowFunction value for selected rows (in my use case, ony top 10 rows). But SQL server insists on evaluating it before TOP and filtering. – MSM Aug 12 '15 at 13:01
  • 2
    So stop using the view and apply SlowFunction() on the outer part of your query. – Aaron Bertrand Aug 12 '15 at 13:25
  • 1
    Check articles about plan guides, for instance msdn.microsoft.com/en-us/library/ms190417(v=sql.100).aspx . However, it appears to me that you're trying to solve ORM problems on SQLServer side. It's not gonna work in a long term even if you find a hack for this very specific issue. – a1ex07 Aug 12 '15 at 15:05
  • @MSM what version of SQL server are you running? – Aaron Aug 13 '15 at 2:42

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:

  1. Write your stored procedure to select the data in the most efficient manner.
  2. Write a class that represents the data you're selecting (a DTO would be perfect).
  3. Write a NHibernate mapping that tells it how to associate the fields
  4. 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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.