Optimizing query with UDF and table vars and IN

9 Feb 2006 9:23 PM

David D Webb

Hi, I am trying to optimize this scenario.

I have a query that is returning a list of services. Each service is done
by an employee and for a client. Employees have rights to see only certain
sevices. They can see any service that is done by an employee they have
rights to OR done for a client they have rights to.

CREATE TABLE [Service] (
service_id Int IDENTITY(1,1) NOT NULL,
emp_id Int,
client_id Int)

I have 2 UDFs that return a list of emp_id's they have rights to and a list
of client_id's they have rights to respectively.

CREATE FUNCTION dbo.f_list_emps (@my_emp_id Int)
RETURNS @EmpList TABLE (emp_id int not null unique)
AS
BEGIN
// Fill @EmpList here with a bunch of queries
END

CREATE FUNCTION dbo.f_list_clients (@my_emp_id Int)
RETURNS @ClientList TABLE (client_id int not null unique)
AS
BEGIN
// Fill @ClientList here with a bunch of queries
END

The actual query is built dynamically because it can have about 15 different
parameters passed to it, but a simplified version would look like:

SELECT * FROM Service
WHERE emp_id IN
       (SELECT emp_id FROM dbo.f_list_emps(@my_emp_id))
    OR client_id IN
       (SELECT client_id FROM dbo.f_list_clients(@my_emp_id))

I'm looking for a way to optimize this a bit better. I can't join the table
vars directly because of the 'OR', and I don't want to do a UNION of 2
queries each with a separate join because of all the other parameters
involved in the query.

Thanks for any advice,
Dave

10 Feb 2006 11:23 PM

Erland Sommarskog

David D Webb (spivey@nospam.post.com) writes:

> The actual query is built dynamically because it can have about 15
> different parameters passed to it, but a simplified version would look
> like: >
> SELECT * FROM Service
> WHERE emp_id IN
>        (SELECT emp_id FROM dbo.f_list_emps(@my_emp_id))
>     OR client_id IN
>        (SELECT client_id FROM dbo.f_list_clients(@my_emp_id))
>
> I'm looking for a way to optimize this a bit better. I can't join the
> table vars directly because of the 'OR', and I don't want to do a UNION
> of 2 queries each with a separate join because of all the other
> parameters involved in the query.

It's of course impossible to suggest optimizations when I don't see
the tables, and do not the full query.

What I would consider is to insert the data from the table functions
into temp tables. Temp tables have statistics, and since you are running
a dynamic query anyway, you could just as well make use of that statistics.
If you use the UDFs in the query, SQL Server will make some standard
assumptions about what they return.

I would also consider running a UNION of two queries. If you are building
the query dynamically, it should not be much of an issue to repeat the
queries. But you should benchmark whether UNION actually gives an
improvement.

--
Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

11 Feb 2006 12:17 PM

Gert-Jan Strik

A simplified query probably won't do.

But here a tip that might be useful: drop the UDF's. If you can rewrite
them as views, then the optimizer can properly optimize the query. When
using UDF's in this fashion you are bound to run into performance
problems as the resultset grows.

Gert-Jan

David D Webb wrote:
Show quoteHide quote

>
> Hi, I am trying to optimize this scenario.
>
> I have a query that is returning a list of services. Each service is done
> by an employee and for a client. Employees have rights to see only certain
> sevices. They can see any service that is done by an employee they have
> rights to OR done for a client they have rights to.
>
> CREATE TABLE [Service] (
>   service_id Int IDENTITY(1,1) NOT NULL,
>   emp_id Int,
>   client_id Int)
>
> I have 2 UDFs that return a list of emp_id's they have rights to and a list
> of client_id's they have rights to respectively.
>
> CREATE FUNCTION dbo.f_list_emps (@my_emp_id Int)
>   RETURNS @EmpList TABLE (emp_id int not null unique)
> AS
> BEGIN
>   // Fill @EmpList here with a bunch of queries
> END
>
> CREATE FUNCTION dbo.f_list_clients (@my_emp_id Int)
>   RETURNS @ClientList TABLE (client_id int not null unique)
> AS
> BEGIN
>   // Fill @ClientList here with a bunch of queries
> END
>
> The actual query is built dynamically because it can have about 15 different
> parameters passed to it, but a simplified version would look like:
>
> SELECT * FROM Service
> WHERE emp_id IN
>        (SELECT emp_id FROM dbo.f_list_emps(@my_emp_id))
>     OR client_id IN
>        (SELECT client_id FROM dbo.f_list_clients(@my_emp_id))
>
> I'm looking for a way to optimize this a bit better. I can't join the table
> vars directly because of the 'OR', and I don't want to do a UNION of 2
> queries each with a separate join because of all the other parameters
> involved in the query.
>
> Thanks for any advice,
> Dave