Can a managed udf contain a static dictionary<>? Is this a wise idea?

I currently have a udf written in T-SQL that's getting way too logically complicated!

It’s typically accessed like this:

SELECT PartNumber,dbo.PartPrice(Manufacturer, Model, AssemblageInfo, Version, CustomerDiscountLevel) FROM WorkOrders where OrderNumber=123456

The udf does some complicated manipulations on the parameters and eventually does a SELECT on a lookup table and returns the result.

If I make this a managed code udf, the logic gets much simpler to write (great!).

But, my question is:

Can I take the lookup table and embed it in the udf--so the udf doesn't have to go to the database to do the lookup?

Would I do that in a STATIC dictionary<>?

Is it wise to keep the info statically?

The lookup table consists of 3600(+/-) elements and changes exactly once a month.

The SELECT statement using the udf typically returns several thousand rows.

The SELECT is done often.

--Mark

If the table changes over the time, I would not implement this as a static dictionary as you would have to recreate the function everytime the tables changes and you would hae more trouble changing the data of the table than it would be in a normal static data table in SQL Server.

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

Hi Jens!

Thanks for the response!

I thought of the refresh function thing.

I 'm willing to make it a monthly chore to refresh the function in exchange for a faster static dictionary lookups--but only if it really will speed up the SELECTs.

I guess this brings the question of when the static dictionary goes out of scope on a function call.

-

For example, if the dictionary is reloaded for each row of a SELECT, then it's worse than worthless!

If the dictionary is retained for all rows in a SELECT statement, it **might** be useful.

If the dictionary is retained over many SELECT's then it's worthwhile.

If the dictionary is retained all month, then it's priceless (as the mastercard commercial says!).

When does a static dictionary within a function go out of scope?

Is a static dictionary retained in memory for all rows in a SELECT statement?

Is a static dictionary retained in memory between function calls?

Does the SQL engine cache the function and then release the dictionary from memory after a time-out period?

Thanks, Mark

|||

I finally got to a machine with SQL2005 to try the static dictionary<>

Turns out you can't even use static variables in a managed udf--at least not without declaring the code as unsafe.

--Mark