Cache Issues

[Fernando Chilvarguer]

Hi,

I'm retriving data from a database and storing it on the Cache Object using
the following code:

HttpContext.Cur rent.Cache.Inse rt(
cacheItemKey,
contentDS, //THE DATASET WITH MY DATA
null,
DateTime.Now.Ad dSeconds(600),
TimeSpan.Zero
);

It works fine but I'm having a strange behaviour I cannot find the cause:

I click around the site to test the speed and the usage of the cache (using
TRACE).

I get to different results at random (from my trace):

1 loaded from cache 2.251844
this is TOO LONG since the other times I get:

1 loaded from cache 0.002361
Nothing changes but for some reason, suddenly the operation that takes 0.002
seconds decide to take 2.25 seconds.

In both instances the data is being loaded from the cache, otherwise my
trace would output "loaded from resource".

Any ideas?

Thanks,
Fernando

[Scott Allen]

Is there anything else happening on the server to cause latency?
Perhaps you could show us the code..

--
Scott
http://www.OdeToCode.com/blogs/scott/

On Sat, 23 Oct 2004 21:20:33 -0700, "Fernando Chilvarguer"
<fe******@nospa m-impex.com> wrote:

Hi,

I'm retriving data from a database and storing it on the Cache Object using
the following code:

HttpContext.Cu rrent.Cache.Ins ert(
cacheItemKey ,
contentDS, //THE DATASET WITH MY DATA
null,
DateTime.Now.A ddSeconds(600),
TimeSpan.Zer o
);

It works fine but I'm having a strange behaviour I cannot find the cause:

I click around the site to test the speed and the usage of the cache (using
TRACE).

I get to different results at random (from my trace):

1 loaded from cache 2.251844
this is TOO LONG since the other times I get:

1 loaded from cache 0.002361
Nothing changes but for some reason, suddenly the operation that takes 0.002
seconds decide to take 2.25 seconds.

In both instances the data is being loaded from the cache, otherwise my
trace would output "loaded from resource".

Any ideas?

Thanks,
Fernando

[Steven Cheng[MSFT]]

Hi Fernando,

As for the problem you mentioned, I think generally the cache data's
loading behavior should be the same during the cached object's lifecycle.
Is the problem you mentioned in the same place(in the same page's code
where you load the cache object) or in different place? In addition, would
you provide the detailed code how to trace the time when your application
loading the data from cache?
If you have any other findings, please also feel free to post here. Thanks.
Regards,

Steven Cheng
Microsoft Online Support

Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

[Fernando Chilvarguer]

Hi Steven,

The problem occurs on different pages. All pages are accessing the same
cache functions I created.
The behavior is random. So I test "page 1" and I get the different behavior,
then I test "page 2" and I get the same interesting behavior.
In other words, sometimes the cache takes over 2 seconds to return the data,
sometimes it takes 0.002 seconds.
I made sure I stopped any process in the machine that could influence the
result by sudenlly acting on the background (such as ActiveSync, print
Spooler, etc.).
(WinXP Pro, .Net 1.1 VS 2003)

Here's the code.

1. I created a delegate function:

public delegate object CacheLoader(str ing cacheItemKey);

2. Then I created the Method implementing Cache Loading Pattern

public static object GetCacheItem(st ring cacheItemKey, CacheLoader
anyCacheLoader)
{
HttpContext context = HttpContext.Cur rent;
// acquire local reference of cache item
object cacheItem = context.Cache[cacheItemKey];
// if local reference is null, load the cache
if (cacheItem==nul l)
{
#region Load from CacheLoader delegate
cacheItem = anyCacheLoader( cacheItemKey);
// trace
context.Trace.W arn(cacheItemKe y + " loaded from resource");
#endregion
}
else
{
// trace
context.Trace.W arn(cacheItemKe y + " loaded from cache");
}
// return
return cacheItem;
}

3. This is the method that retrieves the data

public static object GetPageContent( string cacheItemKey)
{
#region Create connection to SQL Server
SqlConnection myConn;
string sConnString = "";
sConnString = "SERVER=" + ConfigurationSe ttings.AppSetti ngs["sServer"] +
ETC....
myConn = new SqlConnection(s ConnString);
try
{
myConn.Open();
}
catch
{
....
}
#endregion
string sqlstr = "select * from XXX where WWW_Status = 1 AND Page_ID=" +
cacheItemKey + " order by Content_OrderBy ";
SqlCommand myCommand = new SqlCommand(sqls tr,myConn);
SqlDataAdapter myDA = new SqlDataAdapter( );
myDA.SelectComm and = myCommand;
DataSet contentDS = new DataSet();
myDA.Fill(conte ntDS, "Content");

#region Insert into Cache with absolute expiration
HttpContext.Cur rent.Cache.Inse rt(
cacheItemKey,
contentDS,
null,
DateTime.Now.Ad dSeconds(600),
TimeSpan.Zero);
#endregion
return contentDS;
}

4. This is how I call everything from the page:

public DataSet getGenericConte nt(string strPageID)
{
DataSet contentDS = (DataSet)CacheL oaders.GetCache Item(strPageID, new
CacheLoader(Cac heLoaders.GetPa geContent));
return contentDS;
}

It all works great with the exception of this weird behavior.

THANK YOU for any help.

Fernando
"Steven Cheng[MSFT]" <v-******@online.m icrosoft.com> wrote in message
news:NY******** ******@cpmsftng xa10.phx.gbl...

Hi Fernando,

As for the problem you mentioned, I think generally the cache data's
loading behavior should be the same during the cached object's lifecycle.
Is the problem you mentioned in the same place(in the same page's code
where you load the cache object) or in different place? In addition,
would
you provide the detailed code how to trace the time when your application
loading the data from cache?
If you have any other findings, please also feel free to post here.
Thanks.
Regards,

Steven Cheng
Microsoft Online Support

Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

[Steven Cheng[MSFT]]

Hi Fernando,

Thanks a lot for your detailed response and the code snippet. I've got the
things you've done. From the code , you're writing trace everytime when you
finished retrieving data from the cache....

Currently I think you can try the following things:
1. Since the loading time we care about is during the following sentense:

object cacheItem = context.Cache[cacheItemKey];

that's the code which retrieve datas from the ASP.NET's cache collection.
So I suggest you put some code to write out the current dateTime string(via
System.DateTime .Now) into trace before and after that line of code so as to
see whether the period of time is about the same. Such as :

if(Cache[key] != null)
{
Trace.Write(cur rent time...)
object cacheItem = context.Cache[cacheItemKey];
Trace.Write(cur rent time..)

}

2. Also, you may add some further tracing code in where it may be in the
execute path at runtime when we retrieve data from trace. Thus, we can
check whether they're always going through the same execute path when we
got different period of time(it load data from cache).

Also, if you have anyother ideas, please feel free to let me know. Thanks.

Regards,

Steven Cheng
Microsoft Online Support

Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

[Fernando Chilvarguer]

Hi Steve,
Thank again for being such a great helper.

Here's the follow-up to your sugestion:

I ended up putting trace stuff everywhere and found the offending statement:

I was opening a connection to the database independent of getting the data
from the cache or from the DB.

I fixed that and now a connection is open ONLY when the data needs to be
loaded from the DB and not from the cache.

Of course, that created another question in my mind:

Why would opening a DB connection be fast sometimes and slow other times?
Shouldn't the time to open a DB connection be consistent? What am I missing?

The statement is:

SqlConnection myConnection;
string sConnString = "SERVER=" +
ConfigurationSe ttings.AppSetti ngs["sServer"] .....
myConnection= new SqlConnection(s ConnString);
try
{
myConnection.Op en();
}

Sometimes is takes 0.002 seconds, sometimes it takes over 2 seconds.

Any ideas?

Thanks,
Fernando

"Steven Cheng[MSFT]" <v-******@online.m icrosoft.com> wrote in message
news:u5******** ******@cpmsftng xa10.phx.gbl...

Hi Fernando,

Thanks a lot for your detailed response and the code snippet. I've got the
things you've done. From the code , you're writing trace everytime when
you
finished retrieving data from the cache....

Currently I think you can try the following things:
1. Since the loading time we care about is during the following sentense:

object cacheItem = context.Cache[cacheItemKey];

that's the code which retrieve datas from the ASP.NET's cache collection.
So I suggest you put some code to write out the current dateTime
string(via
System.DateTime .Now) into trace before and after that line of code so as
to
see whether the period of time is about the same. Such as :

if(Cache[key] != null)
{
Trace.Write(cur rent time...)
object cacheItem = context.Cache[cacheItemKey];
Trace.Write(cur rent time..)

}

2. Also, you may add some further tracing code in where it may be in the
execute path at runtime when we retrieve data from trace. Thus, we can
check whether they're always going through the same execute path when we
got different period of time(it load data from cache).

Also, if you have anyother ideas, please feel free to let me know. Thanks.

Regards,

Steven Cheng
Microsoft Online Support

Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

[Scott Allen]

Hi Fernando:

I'm just throwing this out as an idea until Steve gets back to you.

I can picture why this would happen with connection pooling enabled
(which it is enabled by default). Sometimes the app will ask for a
connection and one is waiting and available in the pool. Other times
the app might ask for a connection and there are no free, open
connections in the pool so the runtime needs to open another
connection with the database server.

Going back up a few posts I noticed the code snippet posted does not
appear to be closing the connection. This has to happen as soon as
possible to keep free connections in the pool. One easy method is to
keep the connection in a using clause:

using(SqlConnec tion connection = new SqlConnection(< cstring>))
{
// ...
}

This ensure the connection is properly disposed even when an exception
occurs.

HTH.

--
Scott
http://www.OdeToCode.com/blogs/scott/

On Wed, 03 Nov 2004 03:32:25 GMT, "Fernando Chilvarguer"
<fe******@nospa mimpex.com> wrote:

Hi Steve,
Thank again for being such a great helper.

Here's the follow-up to your sugestion:

I ended up putting trace stuff everywhere and found the offending statement:

I was opening a connection to the database independent of getting the data
from the cache or from the DB.

I fixed that and now a connection is open ONLY when the data needs to be
loaded from the DB and not from the cache.

Of course, that created another question in my mind:

Why would opening a DB connection be fast sometimes and slow other times?
Shouldn't the time to open a DB connection be consistent? What am I missing?

The statement is:

SqlConnectio n myConnection;
string sConnString = "SERVER=" +
ConfigurationS ettings.AppSett ings["sServer"] .....
myConnection = new SqlConnection(s ConnString);
try
{
myConnection.O pen();
}

Sometimes is takes 0.002 seconds, sometimes it takes over 2 seconds.

Any ideas?

Thanks,
Fernando

[Steven Cheng[MSFT]]

Great ideas , Scott,

Hi Fernando,

I think Scott's explanation on the ConnectionPool make much sense. This is
a default option of the ADO.NET uses which may help reuse the existing
connections. Also, there're many available confige arguments we can set for
adjusting the connection pool(via connection string). And the detailed
mechasim are also different according to the end DBMS or the data access
level, here are some tech articles discussing on this:
#The .NET Connection Pool Lifeguard
http://msdn.microsoft.com/library/de...us/dnsqlmag03/
html/The_NETConnecti onPoolLifeguard .asp

#Pooling in the Microsoft Data Access Components
http://msdn.microsoft.com/library/en...asp?frame=true

Hope also helps. Thanks.

Regards,

Steven Cheng
Microsoft Online Support

Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

[Fernando Chilvarguer]

Steve, Scott,

Thanks!! You were both right.
I used the performance monitor to check my connections and I was leaving
some connections open which I think was the reason for my problem
(ironically, absolutly nothing to do with caching!).

Of course, this opened an even bigger can of worms for me. I'll try to make
a long story short:

1. I always thought that the expensive operation was the "connection.ope n()"
so I would open the connection, do whatever I had to do, use that same
connection many times and just close it at the end, trying to avoid many
"opens" and "closes". Now I realise that's the wrong way and I should open a
connection, use it, and close it as soon as possible. There will be no
performance penalty since there's a managed pool.

2. Coming from regular ASP and ADO, I built the majority of my code around
SQLDataReaders, which now I understand, is a "connected" object. It seems
that I'll have to change all my data access methods to use DataSets insted.
(fyi, I do all my data updates "manually" via SQL)

Question:

The DataSet is a "heavier" object, with a bigger footprint. Will there be
any performace issues with that? Isn't it overkill to use a DataSet just to
use the functionality available on the DataReader? All I need is to retrive
the data and read it once.

the current code (which does not work because the reader is closed before
it's returned):

public SqlDataReader loadMemberById( string strMemberID)
{
using(SqlConnec tion myConn = new SqlConnection(s ConnString))
{
string sqlstr = "select * from TABLE where MemberID ='" +
strMemberID + "'";
SqlCommand myCommand = new SqlCommand(sqls tr,myConn);
SqlDataReader myReader = myCommand.Execu teReader();
return myReader;
}
}

Is there any way to avoid using a DataSet in this scenario?

Once again, many thanks!!!

Fernando

"Steven Cheng[MSFT]" <v-******@online.m icrosoft.com> wrote in message
news:2c******** ******@cpmsftng xa10.phx.gbl...

Great ideas , Scott,

Hi Fernando,

I think Scott's explanation on the ConnectionPool make much sense. This is
a default option of the ADO.NET uses which may help reuse the existing
connections. Also, there're many available confige arguments we can set
for
adjusting the connection pool(via connection string). And the detailed
mechasim are also different according to the end DBMS or the data access
level, here are some tech articles discussing on this:
#The .NET Connection Pool Lifeguard
http://msdn.microsoft.com/library/de...us/dnsqlmag03/
html/The_NETConnecti onPoolLifeguard .asp

#Pooling in the Microsoft Data Access Components
http://msdn.microsoft.com/library/en...asp?frame=true

Hope also helps. Thanks.

Regards,

Steven Cheng
Microsoft Online Support

Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

[Scott Allen]

Hi Fernando:

Something you could do is use CommiandBehavio r.CloseConnecti on when
you open the data reader. When you close the reader then, the
connection is also closed. i.e.:

reader= myCommand.Execu teReader(Comman dBehavior.Close Connection);

and sometime later

reader.Close(); // connection also closed

That would allow you to still return the reader from a function, but
you still need to take great care to close the reader!

--
Scott
http://www.OdeToCode.com/blogs/scott/

On Fri, 05 Nov 2004 18:57:18 GMT, "Fernando Chilvarguer"
<fe******@nospa mimpex.com> wrote:

Steve, Scott,

Thanks!! You were both right.
I used the performance monitor to check my connections and I was leaving
some connections open which I think was the reason for my problem
(ironically, absolutly nothing to do with caching!).

Of course, this opened an even bigger can of worms for me. I'll try to make
a long story short:

1. I always thought that the expensive operation was the "connection.ope n()"
so I would open the connection, do whatever I had to do, use that same
connection many times and just close it at the end, trying to avoid many
"opens" and "closes". Now I realise that's the wrong way and I should open a
connection, use it, and close it as soon as possible. There will be no
performance penalty since there's a managed pool.

2. Coming from regular ASP and ADO, I built the majority of my code around
SQLDataReaders , which now I understand, is a "connected" object. It seems
that I'll have to change all my data access methods to use DataSets insted.
(fyi, I do all my data updates "manually" via SQL)

Question:

The DataSet is a "heavier" object, with a bigger footprint. Will there be
any performace issues with that? Isn't it overkill to use a DataSet just to
use the functionality available on the DataReader? All I need is to retrive
the data and read it once.

the current code (which does not work because the reader is closed before
it's returned):

public SqlDataReader loadMemberById( string strMemberID)
{
using(SqlConnec tion myConn = new SqlConnection(s ConnString))
{
string sqlstr = "select * from TABLE where MemberID ='" +
strMemberID + "'";
SqlCommand myCommand = new SqlCommand(sqls tr,myConn);
SqlDataReader myReader = myCommand.Execu teReader();
return myReader;
}
}

Is there any way to avoid using a DataSet in this scenario?

Once again, many thanks!!!

Fernando

"Steven Cheng[MSFT]" <v-******@online.m icrosoft.com> wrote in message
news:2c******* *******@cpmsftn gxa10.phx.gbl.. .

Great ideas , Scott,

Hi Fernando,

I think Scott's explanation on the ConnectionPool make much sense. This is
a default option of the ADO.NET uses which may help reuse the existing
connections. Also, there're many available confige arguments we can set
for
adjusting the connection pool(via connection string). And the detailed
mechasim are also different according to the end DBMS or the data access
level, here are some tech articles discussing on this:
#The .NET Connection Pool Lifeguard
http://msdn.microsoft.com/library/de...us/dnsqlmag03/
html/The_NETConnecti onPoolLifeguard .asp

#Pooling in the Microsoft Data Access Components
http://msdn.microsoft.com/library/en...asp?frame=true

Hope also helps. Thanks.

Regards,

Steven Cheng
Microsoft Online Support

Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)