473,394 Members | 1,746 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,394 software developers and data experts.

Filter Criteria - Temp Table Join or Where clause?

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_patient]
(
@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.patient_id = open_bills.patient_id

GO
Method 2 :

CREATE PROC sp__filter_open_bills
@patient_list varchar(2000)
AS

SELECT
*
FROM
open_bills
WHERE
open_bills.patient_id IN ( SELECT patient_id FROM
dbo.udf_filter_patient( @patient_list ) )

GO

Thanks for the help!

Chris G




Jul 20 '05 #1
4 12277
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_patient]
(
@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.patient_id = open_bills.patient_id

GO
Method 2 :

CREATE PROC sp__filter_open_bills
@patient_list varchar(2000)
AS

SELECT
*
FROM
open_bills
WHERE
open_bills.patient_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)
Jul 20 '05 #2
[posted and mailed, please reply in news]

Chris Geihsler (ch************@carecentric.com) 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_patient (@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
Jul 20 '05 #3
On Thu, 20 May 2004 22:58:27 +0000 (UTC), Erland Sommarskog wrote:
[posted and mailed, please reply in news]

Chris Geihsler (ch************@carecentric.com) 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_patient (@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)
Jul 20 '05 #4
Hugo Kornelis (hugo@pe_NO_rFact.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
Jul 20 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

11
by: Matt | last post by:
Hi everyone, still pretty new to MySQL. I was wondering if there is a way to automatically filter records based on a mysql userlogin name?? I have serveral databases that I want to combine in...
8
by: dick | last post by:
I am just trying to print/report the results of a "filter by selection" which is done by right-clicking a form, filling in values, and "applying the filter." I have searched the newsgroups, and...
3
by: Stewart | last post by:
Hi all! My (relatively small) database holds data on staff members and the projects (services) that they are assigned to. In my form frmStaff, I have a list of staff members - it is a...
8
by: Steve Jorgensen | last post by:
Mailing List management is a good example of a case where my conundrum arises. Say there is a m-m relationship between parties and groups - anyone can be a member of any combintation of groups. ...
3
by: dhowell | last post by:
In reading some of the posts on this group, it appears as though it is not strait forward at all to filter a form, which has subforms, by criteria which are either on subforms or span more than one...
1
by: lorirobn | last post by:
Hi, I have a report that works just fine. Now I would like to add the capability to choose selection criteria to limit what is displayed. I created several reports that do this, but they used...
2
by: tricard | last post by:
Good day all, I have a large outer joined query that I want to have some criteria. The select query is gathering all part numbers from tblPartNumbers, left joining to tblPartNumberVendor (since...
1
by: mtnbikur | last post by:
Lots of reading out there says you should only place “join criteria” in the join clause. That makes perfect sense. What about other search criteria? I’ve got other criteria, ie. cst_delete_flag =...
1
by: jcf378 | last post by:
Hi all-- Does anyone have any insight as to how I might create a search form that allows a user to select criteria based on any related table in the whole database. The search form I have now only...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
0
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
0
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.