GregoryAJackson (3/4/2008)
function use in the SELECT List and or on the JOIN can also cause severe problems (maybe even worse then in the WHERE Clause).by placing a Function in the select, that function will execute 1x per row.
We had these at my work also when I first got there. They were causing terrible performance system wide.
Removing them all helped immensely. typically you can replace the functions with some other mechanism of retrieving the data needed (Derived tables, correlated subQueries, etc)
you really need to pay close attention to the Query Plan and specifically to the SubTree cost of the plan. If the SubTree cost approaches 10.0 (in my experience), you need to consider modifying the query.
Cheers,
GAJ
Actually - there was a thread recently on this - and that is by no means true in general. Meaning - there are lots of exceptions. Simple scalar functions, especially deterministic ones, tend to be treated as scalar values that simply replicate on down the line.
It's largely a matter of what goes in (inputs), whether the inputs are based on the row, whether the function is in effect deterministic AND is marked correctly as such (as I came to find out).
Now - if you were using functions to mask a CSQ - then yes - that can be a terrible drag on your performance, but still - it depends a LOT on what the function is doing.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?