x

CREATE FUNCTION executes during the UPDATE statement?

I was executing a statement:

UPDATE [TableName] SET Col_1 = dbo.FunctionName(Col_2, Col_3)

It was taking long time to execute, so I ran a query to see the activity on the server:

SELECT d1.session_id, d3.[text] FROM sys.dm_exec_sessions d1 JOIN sys.dm_exec_requests d2 ON d1.session_id=d2.session_id CROSS APPLY sys.dm_exec_sql_text(d2.sql_handle) d3

, and saw there CREATE FUNCTION dbo.FinctionName statement, and none other activity. And when I opened the Activity Monitor it was showing a zillion of "U" page locks on the [TableName]. There are several indexes on that table with ALLOW_PAGE_LOCKS = ON. Were they causing the problem? And why the CREATE FUNCTION statement was executing (and never able to finish) during the simple update statement?
more ▼

asked Jun 21, 2011 at 06:30 AM in Default

Tatyana gravatar image

Tatyana
413 15 15 18

(comments are locked)
10|1200 characters needed characters left

2 answers: sort oldest

When you see the CREATE FUNCTION in the activity, this is just showing you the definition of the function that is running, it doesn't mean it is creating the function (that's a one-time thing, that you've already done).

However 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.
more ▼

answered Jun 21, 2011 at 06:42 AM

Kev Riley gravatar image

Kev Riley ♦♦
53k 47 49 76

I type too slow, and my wife is having a crisis on her computer (Libraries aren't displaying properly, end of the world).
Jun 21, 2011 at 06:45 AM Grant Fritchey ♦♦
Thank you, Kev and Grant. I understand it about CREATE FUNCTION statement. And I rewrote the code to avoid updating a table using a function, and it did execute in a much more reasonable time. I wonder if the same rule would apply to calling a function in the WHERE clause. Maybe you could suggest a good resource to read more on this subject. Thank you!
Jun 21, 2011 at 08:04 AM Tatyana
Functions in WHERE clauses can cause issues... mostly preventing the use of an index. @Grant Fritchey covers it in his "SQL Server 2008 Query Performance Tuning Distilled" book. A good read and it should be on your bookshelf.
Jun 21, 2011 at 08:10 AM Blackhawk-17
I just bought this book! Maybe it's time to read it now :) Thank you!
Jun 21, 2011 at 08:40 AM Tatyana
(comments are locked)
10|1200 characters needed characters left

One of two things, check the code, someone might have accidently wrapped a CREATE statement inside some other code, usually be misplacing GO statements.

Or, you're see the CREATE statement just as a marker for what is being executed. For example, if you run queries against the DMO objects and join to sys.dm_exec_sql_text and you don't use the offsets to identify the statement being executed, you'll see a CREATE statement for the procedure/view/function. It could just be that.
more ▼

answered Jun 21, 2011 at 06:44 AM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
100k 19 21 74

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x39
x19
x13

asked: Jun 21, 2011 at 06:30 AM

Seen: 1799 times

Last Updated: Jun 21, 2011 at 06:30 AM