473,714 Members | 3,077 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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_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




Jul 20 '05 #1
4 12293
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)
Jul 20 '05 #2
[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
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.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)
Jul 20 '05 #4
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
Jul 20 '05 #5

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

Similar topics

11
11988
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 order to manage the databases more efficiently. - I'm currently using MySQL 4.1.12 and I'm currently testing 5.0.14 - I have a databases for each user account. - Each database has the same schema and entity relation model.
8
6532
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 there are many examples. BUT, they fail sometimes. The techique is to pass the form's Me.filter as the "where condition" in a Docmd.openreport statement in code behind a "print button" on the form.
3
12500
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 continuous form. Each staff member is linked to a Service through a many-to-many relationship, using a junction table called jctStaffServices. I would like to place a Combo Box in frmStaff where you can 'filter' staff by the Service (i.e. ServiceName)...
8
2959
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. Now, let's say the user wants to be able to send mailings to people who have various combinations of membership and non-membership in those groups. Here's a medium-complex example: (Knitting Group or Macrame Group) and Active Contact and Mailing...
3
11966
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 subform..... This MSDN article however makes it sound as simple as clicking "Filter by Form", entering the search criterial in the form and/or it's subforms, then just clicking "Apply Filter"...... ...
1
16385
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 VBA to create SQL statements, then apply them. Now that I have been using Access/VBA for awhile, I am wondering if there is a simpler way to do this than coding these long sql statements. My report has a query as its record source. Can I just...
2
3143
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 more than one vendor can make the part), then left joining to tblPartNumberSupplier (since more than one supplier can distribute the vendor's part), then left joining to tblPartNumberCost (since more than one cost can be associated with a single...
1
2053
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 = 0 that predecessors have put in the where clause and sometimes it’s programmed into the join clause. Ie. Select cst_id, cst_name, from co_customer left join mb_membership on mbr_cst_key = cst_key and mbr_delete_flag = 0 where...
1
4166
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 allows me to filter based on variables in a single table. I would like to have a search form where I can select multiple variables (from various linked tables) to filter by, and return results based on this multi-table filter. Allen Browne...
0
8808
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, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8712
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9316
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, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9179
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 tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
9023
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 protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7954
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
5959
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
1
3160
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2525
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

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.