Asp Forum - SqlCacheDependency: recommended poll time? - microsoft.public.dotnet.framework.aspnet.caching

vecozo@online.nospam

10/9/2006 3:13:00 PM

Hi,

Untill we migrate to Sql Server 2000 we will be using SqlCacheDependencies
in combination with Sql 2000. In this respect we should determine a poll
frequency. i cannot find documentation that can help me in determining this
setting.

I am considering a poll frequency of 1000 miliseconds (one second) for user
(login) data. Would this be an appropriate value?

Regards,
Martijn Kaag

______________________________
www.VECOZO.nl

8 Answers

vecozo@online.nospam

10/9/2006 3:18:00 PM

CORRECTION: Untill we migrate to Sql Server 2000*5* ...

offwhite

10/9/2006 5:19:00 PM

You could base it on the average page view time. I doubt users are
moving from page to age in under 1 second. You may find that 3 to 5
seconds may be reasonable.

Also consider what is happening with your data. If you have an object
called UserData which holds various user properties and serializes to
the database upon request, you can build in protection for caching.
What you may find is that nearly every time you access the UserData
object you just read the data and that very rarely a request changes a
property. In that case your poll time could be high because the
database values will not change that often. And when they do, you can
invalidate the cache immediately so the next request is forced to get a
current copy from the database.

If you manage your cached data in this way, you may have a very high
poll time which is simply used to drop older objects from the cache to
clear up space for newer items. And you will be assured that the
values in the cache are current.

Brennan Stehling
http://brennan.offwhite...

vecozo@online.nospam wrote:
> Hi,
>
> Untill we migrate to Sql Server 2000 we will be using SqlCacheDependencies
> in combination with Sql 2000. In this respect we should determine a poll
> frequency. i cannot find documentation that can help me in determining this
> setting.
>
> I am considering a poll frequency of 1000 miliseconds (one second) for user
> (login) data. Would this be an appropriate value?
>
> Regards,
> Martijn Kaag
>
>
>
>
>
>
> ______________________________
> www.VECOZO.nl

vecozo@online.nospam

10/10/2006 8:10:00 AM

Bi Brennan,

Thanks for your response.

Perhaps I should reformulate my question. Because we are dealing with user
login data we would obviously prefer live instead of cached data. My question
should be:

What would be the impact of the following settings [on approx 16 www
servers] on the performance of our database server:

<system.web>
<caching>
<sqlCacheDependency enabled = "true"
pollTime = "1000">
<databases>
<add name = "xxxxx"
connectionStringName = "xxxxx"
pollTime = "1000"/>
</databases>
</sqlCacheDependency>
</caching>
</system.web>

Regards,
Martijn Kaag

______________________________
www.VECOZO.nl

"offwhite" wrote:

> You could base it on the average page view time. I doubt users are
> moving from page to age in under 1 second. You may find that 3 to 5
> seconds may be reasonable.
>
> Also consider what is happening with your data. If you have an object
> called UserData which holds various user properties and serializes to
> the database upon request, you can build in protection for caching.
> What you may find is that nearly every time you access the UserData
> object you just read the data and that very rarely a request changes a
> property. In that case your poll time could be high because the
> database values will not change that often. And when they do, you can
> invalidate the cache immediately so the next request is forced to get a
> current copy from the database.
>
> If you manage your cached data in this way, you may have a very high
> poll time which is simply used to drop older objects from the cache to
> clear up space for newer items. And you will be assured that the
> values in the cache are current.
>
> Brennan Stehling
> http://brennan.offwhite...
>
> vecozo@online.nospam wrote:
> > Hi,
> >
> > Untill we migrate to Sql Server 2000 we will be using SqlCacheDependencies
> > in combination with Sql 2000. In this respect we should determine a poll
> > frequency. i cannot find documentation that can help me in determining this
> > setting.
> >
> > I am considering a poll frequency of 1000 miliseconds (one second) for user
> > (login) data. Would this be an appropriate value?
> >
> > Regards,
> > Martijn Kaag
> >
> >
> >
> >
> >
> >
> > ______________________________
> > www.VECOZO.nl
>
>

offwhite

10/10/2006 7:46:00 PM

I assume the 1000 there is in milliseconds, or 1 second.

That is a low number which will ensure very small cache windows. This
is still extremely helpful because in a very short period your page
requests may hit that same data very often in a short period. Not only
does the caching allow those multiple requests to share the same
results, it also offloads a great deal of load from the database.

If you were not to use a cached (yet temporary) result, you can cause
the database to run many redundant queries which will cause resource
contention and slow down your database. You can speed up your database
queries significantly if you do this sort of caching. Consider that if
5 requests in that 1 second hit the user data which may join across 5
tables, you have just cut it down to a fraction of the load without the
caching.

Alternatively, you can create an HTTP Module which will load the user
data at the very beginning of each request and hold it for all controls
which may need to access that state. You would simply load in the the
beginning of the request and hold it for the duration of the request
scope. But that does not account for other requests which are hitting
the same data.

http://msdn2.microsoft.com/en-us/library/ms2...

Brennan Stehling
http://brennan.offwhite...

vecozo@online.nospam wrote:
> Bi Brennan,
>
> Thanks for your response.
>
> Perhaps I should reformulate my question. Because we are dealing with user
> login data we would obviously prefer live instead of cached data. My question
> should be:
>
> What would be the impact of the following settings [on approx 16 www
> servers] on the performance of our database server:
>
> <system.web>
> <caching>
> <sqlCacheDependency enabled = "true"
> pollTime = "1000">
> <databases>
> <add name = "xxxxx"
> connectionStringName = "xxxxx"
> pollTime = "1000"/>
> </databases>
> </sqlCacheDependency>
> </caching>
> </system.web>
>
> Regards,
> Martijn Kaag
>
>
> ______________________________
> www.VECOZO.nl
>
>
>
> "offwhite" wrote:
>
> > You could base it on the average page view time. I doubt users are
> > moving from page to age in under 1 second. You may find that 3 to 5
> > seconds may be reasonable.
> >
> > Also consider what is happening with your data. If you have an object
> > called UserData which holds various user properties and serializes to
> > the database upon request, you can build in protection for caching.
> > What you may find is that nearly every time you access the UserData
> > object you just read the data and that very rarely a request changes a
> > property. In that case your poll time could be high because the
> > database values will not change that often. And when they do, you can
> > invalidate the cache immediately so the next request is forced to get a
> > current copy from the database.
> >
> > If you manage your cached data in this way, you may have a very high
> > poll time which is simply used to drop older objects from the cache to
> > clear up space for newer items. And you will be assured that the
> > values in the cache are current.
> >
> > Brennan Stehling
> > http://brennan.offwhite...
> >
> > vecozo@online.nospam wrote:
> > > Hi,
> > >
> > > Untill we migrate to Sql Server 2000 we will be using SqlCacheDependencies
> > > in combination with Sql 2000. In this respect we should determine a poll
> > > frequency. i cannot find documentation that can help me in determining this
> > > setting.
> > >
> > > I am considering a poll frequency of 1000 miliseconds (one second) for user
> > > (login) data. Would this be an appropriate value?
> > >
> > > Regards,
> > > Martijn Kaag
> > >
> > >
> > >
> > >
> > >
> > >
> > > ______________________________
> > > www.VECOZO.nl
> >
> >

vecozo@online.nospam

10/13/2006 8:14:00 AM

Hi Brennan,

I do really appreciate your effort. Unfortunately, however, you are not
really helpful :)

This is a very specific question about the impact of the
SqlCacheDependency's polling procedure. Not about general caching issues.

The SqlDepenency's polling frequency has nothing to do with the cache window
(which we set at around five minutes). The polling frequency determines the
time it takes till cached data is invalidated after a change of our userdata
and role tables.

Kind regards,
Martijn Kaag

______________________________
www.VECOZO.nl

"offwhite" wrote:

> I assume the 1000 there is in milliseconds, or 1 second.
>
> That is a low number which will ensure very small cache windows. This
> is still extremely helpful because in a very short period your page
> requests may hit that same data very often in a short period. Not only
> does the caching allow those multiple requests to share the same
> results, it also offloads a great deal of load from the database.
>
> If you were not to use a cached (yet temporary) result, you can cause
> the database to run many redundant queries which will cause resource
> contention and slow down your database. You can speed up your database
> queries significantly if you do this sort of caching. Consider that if
> 5 requests in that 1 second hit the user data which may join across 5
> tables, you have just cut it down to a fraction of the load without the
> caching.
>
> Alternatively, you can create an HTTP Module which will load the user
> data at the very beginning of each request and hold it for all controls
> which may need to access that state. You would simply load in the the
> beginning of the request and hold it for the duration of the request
> scope. But that does not account for other requests which are hitting
> the same data.
>
> http://msdn2.microsoft.com/en-us/library/ms2...
>
> Brennan Stehling
> http://brennan.offwhite...
>
> vecozo@online.nospam wrote:
> > Bi Brennan,
> >
> > Thanks for your response.
> >
> > Perhaps I should reformulate my question. Because we are dealing with user
> > login data we would obviously prefer live instead of cached data. My question
> > should be:
> >
> > What would be the impact of the following settings [on approx 16 www
> > servers] on the performance of our database server:
> >
> > <system.web>
> > <caching>
> > <sqlCacheDependency enabled = "true"
> > pollTime = "1000">
> > <databases>
> > <add name = "xxxxx"
> > connectionStringName = "xxxxx"
> > pollTime = "1000"/>
> > </databases>
> > </sqlCacheDependency>
> > </caching>
> > </system.web>
> >
> > Regards,
> > Martijn Kaag
> >
> >
> > ______________________________
> > www.VECOZO.nl
> >
> >
> >
> > "offwhite" wrote:
> >
> > > You could base it on the average page view time. I doubt users are
> > > moving from page to age in under 1 second. You may find that 3 to 5
> > > seconds may be reasonable.
> > >
> > > Also consider what is happening with your data. If you have an object
> > > called UserData which holds various user properties and serializes to
> > > the database upon request, you can build in protection for caching.
> > > What you may find is that nearly every time you access the UserData
> > > object you just read the data and that very rarely a request changes a
> > > property. In that case your poll time could be high because the
> > > database values will not change that often. And when they do, you can
> > > invalidate the cache immediately so the next request is forced to get a
> > > current copy from the database.
> > >
> > > If you manage your cached data in this way, you may have a very high
> > > poll time which is simply used to drop older objects from the cache to
> > > clear up space for newer items. And you will be assured that the
> > > values in the cache are current.
> > >
> > > Brennan Stehling
> > > http://brennan.offwhite...
> > >
> > > vecozo@online.nospam wrote:
> > > > Hi,
> > > >
> > > > Untill we migrate to Sql Server 2000 we will be using SqlCacheDependencies
> > > > in combination with Sql 2000. In this respect we should determine a poll
> > > > frequency. i cannot find documentation that can help me in determining this
> > > > setting.
> > > >
> > > > I am considering a poll frequency of 1000 miliseconds (one second) for user
> > > > (login) data. Would this be an appropriate value?
> > > >
> > > > Regards,
> > > > Martijn Kaag
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > > ______________________________
> > > > www.VECOZO.nl
> > >
> > >
>
>

lukezhan

10/13/2006 9:26:00 AM

Hello,

Let us image such a scenario, there is a property "enabled user" in the
user login data, if you disable it in database. We will expect the user is
"disabled" on his next request to the server. When set pollTime to 1
second, the worst may be that the user can perform some opreations in this
1 second.

However, think of how long it will take if we perform some operation to
"disable" a user, 10 seconds? Compared this value, 1 second can be ignored
in some unstrict system.

Sincerely,

Luke Zhang

Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default....
ications.

Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 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 or complex
project analysis and dump analysis issues. 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/subscriptions/support/de....
==================================================

This posting is provided "AS IS" with no warranties, and confers no rights.

vecozo@online.nospam

10/13/2006 1:21:00 PM

Hi,

Thanks for your assistance. Its seems that I was unable to formulate our
question properly. We have found an answer ourselves.

The performance impact of the polling meganism behind the SqlCacheDepency
class seems to be very limited. If not mistaken, polling operates by calling
the following stored procedure.

CREATE PROCEDURE dbo.AspNet_SqlCachePollingStoredProcedure AS
SELECT tableName, changeId FROM
dbo.AspNet_SqlCacheTablesForChangeNotification
RETURN 0

Obviously, this is a simple select which returns the number of rows equal to
the number of tables on which you defined cache dependencies. Therefore,
polling every second hardly has any performance penalties.

The price for SqlCacheDepency on SQL Server 2000 is paid by triggers that
fire on every change (insert, delete, update) of the tables on which you
defined cache dependencies.

Hope this helps anyone.

Regards,
Martijn Kaag

______________________________
www.VECOZO.nl

"vecozo@online.nospam" wrote:

> Bi Brennan,
>
> Thanks for your response.
>
> Perhaps I should reformulate my question. Because we are dealing with user
> login data we would obviously prefer live instead of cached data. My question
> should be:
>
> What would be the impact of the following settings [on approx 16 www
> servers] on the performance of our database server:
>
> <system.web>
> <caching>
> <sqlCacheDependency enabled = "true"
> pollTime = "1000">
> <databases>
> <add name = "xxxxx"
> connectionStringName = "xxxxx"
> pollTime = "1000"/>
> </databases>
> </sqlCacheDependency>
> </caching>
> </system.web>
>
> Regards,
> Martijn Kaag
>
>
> ______________________________
> www.VECOZO.nl
>
>
>
> "offwhite" wrote:
>
> > You could base it on the average page view time. I doubt users are
> > moving from page to age in under 1 second. You may find that 3 to 5
> > seconds may be reasonable.
> >
> > Also consider what is happening with your data. If you have an object
> > called UserData which holds various user properties and serializes to
> > the database upon request, you can build in protection for caching.
> > What you may find is that nearly every time you access the UserData
> > object you just read the data and that very rarely a request changes a
> > property. In that case your poll time could be high because the
> > database values will not change that often. And when they do, you can
> > invalidate the cache immediately so the next request is forced to get a
> > current copy from the database.
> >
> > If you manage your cached data in this way, you may have a very high
> > poll time which is simply used to drop older objects from the cache to
> > clear up space for newer items. And you will be assured that the
> > values in the cache are current.
> >
> > Brennan Stehling
> > http://brennan.offwhite...
> >
> > vecozo@online.nospam wrote:
> > > Hi,
> > >
> > > Untill we migrate to Sql Server 2000 we will be using SqlCacheDependencies
> > > in combination with Sql 2000. In this respect we should determine a poll
> > > frequency. i cannot find documentation that can help me in determining this
> > > setting.
> > >
> > > I am considering a poll frequency of 1000 miliseconds (one second) for user
> > > (login) data. Would this be an appropriate value?
> > >
> > > Regards,
> > > Martijn Kaag
> > >
> > >
> > >
> > >
> > >
> > >
> > > ______________________________
> > > www.VECOZO.nl
> >
> >

stcheng

10/16/2006 1:38:00 PM

Hi Martijn,

I think you're right. The ASP.NET's polling just send request to query a
very simple value from table. And the most things are done through those
built-in ASP.NET sql cache specific store procedures and triggers. These SP
and triggers are created when you use the aspnet_regsql to enable SqlCache
on a certain database and database table(for SQL 2000 only).

As for the timespan for polling, IMO, the default setting should be ok and
normally we won't have any particular reason to modify it.

If you have any further questions, please feel free to post here.

Sincerely,

Steven Cheng

Microsoft MSDN Online Support Lead

This posting is provided "AS IS" with no warranties, and confers no rights.

microsoft.public.dotnet.framework.aspnet.caching

SqlCacheDependency: recommended poll time?

vecozo@online.nospam

vecozo@online.nospam

offwhite

vecozo@online.nospam

offwhite

vecozo@online.nospam

lukezhan

vecozo@online.nospam

stcheng

x Login to ForumsZone