Filter Criteria - Temp Table Join or Where clause?

[Chris Geihsler]

I have a set of udf's dealing that return a one column table of values
parsed from a comma delimeted string.

For example:

CREATE FUNCTION [dbo].[udf_filter_pati ent]
(
@patient_list varchar(2000)
)
RETURNS @patient TABLE
(
patient_id int
)
AS
BEGIN
insert into @patient
select patient_id from patient
-- parse @patient_list stuff excluded
RETURN
END

I have come up with the following two schemes to use these udfs. These
examples are obviously simplified, and I have a handful of stored procedures
that will use between 10 or more of these filters. If the two are
equivalent, I prefer Method 2 because it makes for much neater SQL when
using many filter criteria.

So my question is, will one noticebly outperform the other? Or is there a
better way in which to filter on a list of criteria?

Method 1 :

CREATE PROC sp__filter_open _bills
@patient_list varchar(2000)
AS

CREATE TABLE #patient
(
patient_id int
)

INSERT INTO #patient
SELECT
patient_id
FROM
dbo.udf_filter_ patient( @patient_list )

SELECT
*
FROM
open_bills
INNER JOIN #patient on #patient.patien t_id = open_bills.pati ent_id

GO
Method 2 :

CREATE PROC sp__filter_open _bills
@patient_list varchar(2000)
AS

SELECT
*
FROM
open_bills
WHERE
open_bills.pati ent_id IN ( SELECT patient_id FROM
dbo.udf_filter_ patient( @patient_list ) )

GO

Thanks for the help!

Chris G

[Hugo Kornelis]

On Thu, 20 May 2004 03:15:07 GMT, Chris Geihsler wrote:

I have a set of udf's dealing that return a one column table of values
parsed from a comma delimeted string.

For example:

CREATE FUNCTION [dbo].[udf_filter_pati ent]
(
@patient_list varchar(2000)
)
RETURNS @patient TABLE
(
patient_id int
)
AS
BEGIN
insert into @patient
select patient_id from patient
-- parse @patient_list stuff excluded
RETURN
END

I have come up with the following two schemes to use these udfs. These
examples are obviously simplified, and I have a handful of stored procedures
that will use between 10 or more of these filters. If the two are
equivalent, I prefer Method 2 because it makes for much neater SQL when
using many filter criteria.

So my question is, will one noticebly outperform the other? Or is there a
better way in which to filter on a list of criteria?

Method 1 :

CREATE PROC sp__filter_open _bills
@patient_list varchar(2000)
AS

CREATE TABLE #patient
(
patient_id int
)

INSERT INTO #patient
SELECT
patient_id
FROM
dbo.udf_filter_ patient( @patient_list )

SELECT
*
FROM
open_bills
INNER JOIN #patient on #patient.patien t_id = open_bills.pati ent_id

GO
Method 2 :

CREATE PROC sp__filter_open _bills
@patient_list varchar(2000)
AS

SELECT
*
FROM
open_bills
WHERE
open_bills.pati ent_id IN ( SELECT patient_id FROM
dbo.udf_filter _patient( @patient_list ) )

GO

Thanks for the help!

Chris G

Hi Chris,

The only way to KNOW which performs better is testing it.

That being said, if you were taking bets I'd know where my money would be.
The second option repeatedly calls the UDF. Evenry time the patient_list
has to be parsed (using some logic you excluded from your post, but
probably complex). The first option executes the UDF only once, stores the
results in a temp table and uses that. You could even define an index on
the temp table and try if that speeds things up even more.

But as I said - test it and you'll know for sure.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[Erland Sommarskog]

[posted and mailed, please reply in news]

Chris Geihsler (ch************ @carecentric.co m) writes:

I have come up with the following two schemes to use these udfs. These
examples are obviously simplified, and I have a handful of stored
procedures that will use between 10 or more of these filters. If the
two are equivalent, I prefer Method 2 because it makes for much neater
SQL when using many filter criteria.

So my question is, will one noticebly outperform the other? Or is there a
better way in which to filter on a list of criteria?

The two methods are not necessarily equivalent. But assuming that
patient_id is unique in the output from the function, they are.

I would probably go for a variation of Method 1:

SELECT *
FROM open_bills ob
JOIN udf_filter_pati ent (@patient_list) fp
ON fp.patient_id = ob.patient_id

No need for a temp table. And use table aliases for conciseness. (And
don't use SELECT * in prodcution code.)

However, from a expressive point of view Method 2 may be more
palatable. I would use EXISTS rather than IN though. By the
way, I think Hugo is wrong when he says that Method 2 will call
the UDF repeatedly. He might be mixing table UDFs with scalar UDFs;
the latter can be real performance killers.
--
Erland Sommarskog, SQL Server MVP, so****@algonet. se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Hugo Kornelis]

On Thu, 20 May 2004 22:58:27 +0000 (UTC), Erland Sommarskog wrote:

[posted and mailed, please reply in news]

Chris Geihsler (ch************ @carecentric.co m) writes:

I have come up with the following two schemes to use these udfs. These
examples are obviously simplified, and I have a handful of stored
procedures that will use between 10 or more of these filters. If the
two are equivalent, I prefer Method 2 because it makes for much neater
SQL when using many filter criteria.

So my question is, will one noticebly outperform the other? Or is there a
better way in which to filter on a list of criteria?

The two methods are not necessarily equivalent. But assuming that
patient_id is unique in the output from the function, they are.

I would probably go for a variation of Method 1:

SELECT *
FROM open_bills ob
JOIN udf_filter_pati ent (@patient_list) fp
ON fp.patient_id = ob.patient_id

No need for a temp table. And use table aliases for conciseness. (And
don't use SELECT * in prodcution code.)

However, from a expressive point of view Method 2 may be more
palatable. I would use EXISTS rather than IN though. By the
way, I think Hugo is wrong when he says that Method 2 will call
the UDF repeatedly. He might be mixing table UDFs with scalar UDFs;
the latter can be real performance killers.

Hi Erland,

I might have misread the OP. I thought that each stored procedure would
contain multiple queries and that the UDF would be called in each of those
queries.

Thanks for correcting me!

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[Erland Sommarskog]

Hugo Kornelis (hugo@pe_NO_rFa ct.in_SPAM_fo) writes:

I might have misread the OP. I thought that each stored procedure would
contain multiple queries and that the UDF would be called in each of those
queries.

I didn't get that part, but of course he plans use the unpacked list
more than once it makes sense to put it in a temp table. I thought you
meant to say that the UDF would be called more than once in a single
query.
--
Erland Sommarskog, SQL Server MVP, so****@algonet. se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp