[posted and mailed, please reply in news]
Steven Ensslen (en*****@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,
so****@algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp