persistent cache in SQL Server

T

Trapulo

Hello,
I'm developing an asp.net application that is connected to a remote system
using WCF (wshttp binding). So I want to use a local cache to manage data
that I receive from remote service.

I'm using caching block from MS application enterprise library 4.1,
configured to use a SQL Server cache. That's great: it works with very
little effort.
However I noticed that caching block mantains a copy of the cache in memory
(loading all cache when it starts). I want to matain data only in SQL
Server, because are very large data that I mantain in cache for a lot of
time (about 7 days). Is there any way I can use ONLY the SQL Server
repository instead of in.memory AND Sql Server?

thanks
 
G

Gregory A. Beamer

The idea behind cache, as MS has it, is to save time getting information by
putting it in memory. SQL is there as a backup, but it is not designed to be
a primary mechanism, as SQL retrievals are slower than memory. If you want
to store long term temporary infomration in SQL and not really cache,
consider rolling your own mechanism rather than use the MS cache bits, as
you are not really caching here.
 
T

Trapulo

Yes: I need a cache that saves time getting data from remote WCF Server,
instead of saving data from getting data from SQL Server. So SQL is a lot
faster that a remote WCF call and I need a "real cache"...

Is there some ready library I can use someone can suggest? I not want to
write my own code to have a very common scenario...

thanks
 
A

Allen Chen [MSFT]

Hi Trapulo,

From your description you want to store the data into SQL Server that is
retrieved from WCF, right? If so, as Gregory said, it's not a cache.

First I need to confirm if you need a global data structure or a
user-independent data structure. If the data is user independent I suggest
you use the ASP.NET Session. As we know, session can be stored in SQL
Server:

http://support.microsoft.com/kb/317604

You can call WCF method and store the retrieved data into Session.
If it's global data you can design a table:

Key P.K.
Value
TimeStamp

Then store the retrieved data into database and use Sql Server Agent
Service to remove the expired records every 7 days.

Please let me know if you have any questions.

Regards,
Allen Chen
Microsoft Online Support

Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/en-us/subscriptions/aa948868.aspx#notifications.

Note: MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 2 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions. Issues of this
nature are best handled working with a dedicated Microsoft Support Engineer
by contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/en-us/subscriptions/aa948874.aspx
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
 
T

Trapulo

Allen Chen said:
Hi Trapulo,

From your description you want to store the data into SQL Server that is
retrieved from WCF, right? If so, as Gregory said, it's not a cache.

Yes
However I think it's sure a cache

http://en.wikipedia.org/wiki/Cache:
"In computer science, a cache (pronounced /kæʃ/) is a collection of data
duplicating original values stored elsewhere or computed earlier, where the
original data is expensive to fetch (owing to longer access time) or to
compute, compared to the cost of reading the cache"
First I need to confirm if you need a global data structure or a
user-independent data structure

global data.
If the data is user independent I suggest
you use the ASP.NET Session. As we know, session can be stored in SQL
Server:

http://support.microsoft.com/kb/317604

You can call WCF method and store the retrieved data into Session.

this can be interesting. However, as I said, data are global
If it's global data you can design a table:

Key P.K.
Value
TimeStamp

Then store the retrieved data into database and use Sql Server Agent
Service to remove the expired records every 7 days.

Please let me know if you have any questions.

Ok thanks.
I was hoping to find a full featured complete library (as logging block).
In fact, however, it can be good to have a copy either in memory and sql
server. But logging block retains in memory all data. If it can retain, for
example, last 100 objects in memory, and 10000 in sql, it will be great (100
is too little to my requirenments, an 10000 is to big to leave in memory).
Instead of this, it has just a single parameter that sets memory and sql
cache. Is there any easy way we can extend logging block to have this goal?

thanks
 
A

Allen Chen [MSFT]

Hi Trapulo,

Quote from Trapulo =================================================
In fact, however, it can be good to have a copy either in memory and sql
server. But logging block retains in memory all data. If it can retain, for
example, last 100 objects in memory, and 10000 in sql, it will be great
(100
is too little to my requirenments, an 10000 is to big to leave in memory).
Instead of this, it has just a single parameter that sets memory and sql
cache.
=================================================

As far as I know currently the enterprise library does not provide that
flexibility. The SQL data storage for the caching block is mainly used to
backup cached data in case in-memory cache is lost due to application
crash. It's not proposed as a primary cache.

To achieve your requirement we can write our own cache mechanism. The
pseudocode may be:

using System.Web.Caching;

public class MyCacheManager
{
private static Cache _cache = new Cache();
public static void AddCache(object data,object key)
{

if (_cache.Count > 100)
{
//insert data to database
}
else
{
//add data to _cache
//optional, add a copy to database in case in-memory cache
is lost.
}


}
public static object GetCache(object key)
{
//if the data retrieved from _cache is null, access database
//if still no record retrieved, return null.
}

}

We can design a table to store the data and use SQL Server agent to remove
records every 7 days.

You can also consider using distributed caching. Though it's not the means
as you thought above it's another option that can increase performance.
http://www.codeplex.com/SharedCache

If you have further questions please feel free to ask.

Regards,
Allen Chen
Microsoft Online Community Support
 
A

Allen Chen [MSFT]

Hi Trapulo,

Do you have any progress on this issue?

Regards,
Allen Chen
Microsoft Online Community Support
 
T

Trapulo

I'm working on it.
However I think that I'll implement some simple cache as you suggested. I'll
work a while then I'll respond to your post...

thanks
 
T

Trapulo

Hi Allen,
I'm writing a cache as you suggested.

Some questions:
- to save on db may I use a standard binaryformatter to serialize objects to
a sql image field?
- is there any way I can mantain in memory-cache the last accessed objects?
Using your pseudocode I'll have in memory the first 100 requeested objects,
the I'll never use memory cache untile some item has expired
- do you think that can be a good idea to clean expired objects on
Application_End event?

Thanks
 
T

Trapulo

Allen,
I have a very strange problem.

Every time I try to access the _cache object, I have errors.

For example, if I try
" If _cache.Item(key) IsNot Nothing Then"
or, in debug:
? _cache.Item (key)
I have this error:
Run-time exception thrown :
System.Reflection.TargetParameterCountException - Parameter count mismatch.


I've tried to use a new Cache object, or to refer to
httcontext.current.cache one, but it's the same. I've also tried to
initialize the object here:
Private Shared _cache As Caching.Cache = New Cache()
or here:
Shared Sub New()
_cache = New Caching.Cache()
End Sub

but nothing.

what's wrong?

the SQL cache is working (I've used Linq to SQL to speed up), but the
in-memory cache doesn't work :(

thanks
 
A

Allen Chen [MSFT]

Hi Trapulo,

Quote from Trapulo =================================================
- to save on db may I use a standard binaryformatter to serialize objects
to
a sql image field?
=================================================
I suggest you use varbinary(max). Please refer to the following
documentation for the reason:
http://msdn.microsoft.com/en-us/library/ms187993.aspx

Quote from Trapulo =================================================
- is there any way I can mantain in memory-cache the last accessed objects?
Using your pseudocode I'll have in memory the first 100 requeested objects,
the I'll never use memory cache untile some item has expired
=================================================
A straightforward means is, in the AddCache method always replace the
oldest item with the newly added one.

To do this first we need to attach a timestamp to the cached item. In the
AddCache method, if the data need to be added into the cache we can use a
Pair object to wrap the data and the timestamp, then add it into the cache.
In the GetCache method, get the Pair object and retrieve the cached data.

To get the oldest item we need to loop through all cache items. Try
following ways to do it:

For Each entry As DictionaryEntry In _cache
' you can get the key and value via entry.Key and entry.Value and
' therefore get the timestamp
Next entry

To remove the cache, we can call _cache.Remove(string key) method.

However, I can predict performance hit of this method. You can do some
experiments first before making the final decision as to whether this
requirement is really needed. Also please note the cached items may have
been expired before trying to removing it. So please remember to check the
null value.

Quote from Trapulo =================================================
- do you think that can be a good idea to clean expired objects on
Application_End event?
=================================================
I think we don't have to clean the expired objects. ASP.NET cache has
already done this for us. We can just set the expiration time when adding
new cache items.

Quote from Trapulo =================================================
I have this error:
Run-time exception thrown :
System.Reflection.TargetParameterCountException - Parameter count mismatch.
=================================================
I'm not sure why this exception will be thrown here. I cannot reproduce it
on my side. Could you provide your code so that I can have some test?

BTW, my code is pseudocode. Please use HttpRuntime.Cache to initialize the
_cache.

Regards,
Allen Chen
Microsoft Online Community Support
 
T

Trapulo

Allen Chen said:
Quote from Trapulo =================================================
- to save on db may I use a standard binaryformatter to serialize objects
to
a sql image field?
=================================================
I suggest you use varbinary(max). Please refer to the following
documentation for the reason:
http://msdn.microsoft.com/en-us/library/ms187993.aspx

mmm I'm still using the old SQL 2000 and varbinary can contain only 8K. My
cached data can be bigger...


Quote from Trapulo =================================================
- do you think that can be a good idea to clean expired objects on
Application_End event?
=================================================
I think we don't have to clean the expired objects. ASP.NET cache has
already done this for us. We can just set the expiration time when adding
new cache items.

Yes: but items in SQL Server?
Quote from Trapulo =================================================
I have this error:
Run-time exception thrown :
System.Reflection.TargetParameterCountException - Parameter count
mismatch.
=================================================
I'm not sure why this exception will be thrown here. I cannot reproduce it
on my side. Could you provide your code so that I can have some test?

BTW, my code is pseudocode. Please use HttpRuntime.Cache to initialize the
_cache.

Ah :)
I did think that you want use a separate in-memory variable :)

However, this is my draft. I have errors every time I try to access _cache.
I added a boolean that disable in-memory cache only to go on with
development.

Imports System.Web

Public Class WcfCache

Shared Sub New()
If EnableInMemoryCache Then _cache = HttpContext.Current.Cache
End Sub

''' <summary>
''' Maximum objects that in-memory can store
''' </summary>
''' <remarks></remarks>
Private Const cMemoryMaxObjects As Int32 = 20

' ** disabilito la cache in memory perchè dà un sacco di errori
assurdi....
Private Shared _enableInMemoryCache As Boolean = True

Public Shared Property EnableInMemoryCache() As Boolean
Get
Return _enableInMemoryCache
End Get
Set(ByVal value As Boolean)
_enableInMemoryCache = value
End Set
End Property


Private Shared _cache As Caching.Cache ' = HttpContext.Current.Cache

Private Shared Function GetDbConnection() As System.Data.IDbConnection
Return
Microsoft.Practices.EnterpriseLibrary.Data.DatabaseFactory.CreateDatabase("Cache").CreateConnection()
End Function

Public Shared Sub AddCache(ByVal key As String, ByVal data As Object,
ByVal absoluteExpiration As Date, ByVal cacheItemPriority As
Caching.CacheItemPriority)

If EnableInMemoryCache Then
If (_cache.Count > cMemoryMaxObjects) Then

AddToDbCache(key, data, absoluteExpiration)
Else

_cache.Add(key, data, Nothing, absoluteExpiration,
Caching.Cache.NoSlidingExpiration, cacheItemPriority, Nothing)

AddToDbCache(key, data, absoluteExpiration)

End If

Else
AddToDbCache(key, data, absoluteExpiration)
End If

End Sub

Private Shared Sub AddToDbCache(ByVal key As String, ByVal data As
Object, ByVal absoluteExpiration As Date)

Dim dal As New DAL.WcfCache.WcfCacheDataContext(GetDbConnection)
Dim newItem As New BusinessEntities.WcfCache.WcfCache

Dim c As BusinessEntities.WcfCache.WcfCache = (From item As
BusinessEntities.WcfCache.WcfCache In dal.WcfCaches _
Where item.Key = key).FirstOrDefault
If c IsNot Nothing Then
dal.WcfCaches.DeleteOnSubmit(c)
dal.SubmitChanges()
End If

Dim serizer As New
System.Runtime.Serialization.Formatters.Binary.BinaryFormatter
Using ms As New System.IO.MemoryStream()
serizer.Serialize(ms, data)
Dim Result(CInt(ms.Length - 1)) As Byte
ms.Position = 0
ms.Read(Result, 0, CInt(ms.Length))

newItem.Value = New Data.Linq.Binary(Result)

End Using

newItem.Key = key
newItem.Expiration = absoluteExpiration

dal.WcfCaches.InsertOnSubmit(newItem)
dal.SubmitChanges()

End Sub

Public Shared Function GetCache(ByVal key As String) As Object

If EnableInMemoryCache Then
If _cache.Item(key) IsNot Nothing Then
Return _cache.Item(key)
End If
End If

Dim dal As New DAL.WcfCache.WcfCacheDataContext(GetDbConnection)

Dim c As BusinessEntities.WcfCache.WcfCache = (From item As
BusinessEntities.WcfCache.WcfCache In dal.WcfCaches _
Where item.Key = key AndAlso item.Expiration >=
Now).FirstOrDefault

If c IsNot Nothing Then
Using ms As New System.IO.MemoryStream(c.Value.ToArray)
Dim serizer As New
System.Runtime.Serialization.Formatters.Binary.BinaryFormatter
GetCache = serizer.Deserialize(ms)
End Using


Else
Return Nothing
End If

End Function



End Class
 
A

Allen Chen [MSFT]

Hi Trapulo,

Quote from Trapulo==================================================

mmm I'm still using the old SQL 2000 and varbinary can contain only 8K. My
cached data can be bigger...
==================================================

If so please use image type.

Quote from Trapulo==================================================
Yes: but items in SQL Server?
==================================================

Since we'll remove the expired records stored in the SQL Server every 7
days I think we don't have to do that bother. Is there any particular
reason that you want to remove them in the Application_End?

Quote from Trapulo==================================================
Imports System.Web

Public Class WcfCache
¡­
==================================================

Thanks for the code. As to the exception when accessing _cache I have
something unclear. Please clarify them first:

1. Do you mean you get the exception:
System.Reflection.TargetParameterCountException - Parameter count mismatch.
When using the code you provided? Or you get another exception?

2. Can the exception be reproduced in this way? (Please create a new
project to test) If it cannot could you tell me how to reproduce it?

aspx.vb:

Imports System.Web.Caching

Partial Public Class _Default
Inherits System.Web.UI.Page

Protected Sub Page_Load(ByVal sender As Object, ByVal e As
System.EventArgs) Handles Me.Load

End Sub

Protected Sub Button1_Click(ByVal sender As Object, ByVal e As
EventArgs) Handles Button1.Click

Dim obj As Object = WcfCache.GetCache("test")

End Sub


End Class


Public Class WcfCache



Private Shared _cache As Caching.Cache = HttpContext.Current.Cache



Public Shared Function GetCache(ByVal key As String) As Object

If _cache.Item(key) IsNot Nothing Then
Return _cache.Item(key)
End If

Return Nothing



End Function



End Class

aspx:
<asp:Button ID="Button1" runat="server" Text="Button" />

3. The class name is WcfCache. Are you using this class in WCF?


Regards,
Allen Chen
Microsoft Online Community Support
 
A

Allen Chen [MSFT]

Hi Trapulo,

Do you have any progress on this issue?

Regards,
Allen Chen
Microsoft Online Community Support
 
T

Trapulo

Allen Chen said:
Quote from Trapulo==================================================
Yes: but items in SQL Server?
==================================================

Since we'll remove the expired records stored in the SQL Server every 7
days I think we don't have to do that bother. Is there any particular
reason that you want to remove them in the Application_End?

to avoid using SQL Agent. So my application doesn't require DBA set-up
request, external settings, and so on... Just this, to have less
requirenments.
Quote from Trapulo==================================================
Imports System.Web

Public Class WcfCache
¡­
==================================================

Thanks for the code. As to the exception when accessing _cache I have
something unclear. Please clarify them first:

1. Do you mean you get the exception:
System.Reflection.TargetParameterCountException - Parameter count
mismatch.
When using the code you provided? Or you get another exception?

Yes: I have that error calling, for example, addCaceh or GetCache...
2. Can the exception be reproduced in this way? (Please create a new
project to test) If it cannot could you tell me how to reproduce it?

mmm
that's strange. I've worked on the project a lot during last days, with
in-memory cache disabled (as the code I pasted in prev post). Now I've
re-enabled it to make tests to reply to your last post, and.. it's working!

3. The class name is WcfCache. Are you using this class in WCF?

The class is compiled in a bll library, that is called by an ASP.NET web
site. An other class in the same BLL is a WCF client: calls the service,
gets data, and uses that WcfCache to avoid remote operations where
possibile.

Thanks!
 
A

Allen Chen [MSFT]

Hi Trapulo,

Thanks for your update.

Quote from Trapulo ==================================================
o avoid using SQL Agent. So my application doesn't require DBA set-up
request, external settings, and so on... Just this, to have less
requirenments.
==================================================

The Application_End event probably is not the proper place to do this task.
It will only be triggered in the following situations:
http://forums.asp.net/p/789499/789499.aspx

If you don't like the SQL Agent idea how about write a Windows Services to
remove the records every 7 days? The following article demonstrates how to
create Windows Services:
http://msdn.microsoft.com/en-us/library/9k985bc9(VS.80).aspx

Quote from Trapulo ==================================================
mmm
that's strange. I've worked on the project a lot during last days, with
in-memory cache disabled (as the code I pasted in prev post). Now I've
re-enabled it to make tests to reply to your last post, and.. it's working!
==================================================

Does it mean that this issue has been resolved?
If you still have questions about this issue please don't hesitate to let
me know. I'll try my best to follow up.

Regards,
Allen Chen
Microsoft Online Support
 
T

Trapulo

Allen Chen said:
Hi Trapulo,

Quote from Trapulo ==================================================
mmm
that's strange. I've worked on the project a lot during last days, with
in-memory cache disabled (as the code I pasted in prev post). Now I've
re-enabled it to make tests to reply to your last post, and.. it's
working!
==================================================

Does it mean that this issue has been resolved?
If you still have questions about this issue please don't hesitate to let
me know. I'll try my best to follow up.


Yes, solved!

thanks!
 
A

Allen Chen [MSFT]

Glad to know that, Trapulo. Have a nice day!

Regards,
Allen Chen
Microsoft Online Community Support
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,744
Messages
2,569,484
Members
44,905
Latest member
Kristy_Poole

Latest Threads

Top