May 4, 2010 at 7:58 am
-- Option 1
select ClientId, Name, id from category c
inner join [dbo].[fn_CSVToTableTest]('1,2,3,4,5,6,7,8') csv on c.ClientId = csv.columnData where [Name] = 'General'
------
-- Option 2
select ClientId, Name, id from category
where Clientid in (Select * from [dbo].[fn_CSVToTableTest]('1,2,3,4,5,6,7,8')) and
[Name]='General'
From above two queries which one is best? Plan is also attached
Shamshad Ali
May 4, 2010 at 8:02 am
Which one runs faster?
Use STATISTICS IO and STATISTICS TIME or SQL Profiler to get cpu, IO and duration.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 4, 2010 at 8:05 am
Make sure you're testing on data that's similar to production because query plans can change with different data sizes and distributions.
May 4, 2010 at 8:36 am
On sample data both seems running as same. That's why i have included sql plan here. Please review and suggest which one should be used and normal practice.
Thanks for your time,
Shamshad Ali.
May 4, 2010 at 9:24 am
The problem is you can't count on the estimated costs within execution plans to make a determination whether or not they're performing well because those costs are based on an estimated one row. If you're moving more than one row through those UDF's, then that execution plan is effectively a lie. So, what are the measured execution times, cpu & i/o use? Without those, we're comparing apples to cars.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 5, 2010 at 6:14 am
Where is exec plan for IN query ? i didnt find it
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
May 5, 2010 at 8:15 am
My experience is that function references in the FROM clause, i.e. inner joins, usually dramatically outperform function references in the WHERE clause because the WHERE clause must execute the function against each row returned.
"Beliefs" get in the way of learning.
May 5, 2010 at 8:29 am
Robert Frasca (5/5/2010)
My experience is that function references in the FROM clause, i.e. inner joins, usually dramatically outperform function references in the WHERE clause because the WHERE clause must execute the function against each row returned.
That's a common misconception. Go check out the article by Paul Randal & Kim Tripp in the latest SQL Server magazine. With the way SQL Server interprets that information, especially on an INNER JOIN, there's no difference in terms of placement to performance and execution plans.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 5, 2010 at 8:45 am
Thanks for the tip, I will. I have the magazine but hadn't had a chance to look at it or the e-version yet.
"Beliefs" get in the way of learning.
May 5, 2010 at 8:55 am
While it doesn't address functions in the join/where, this is a look at how IN and joins compare:
http://sqlinthewild.co.za/index.php/2010/01/12/in-vs-inner-join/
http://sqlinthewild.co.za/index.php/2010/04/27/in-exists-and-join-a-roundup/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 6, 2010 at 6:52 am
The best plan choices would likely be made (over many iterations with many different values in the delimited string) by putting the values into a temporary table first and then joining to that. This will be especially true if you have either widely varying numbers of rows delimited out OR widely varying data distribution in the joined table.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 6, 2010 at 7:20 am
Grant Fritchey (5/5/2010)
Robert Frasca (5/5/2010)
My experience is that function references in the FROM clause, i.e. inner joins, usually dramatically outperform function references in the WHERE clause because the WHERE clause must execute the function against each row returned.That's a common misconception. Go check out the article by Paul Randal & Kim Tripp in the latest SQL Server magazine. With the way SQL Server interprets that information, especially on an INNER JOIN, there's no difference in terms of placement to performance and execution plans.
Hi Grant,
I did read that article and I agree with what they said because that has been my experience but I think they were talking about a different scenario. They were talking about applying a relatively simple filter in the FROM clause versus the WHERE clause. (I've gotten burned by the OUTER JOIN scenario they mentioned.)
I should probably have been more specific. It's been my experience that table valued functions perform better in the FROM clause versus the WHERE clause. I'm not really a fan of table valued functions but I've inherited a bunch of them and until I am granted permission to fix them (they're considered low priority despite the performance hit primarily because of QA issues, regression testing would be expensive) I have to live with them. I'll see if I can drum up an example to back up my claim.
Regards,
Bob
"Beliefs" get in the way of learning.
May 6, 2010 at 7:29 am
Robert Frasca (5/6/2010)
Grant Fritchey (5/5/2010)
Robert Frasca (5/5/2010)
My experience is that function references in the FROM clause, i.e. inner joins, usually dramatically outperform function references in the WHERE clause because the WHERE clause must execute the function against each row returned.That's a common misconception. Go check out the article by Paul Randal & Kim Tripp in the latest SQL Server magazine. With the way SQL Server interprets that information, especially on an INNER JOIN, there's no difference in terms of placement to performance and execution plans.
Hi Grant,
I did read that article and I agree with what they said because that has been my experience but I think they were talking about a different scenario. They were talking about applying a relatively simple filter in the FROM clause versus the WHERE clause. (I've gotten burned by the OUTER JOIN scenario they mentioned.)
I should probably have been more specific. It's been my experience that table valued functions perform better in the FROM clause versus the WHERE clause. I'm not really a fan of table valued functions but I've inherited a bunch of them and until I am granted permission to fix them (they're considered low priority despite the performance hit primarily because of QA issues, regression testing would be expensive) I have to live with them. I'll see if I can drum up an example to back up my claim.
Regards,
Bob
Table valued functions is a different critter entirely. Are you talking multi-statement tvfs? Those things will give you a bad day, regardless of where you put them.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 6, 2010 at 8:00 am
Grant Fritchey (5/6/2010)
Robert Frasca (5/6/2010)
Grant Fritchey (5/5/2010)
Robert Frasca (5/5/2010)
My experience is that function references in the FROM clause, i.e. inner joins, usually dramatically outperform function references in the WHERE clause because the WHERE clause must execute the function against each row returned.That's a common misconception. Go check out the article by Paul Randal & Kim Tripp in the latest SQL Server magazine. With the way SQL Server interprets that information, especially on an INNER JOIN, there's no difference in terms of placement to performance and execution plans.
Hi Grant,
I did read that article and I agree with what they said because that has been my experience but I think they were talking about a different scenario. They were talking about applying a relatively simple filter in the FROM clause versus the WHERE clause. (I've gotten burned by the OUTER JOIN scenario they mentioned.)
I should probably have been more specific. It's been my experience that table valued functions perform better in the FROM clause versus the WHERE clause. I'm not really a fan of table valued functions but I've inherited a bunch of them and until I am granted permission to fix them (they're considered low priority despite the performance hit primarily because of QA issues, regression testing would be expensive) I have to live with them. I'll see if I can drum up an example to back up my claim.
Regards,
Bob
Table valued functions is a different critter entirely. Are you talking multi-statement tvfs? Those things will give you a bad day, regardless of where you put them.
This function is the top cpu user in our system, primarily because it's referenced in about three hundred different procedures and (shudder) views. Please ignore the use of NOLOCK while READ COMMITTED SNAPSHOT is turned on (rendering it useless). That's another battle I'm losing because much of this code was developed on SQL Server 2000 and managment is literally afraid to remove NOLOCK because of the locking nightmares they had.
CREATE FUNCTION [PM].[fnNoteTypes] ( @Operator_ID int ) RETURNS @Note_Types TABLE
(
Note_Type_IDint,
Abbreviation char(8),
Descriptionvarchar(40),
Type_Of_Notechar(1),
Flagchar(1),
Prevent_New_Appointmentsbit,
Hold_Statementsbit,
Default_Textvarchar(7500),
Prevent_Encounter_Form_Printbit,
Associated_Department_IDint,
NoteWarning_AcctLedgerbit,
NoteWarning_ApptSchedulingbit,
NoteWarning_ChargeEntrybit,
NoteWarning_Editsbit,
NoteWarning_FinInquirybit,
NoteWarning_PaymentEntrybit,
NoteWarning_Registrationbit,
Default_Subjectvarchar(80),
Allow_Editable_Textbit
)
AS
BEGIN
IF EXISTS (SELECT * FROM PM.Operator_Dept_Membership WITH (NOLOCK) WHERE Operator_ID = @Operator_ID)
BEGIN
INSERT @Note_Types
SELECT NOTETYPES.Note_Type_ID,
NOTETYPES.Abbreviation,
NOTETYPES.Description,
NOTETYPES.Type_Of_Note,
NOTETYPES.Flag,
NOTETYPES.Prevent_New_Appointments,
NOTETYPES.Hold_Statements,
NOTETYPES.Default_Text,
NOTETYPES.Prevent_Encounter_Form_Print,
NOTETYPES.Associated_Department_ID,
NOTETYPES.NoteWarning_AcctLedger,
NOTETYPES.NoteWarning_ApptScheduling,
NOTETYPES.NoteWarning_ChargeEntry,
NOTETYPES.NoteWarning_Edits,
NOTETYPES.NoteWarning_FinInquiry,
NOTETYPES.NoteWarning_PaymentEntry,
NOTETYPES.NoteWarning_Registration,
NOTETYPES.Default_Subject,
NOTETYPES.Allow_Editable_Text
FROMPM.Note_Types NOTETYPES WITH (NOLOCK)
LEFT JOIN PM.Operator_Dept_Membership OPERDEPTMBR WITH (NOLOCK) ON OPERDEPTMBR.Department_ID =
NOTETYPES.Associated_Department_ID
WHERE NOTETYPES.Associated_Department_ID IS NULL OR OPERDEPTMBR.Operator_ID = @Operator_ID
END
ELSE
BEGIN
INSERT @Note_Types
SELECT Note_Type_ID,
Abbreviation,
Description,
Type_Of_Note,
Flag,
Prevent_New_Appointments,
Hold_Statements,
Default_Text,
Prevent_Encounter_Form_Print,
Associated_Department_ID,
NoteWarning_AcctLedger,
NoteWarning_ApptScheduling,
NoteWarning_ChargeEntry,
NoteWarning_Edits,
NoteWarning_FinInquiry,
NoteWarning_PaymentEntry,
NoteWarning_Registration,
Default_Subject,
Allow_Editable_Text
FROMPM.Note_Types WITH (NOLOCK)
END
RETURN
END
"Beliefs" get in the way of learning.
May 6, 2010 at 8:11 am
My statement above applies to TVFs also, at least ones that aren't inlined by the optimizer.
I will add that as a consultant in the past 3-4 years I have probably spent more time fixing UDF-related problems at various clients than I have spent dealing with any other single topic. And believe me, that is saying a LOT about how bad UDFs can be.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply