Caching UDF

Hi All,

I have an application that reads data from a very slow database link
(like 10 seconds per call) though what I am looking for would be of
generic use for anyone who has long-running queries that are
frequently repeated.

I would like to be able to cache the results of a query so that I do
not have to re-execute that query if it is reissued. Ideally I
believe that this could be implemented by hiding the query inside a
UDF and exposing the UDF through a view. The UDF could then "Check
the cache" and only run the slow query if there wasn't a match (or if
the match was too old). From what I understand the best way to do
this would be for the cache to be an extended stored procedure.

Has anyone done or seen this? Has someone written a copy that I
could purchase? Does anyone care to offer their opinnion of how or if
this could work?

Thanks in Advance,

StevenHi

Caching like this is usually the function of a middle tier rather than the
database.

John

"Steven Ensslen" <ensslen@.planet-save.com> wrote in message
news:73ce0e91.0405141350.716061eb@.posting.google.c om...
> Hi All,
> I have an application that reads data from a very slow database link
> (like 10 seconds per call) though what I am looking for would be of
> generic use for anyone who has long-running queries that are
> frequently repeated.
> I would like to be able to cache the results of a query so that I do
> not have to re-execute that query if it is reissued. Ideally I
> believe that this could be implemented by hiding the query inside a
> UDF and exposing the UDF through a view. The UDF could then "Check
> the cache" and only run the slow query if there wasn't a match (or if
> the match was too old). From what I understand the best way to do
> this would be for the cache to be an extended stored procedure.
> Has anyone done or seen this? Has someone written a copy that I
> could purchase? Does anyone care to offer their opinnion of how or if
> this could work?
> Thanks in Advance,
> Steven|||[posted and mailed, please reply in news]

Steven Ensslen (ensslen@.planet-save.com) writes:
> I have an application that reads data from a very slow database link
> (like 10 seconds per call) though what I am looking for would be of
> generic use for anyone who has long-running queries that are
> frequently repeated.
> I would like to be able to cache the results of a query so that I do
> not have to re-execute that query if it is reissued. Ideally I
> believe that this could be implemented by hiding the query inside a
> UDF and exposing the UDF through a view. The UDF could then "Check
> the cache" and only run the slow query if there wasn't a match (or if
> the match was too old). From what I understand the best way to do
> this would be for the cache to be an extended stored procedure.

Unless I am misunderstanding something, this won't fly at all. The UDF
and the extended stored procedure still executes on the server, so there
is no cache you could retrieve data from. SQL Server maintains a cache, but
that is from disk to local memory, so from your point of view, this is
still on the remote side of your link.

For such a cache to be meaningful, you must have it on your side of the
link. Thus, the typical place to fix this would be in the application
itself (unless there is a separate middle tier between the application
and the database).

If this is an application you cannot modify, you might still be able to
do it, but it will be hairy. In this case you would point your application
to a local SQL Server, which use linked servers to access the remote
server, and this local server would implement a cache. But how you would
load the cache and keep int current is far from trivial. To develop this,
I wold need some more information to proceed.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks for the replies, but I guess that I haven't explained my idea
clearly enough.

> Unless I am misunderstanding something, this won't fly at all. The UDF
> and the extended stored procedure still executes on the server, so there
> is no cache you could retrieve data from. SQL Server maintains a cache, but
> that is from disk to local memory, so from your point of view, this is
> still on the remote side of your link.
> For such a cache to be meaningful, you must have it on your side of the
> link. Thus, the typical place to fix this would be in the application
> itself (unless there is a separate middle tier between the application
> and the database).

I'm looking for a custom-coded,programmer-activated, server-side
cache. I want to be able to store an arbitrary string so that it
persists for my entire database session and I do not have to execute
the expensive query that generated that string more than once.

> If this is an application you cannot modify, you might still be able to
> do it, but it will be hairy. In this case you would point your application
> to a local SQL Server, which use linked servers to access the remote
> server, and this local server would implement a cache. But how you would
> load the cache and keep int current is far from trivial. To develop this,
> I wold need some more information to proceed.

You're correct that I can't modify the application. So I'd like the
local server to implement a cache of the remote server.

Has anyone done this? Does anyone have an example or know of a 3rd
party program/extension that will perform this function?

Steven|||Steven Ensslen (ensslen@.planet-save.com) writes:
> I'm looking for a custom-coded,programmer-activated, server-side
> cache. I want to be able to store an arbitrary string so that it
> persists for my entire database session and I do not have to execute
> the expensive query that generated that string more than once.

I'm afraid that I don't really follow. Can you give an overview the
architecture of the application as it works now? I mean which boxes
you have, and where the slow link is.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp