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

OpenXML not returning desired results

Hi all,

I have a SQL job where I do the following -

I check for new rows in my Table "DumpResults", every now and then and get the new rows to be inserted into table "CleanTable". I use OPENXML() to get the new data to be inserted but for some reason I don't get the right data through OPENXML() -

DECLARE @.intDocINT
DECLARE @.xmlDocVARCHAR(8000)
IF(SELECTCOUNT(*)FROM DumpResults WHERE DumpResults.C1NOT IN (SELECT CleanTable.C1FROM CleanTable)) > 0
BEGIN
SET @.xmlDoc = (SELECT *FROM DumpResultsWHERE DumpResults.C1NOT IN (SELECT CleanTable.C1FROM CleanTable)FOR XML RAW)
SET @.xmlDoc ='<TABLE>' + @.xmlDoc +'</TABLE>'PRINT @.xmlDoc

EXECsp_xml_preparedocument @.intDocOUTPUT, @.xmlDoc
--INSERT INTOCleanTable(C1, C2, C3, C4, C5)SELECTC1, C2, C3, C4, C5, C6FROM OPENXML(@.intDoc,'/row',1)
WITH (C1 INT,C2CHAR(3),C3CHAR(3) ,C4FLOAT,C5INT)

EXECsp_xml_removedocument @.intDocENDELSE

Output that I get is -

<TABLE><row C1="1" C2="AAA" C3="BBB" C4="1.000000000000000e+000" C5="2"/></TABLE
(0 row(s) affected)

SO "PRINT @.xmlDoc" is returning back the xml data (new results) it collected from the "DumpResults" table which isn't there in "CleanTable" but the "Select... FROM OPENXML(...)" doesn't return any result. why so? If anyone knows please reply

If anyone has any better method to do it, inputs are welcome.

Thanks

The issue is with your xpath.

Try it like this FROM OPENXML(@.intDoc, '/Table/row', 1)

Everything else looks to be right. Your XML has attributes and your setting your OPENXML to be with attribute centric. Just your XPATH is telling it to look at the root level for the row element, but its not at the root element.

|||

Hey guess what I had tried '/Table/row' earlier too.. But didn't work. Now once you said I looked at my query, and the place where I am appending -

SET @.xmlDoc = '<Table>' + @.xmlDoc + '</Table>'

I changed the case of "Table" and tried.. and it worked.. didn't know it was case sensitive..

Thanks

|||

Thats great,

Yes XML is very case sensitive :P