Update statement with a function

functionsssmsupdate

I'm just curious. I was trying to optimize an update statement with a function

Ex

update circle set area = fnGetArea(radius) where ...<bunch of conditions>

This update went 5-10mins and when I removed the fnGetArea, it went 5-10 seconds!

How did that happen…?

I want to google it but I'm not sure what to type in.

Please enlighten me! 😀

Thanks in advance

Best Answer

Answering my own question, I found this link, which contains the answer:

...by having this as a function on your update, you are essentially calling the function as many number of times as there are rows in the table. This is not the best way to do this, and is known as row-by-row processing, rather than set-based.

Try and take the processing that the function is doing and apply it to the table in one go rather than this one-at-a-time approach.