SQL Query Speed

I'm building a report that collates a huge amount of data, the data for the report has taken shape as a view which runs in about 2 to 9 seconds (which is acceptable). I also have a function that returns a set of ids which needs to filter the view:

select *
from vw_report
where employee_id in (select id from dbo.fnc_security(@personRanAsID))

The security function on its own runs in less than a second. However when I combine the two as I have above the query takes over 15 minutes.

Both the view and the security function do quite a lot of work so originally I thought it might be down to locking, I've tried no lock on the security function but it made no difference.

Any tips or tricks as to where I may be going wrong?

It may be worth noting that when I copy the result of the function into the in part of the statement:

select *
from vw_report
where employee_id in (123, 456, 789)

The speed increases back to 2 to 9 seconds.

Answers


Firstly, any extra background will help here... - Do you have the code for the view and the function? - Can you specify the schema and indexes used for the tables being referenced?

Without these, advise become difficult, but I'll have a stab...

1). You could change the IN clause to a Join. 2). You could specify WITH (NOEXPAND) on the view.

SELECT
  *
FROM
  vw_report WITH (NOEXPAND)
INNER JOIN
  (select id from dbo.fnc_security(@personRanAsID)) AS security
    ON security.id = vw_report.employee_id

Note: I'd try without NOEXPAND first.

The other option is that the combination of the indexes and the formulation of the view make it very hard for the optimiser to create a good execution plan. With the extra info I asked for above, this may be improvable.


Need Your Help

Handling WCF Rest Service exceptions only in one place

c# wcf rest

I'm developing an WCF Rest service that I'm going to host on an IIS.

Releasing CoreData objects with ARC

ios objective-c core-data automatic-ref-counting

Since I moved to ARC two months ago I discovered that I have problems with releasing objects.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.