PDA

View Full Version : eliminating repeated function calls for each record



Roelf
Jun 29th, 2007, 01:20 PM
I have to optimize a (MSSQL 2000) query which will perform a call to a user defined function for each field in the returned recordset, like this:



select
a * dbo.functionname(field10, field11, field12),
b * dbo.functionname(field10, field11, field12),
c * dbo.functionname(field10, field11, field12).
.
.
.
from
thetable

where field10, field11 and field12 are fields of the current record, as well as a, b and c.
The function called is a bit heavy though, and there are really many records to return, and a lot of fields which need this multiplication

What i want to accomplish is that for each record, the function is called once, and the result of the call will be used for the fields i want to retrieve.

So something like:


declare @varname numeric (18,7)
select
-- first determine the result of the function and store that into a variable
set @varname = dbo.functionname(field10, field11, field12)
a * @varname,
b * @varname,
c * @varname.
.
.
.
from
thetable


But this doesn't work. Is this even possible in some way? I have spent half a day googling and diggin into sql server books online.

javabits
Jul 3rd, 2007, 08:44 PM
Have you tried something like this



select a * result,
b * result,
c * result
from
(
select a, b, c, dbo.functionname(field10, field11, field12) as result
from thetable
) tmp


semper fi...

Roelf
Jul 4th, 2007, 08:13 AM
No, not yet. I've tried to give the function result an alias in the select part of the query, that did not work because sql server does not know the alias 'result' before the resultset is sent to the client. That is also the reason why you cannot use aliases in join conditions etc. But i have not tried to apply the alias in the from clause of the query. Will do that.

javabits
Jul 4th, 2007, 09:07 AM
What's happening is that the query inside of the parentheses is creating what is called a derived table. If you were to break it down the query inside the parentheses is done first and then a table is created from the results which is used for the outer query.

The other alternative is to use temporary tables. I've seen people resort to temporary tables, personally I'm not a big fan of it but to each their own.

semper fi...

Roelf
Jul 4th, 2007, 12:10 PM
javabits,

this took a while to incorporate that in my original query, because the original query did not fetch from one table, but from a rather complex join. But i was able to get it to work and the performance gain was more than 50% so thanks for your advice. i did not know the trick of the derived table, but it workes like a charm.

Thanks again!

Roelf

javabits
Jul 5th, 2007, 07:21 PM
Glad I could be of assistance. I've found that by helping answer questions here, it actually helps me keeps my skills sharp as I've probably forgotten about as much as I learned. Honestly I didn't remember that it was called a derived table (thank goodness for google), but at least I still remembered how to use it.

I've also found that within the industry developers who can communicate a solution are far more valuable.

It's cool that you got a 50 percent performance gain too. Appreciate that type of feedback as that way everyone learns something.

semper fi...