function executed as part of a stored procedure?

Hi there!

I've a stored procedure who gets a lot of data sets (50.000 - 200.000). For each of this data set i've to make further (recursiv) calculations what i do in an user defined function. The problem is that the functions needs to make a heavy query (everytime the same) and calculates something based on this query resultset. But the query requieres ~ 600ms, what results in a great amount of time (i.e. 6 hours)by executing this function 50.000 or more times.

I am wondering if it is possible to create the function as part of the stored procedure, so that the heavy query is beeing executed only one time and the function run in the scope of the stored procedure.

Is there any idea?

Cheers, Torsten

Torsten:

What exactly are you meaning by 50,000 - 200,000 result sets? Do you mean records or do you mean 50,000 -200,000 multi-record datasets? To me it sounds like you are talking about a record-based process instead of a set-based process.

If so, then yes, you should by all means convert the stored procedure to a set-based process and call your function only one time.

It would help if you could provide the main part of the process you are doing and important elements of your data schema.

|||

Hi Kent!

My procedure makes something like

select a,b,c,d,e,f,dbo.fct_my_funktion(a,b,c,d,e,f) amount from somewhere. This are > 50.000 sets, so the function is called > 50.000 times.

The function dbo.fct_my_funktion makes something like that:

Insert into a table var (...) (Select <a hierarchy left outer join to somethiong> from x,y.) <<<- That is the bottleneck

Aggregate recursiv on the table var <<<- That is very fast (3 to 20 ms, that quiet okay)

return the amount

Torsten

|||

Torsten:

A few things. First, you have done a good job of identifying your bottleneck. If this insert statement is taking about 600 ms per iteration then yes, this is what is killing you. Therefore, it would be good if you could give details that relate to this particular insert statement. Also, is it possible for you to run of the SELECT statements for that INSERT in isolation and get the QUERY PLAN for that particular SELECT.

Also, a couple of other issues. Are you running SQL Server 2005? A secondary issue here is that you are using a SCALAR function. It would be best if we could retrieve this information by some other means because scalar functions tend to be inefficient.

But FIRST: we need to look at the SELECT that statement.

|||

I did it!

I'm running SQL Server 2005. The solution ist to create a cursor (over the very heavy query - is only executed once) and then calculate the staff for each row in the cursor loop. So i dont need to use another function - i have gone from ~ 600ms per row to 13-17ms. That's quite good.

Thank you for your comments, may be it has openend my mind...

Greetings from germany,

Torsten