Question 1

Question

Question 1

Sreemati

Old Hand

Points: 358
More actions
April 26, 2007 at 4:45 am

#116877

Hi everyone,
Can anyone explain to me how using Funtion in WHERE clause of the SQL QUERISE effect the serever performance?
The data server is very very large with loads and loads of record.
Thanks
Sree

Viewing 4 posts - 1 through 3 (of 3 total)

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

Jeff Moden SSC Guru Points: 1004307 More actions · Answer 1

Depending on how and where the function is used in a WHERE clause, it can drastically and negatively affect performance because it may disable the ability for INDEX SEEKs to occur. And, it doesn't matter if it's a UDF or a system function... although UDF's are typically poorly written and may be a performance problem all by them selves...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jo Pattyn SSC-Dedicated Points: 32342 More actions · Answer 2

depends if the function is based on a datacolumn (needs calculation per record) or if the function is based on a condition (calculated once, calculated value is than a candidate for indexuse)

schleep SSChampion Points: 12583 More actions · Answer 3

The other thing is, the function which runs against every row in a query amounts to row by row processing, something that could only be accomplished with a while loop or a cursor in the past.

And we all know to avoid those whenever we can, ideally almost always.

P