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