I am using SQL Server 2008 and C# for a CLR Stored Procedure. Is there any way to keep in memory a big object and to retrieve this object after a second call of the Stored Procedure?
In the second call, I need to retrieve the data of this big object as quickly as possible. This object is like a table.
If there is no way, I was thinking about the serialization. Or I was thinking to implement something by myself to speed-up the process. What do you think?
Thanks!
The easiest way would be to serialize it to a file somewhere. I wouldn't hold it in memory because you are not given any hooks as to when to clear this memory.
There are a few posts about file access here:
http://social.msdn.microsoft.com/Forums/en-US/sqlnetfx/thread/45b46ce0-a4b3-45d0-86ec-1c1638fb7cb2
If this is a table-like structure, then another easy and probably fast-enough way is to create a temporary table that the CLR can select out of using a data reader. Personally I would go this route and prove it isn't fast enough before taking other routes.
The only way to store an object in memory within sql server is to place it in a static field. This can work, however doing so requires writing some relatively tricky code to ensure it is thread-safe and the lifetime is managed correctly. Under sql server, the clr appdomain may be unloaded and regenerated at essentially any point in time, so one must be cautious to account for this. There is also the possibility of multiple appdomains for the same assembly living concurrently but processing different query requests during ddl operations.
Adam Machanic has written an article about some of these implications of using static fields as a cache and the security implications and best-practices associated with doing so.
