Optimizing query with UDF and table vars and IN

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,
DaveDavid 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, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||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:
> 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 certai
n
> 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 lis
t
> 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 differe
nt
> 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 tab
le
> 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

Optimization Required for User Defined Function

Hi,

I've an UDF which inside has two query joined by union and it 's similar to this

select * from Table1 ... (several conditions)
union
select * from Table2 ... (several conditions) (this could takes long time to run)

Since i can't write dynamic sql into UDF , i can't avoid to insert Table2 into the query but to improve permormance I've seen how costant can help me.
For Example if I change my UDF in

select * from Table1 ... (several conditions)
union
select * from Table2 Where 1=2 AND (several conditions)

Optimazer is able to skip completely the second execution, so i need to transform 1=2 into a dynamic condition for example test a field table existence.
select * from Table2 Where Exist (select * from Table3 where Field1=1)

That is why i try to write a single UDF can adapt itself to several situations using second condition only where is necessary and not always.
The problem is the dynamic condition for simple could be, wasn't recognize as costant.

For Example
select top 1 * from MyTable where (select 1)=2
select top 1 * from MyTable where 1=2

If you see the execution plan of these 2 queries you could see that the first takes more than 80% of execution time and in the second less than 20%.
Moreover the second plan use a costant scan unlike the first doesn't it.

Do anyone know a way to tell to optimizer to use a simple condition as constant ? This improve drastically my UDF performance.... :( :(

Thanks.1) why is it essential to make it a function and not procedure or view.
2) how do u find

..you could see that the first takes more than 80% of execution time and in the second less than 20%...

if u r referring to execution-plan these % values relative to the batch and does not represent an absolute value. and practically both are taking 0 sec in my machine.
3) if u use "select" in a "where" it is evaluated once for each row of the outer query hence is inefficient.

Optimiser and UDF's

I came across something today that hopefully if my explanation is
wrong someone could correct me.
I was trying to speed up a stored proc that took an integer as a
parameter and then ran around a dozen SQL statements to populate a
table variable.
1st query was :-
select <some columns> from A where A.id = @.id
The others were along those lines but joined to several lookup tables
and other combinations. Basically though it was hitting table A with
the same search conditions each time (id = @.id).
Thinking this was a waste I wrote a UDF that took the id as a
parameter and returned a table with just the relevant records from
table A in it. The SQL in the stored proc was then changed as follows
:-
select <some columns> from myUDF(@.id)
The new stored proc now runs twice as fast, uses half the cpu time and
a third of the disk io.
Would I be right in saying that SQL2k has realised that the UDF had
been called a dozen times with the same parameters, ran it once and
cached the results?
Its the only reason I can see for such a big performance improvement.
cheers.No, UDF results are not cached in SQL 2k, but that might be a nice feature
for Yukon... Sybase, for instance, does cache udf results...If your efforts
lowered the number of joins that were being done, I suspect the improvement
lies there.
"Mike Watson" <mike@.prog99.com> wrote in message
news:q1h7lvoluabdh7ulaigatf1ndn05gaofo8@.4ax.com...
> I came across something today that hopefully if my explanation is
> wrong someone could correct me.
> I was trying to speed up a stored proc that took an integer as a
> parameter and then ran around a dozen SQL statements to populate a
> table variable.
> 1st query was :-
> select <some columns> from A where A.id = @.id
> The others were along those lines but joined to several lookup tables
> and other combinations. Basically though it was hitting table A with
> the same search conditions each time (id = @.id).
> Thinking this was a waste I wrote a UDF that took the id as a
> parameter and returned a table with just the relevant records from
> table A in it. The SQL in the stored proc was then changed as follows
> :-
> select <some columns> from myUDF(@.id)
> The new stored proc now runs twice as fast, uses half the cpu time and
> a third of the disk io.
> Would I be right in saying that SQL2k has realised that the UDF had
> been called a dozen times with the same parameters, ran it once and
> cached the results?
> Its the only reason I can see for such a big performance improvement.
> cheers.
>|||On Tue, 2 Sep 2003 07:45:12 -0400, "Wayne Snyder"
<wsnyder@.computeredservices.com> wrote:
>No, UDF results are not cached in SQL 2k, but that might be a nice feature
>for Yukon... Sybase, for instance, does cache udf results...If your efforts
>lowered the number of joins that were being done, I suspect the improvement
>lies there.
>
Thanks for the reply, I'm surprised that the overhead of calling the
UDF around a dozen times is so much cheaper than doing a striaght join
on the table.