Caching Feature Results in SQL Server 2000

Question

I want to notice the results of a function for performance, i.e. lazily populating a cache indexed by function arguments. The first time I call a function, the cache will not have anything for the input arguments, so it will compute it and save it before returning. Subsequent calls simply use the cache.

However, it seems that SQL Server 2000 has a silly arbitrary rule that functions are "deterministic." INSERT, UPDATE, and regular stored procedure calls are not allowed. However, extended stored procedures are allowed. How is it determined? If another session changes the state of the database, the output of the function will change in any case.

I got angry. I thought I could make caching transparent to the user. Is it possible? I do not have permissions to deploy extended stored procedures.

EDIT:

This is a limitation back in 2008. You can't call RAND, for God's sake!

The cache will be implemented by me in the database. A cache is any data store used for caching ...

EDIT:

There are no cases where the same function arguments will give different results outside of changes in the underlying data. This is a BI platform, and the only changes come from the planned ETL, and at this time I would TRUNCATE in the cache table.

These are intensive calculations of time series of input-output, order O (n ^ 4). I have no mandate to change the underlying table or indexes. In addition, many of these functions use the same intermediate functions, and caching allows you to use these functions.

UDFs are not truly deterministic unless they take into account changes in the state of the database. What is the point? Is SQL Server Caching? (Ironic.) If SQL Server is cached, it should expire when tables bound to a schema change. If they are related to a schema, then why not bind the tables that the function modifies? I see why procs are not allowed, although this is just messy; just a circuit bundles procs. And, BTW, why allow extended stored procedures? You cannot keep track of what they are doing to ensure determinism !!! Argh !!!

EDIT:

My question is: is there a way to lazy cache result in a way that can be used in a view?

Answer 1

Deterministic means that the same inputs return the same output, independent of time and database.

SQL Server (any version) does not cache UDF - I believe that it will avoid calling UDF at the same time on the same line, but that is.

One trick I used was this (I think I posted it here on SO):

UDF refactoring, if you can, so as to effectively use the discrete subset of values ​​returned for a given set of inputs. For numerical calculations, it is sometimes possible to reorganize the logic to return a coefficient or speed that is multiplied outside the UDF, rather than multiplied inside the UDF from the transmitted value.

Call UDF on the DISTINCT rowset and cache the results in a temporary table. If you only call UDFs with 100,000 sets of parameters for a set of rows of 17,000,000, this is very much more efficient.

SAVE to temporary table (basically convert from code-based logic to table logic) to get values.

This table can be reused as needed or even saved.

Adding to the table can be done using the first LEFT JOINING to find missing cached entries.

This works for both single-row tabular UDF values ​​and scalar UDFs. I mainly use it for tabular UDFs. There is a fix for SQL Server 2005 that should meet UDF performance - I'm waiting for the database administrator to check it before deploying it to production.