Inner Join v/s WHERE Col IN (select col from dbo.UDF_Function)

Question

Inner Join v/s WHERE Col IN (select col from dbo.UDF_Function)

Viewing 4 posts - 16 through 18 (of 18 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398881 More actions · Answer 1

TheSQLGuru (5/6/2010)
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.

Total agreement. I've never seen performance so bad as that generated by UDF's, especially multi-statement table valued UDF's.

"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

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 2

>> WHERE NOTETYPES.Associated_Department_ID IS NULL OR OPERDEPTMBR.Operator_ID = @Operator_ID

That type of WHERE clause is HORRIBLE from a performance standpoint. Our own Gail Shaw has a very nice blog post about this topic. Read it and fix the code!!

http://sqlinthewild.co.za is the main blog link. Great stuff there!

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 3

TheSQLGuru (5/6/2010)
>> WHERE NOTETYPES.Associated_Department_ID IS NULL OR OPERDEPTMBR.Operator_ID = @Operator_ID
That type of WHERE clause is HORRIBLE from a performance standpoint. Our own Gail Shaw has a very nice blog post about this topic. Read it and fix the code!!
http://sqlinthewild.co.za is the main blog link. Great stuff there!

can you tell me the link ?

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Bhuvnesh (5/6/2010)
TheSQLGuru (5/6/2010)
>> WHERE NOTETYPES.Associated_Department_ID IS NULL OR OPERDEPTMBR.Operator_ID = @Operator_ID
That type of WHERE clause is HORRIBLE from a performance standpoint. Our own Gail Shaw has a very nice blog post about this topic. Read it and fix the code!!
http://sqlinthewild.co.za is the main blog link. Great stuff there!
can you tell me the link ?

He did, see bolded text. It's one of the 'popular posts' linked in the sidebar at the top.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass