CODEWITHSUNDEEP
sqlperformancereporting
Jack Mills
Jack Mills

Reputation: 6132

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.

Upvotes: 0

Views: 105

Answers (3)

Jack Mills
Jack Mills

Reputation: 6132

I ended up dumping the result from the security function into a temporary table and using the temporary table in my main query. Proved to be the fastest method.

e.g.:

create table #tempTable (id bigint)

select id 
into #tempTable
from dbo.fnc_security(@personRanAsID)

select *
from vw_report
where id in (select id from #tempTable)

Upvotes: 0

MatBailie
MatBailie

Reputation: 86735

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.

Upvotes: 2

ravnur
ravnur

Reputation: 2852

It takes so much time because sub-select query executing for each row from vw_report while the second query doesn't. You should use something like:

select *
from vw_report r, (select id from dbo.fnc_security(@personRanAsID)) v
where r.employee_id = v.id

Upvotes: 0

Related Questions