Cache Issues

[Fernando Chilvarguer]

Hi,

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

HttpContext.Current.Cache.Insert(
cacheItemKey,
contentDS, //THE DATASET WITH MY DATA
null,
DateTime.Now.AddSeconds(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..

 

[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(string cacheItemKey);

2. Then I created the Method implementing Cache Loading Pattern

public static object GetCacheItem(string cacheItemKey, CacheLoader
anyCacheLoader)
{
HttpContext context = HttpContext.Current;
// acquire local reference of cache item
object cacheItem = context.Cache[cacheItemKey];
// if local reference is null, load the cache
if (cacheItem==null)
{
#region Load from CacheLoader delegate
cacheItem = anyCacheLoader(cacheItemKey);
// trace
context.Trace.Warn(cacheItemKey + " loaded from resource");
#endregion
}
else
{
// trace
context.Trace.Warn(cacheItemKey + " 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=" + ConfigurationSettings.AppSettings["sServer"] +
ETC....
myConn = new SqlConnection(sConnString);
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(sqlstr,myConn);
SqlDataAdapter myDA = new SqlDataAdapter();
myDA.SelectCommand = myCommand;
DataSet contentDS = new DataSet();
myDA.Fill(contentDS, "Content");

#region Insert into Cache with absolute expiration
HttpContext.Current.Cache.Insert(
cacheItemKey,
contentDS,
null,
DateTime.Now.AddSeconds(600),
TimeSpan.Zero);
#endregion
return contentDS;
}

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

public DataSet getGenericContent(string strPageID)
{
DataSet contentDS = (DataSet)CacheLoaders.GetCacheItem(strPageID, new
CacheLoader(CacheLoaders.GetPageContent));
return contentDS;
}

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

THANK YOU for any help.

Fernando

 

[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(current time...)
object cacheItem = context.Cache[cacheItemKey];
Trace.Write(current 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=" +
ConfigurationSettings.AppSettings["sServer"] .....
myConnection= new SqlConnection(sConnString);
try
{
myConnection.Open();
}

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

Any ideas?

Thanks,
Fernando

 

[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(SqlConnection connection = new SqlConnection(<cstring>))
{
// ...
}

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

HTH.

 

[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/default.asp?url=/library/en-us/dnsqlmag03/
html/The_NETConnectionPoolLifeguard.asp

#Pooling in the Microsoft Data Access Components
http://msdn.microsoft.com/library/en-us/dnmdac/html/pooling2.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.open()"
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(SqlConnection myConn = new SqlConnection(sConnString))
{
string sqlstr = "select * from TABLE where MemberID ='" +
strMemberID + "'";
SqlCommand myCommand = new SqlCommand(sqlstr,myConn);
SqlDataReader myReader = myCommand.ExecuteReader();
return myReader;
}
}

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

Once again, many thanks!!!

Fernando

 

[Scott Allen]

Hi Fernando:

Something you could do is use CommiandBehavior.CloseConnection when
you open the data reader. When you close the reader then, the
connection is also closed. i.e.:

reader= myCommand.ExecuteReader(CommandBehavior.CloseConnection);

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!

 

[Steven Cheng[MSFT]]

Hi Fernando,

Thanks for your followup. As Scott has mentioned , if you still need to use
the DataReader, please do remember to close it when finished using it. Also
one means you can consider is always put those code which use the
DataReader in a try{}catch{}finally{} block and put the checking and close
DataReader code in the finally{} block which may ensure the resource be
released incase any exception occurs.
In fact, I prefer using finally{} rather than the using(){} .

Also, as for DataSet, since it's a heavior object and can contains multi
datatables. I think if the data you retrieved is for display only and won't
change frequently , you may consider querying them via DataAdapter and
store in DataSet. Then ,you can put them in the ASP.NET's Application Cache
so that all the seqential query can retrieve data from the application
cache directly which may much improve the performance.

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 for all the info. After a week of fixing my application, it now does
not have connection "leaks" and works pretty efficiently.

I do occasionally notice a page taking a while to load but I'm not going to
get that picky at this moment, I have to get back to "real" work and start
creating more pages. I also noticed that my application open 2 connections
to the database (per performance monitor). I could not figure out why but
again, since the number remain constant at 2, I'm not going to put any more
time at it.

I tried the dataReader approach first, with the
CommandBehavior.CloseConnection parameter. It did not work as consistent as
I expected so I gave in and started to use DataSets instead. Remember, I
already had created my caching infrastructure so 90% of my dataSet are
cached.

So, to close this thread, I have one final question:

1. In a shared environment, such as my internet host provider, where I share
"my" server with at least 50 other web sites and the DB server with at least
another 100 databases, is there any way to protect myself from bad
programmers?
I say that because I'm sure I'm the exception spending such an amount of
time to research and make sure my application does not leave orphan
connections and make good use of the machine resources. I worry that once 1
of these web sites is programmed by someone that does not fully understand
the issues (such as myself at the beginning of this thread) my web site
performance will suffer.

Any thoughts appreciated.

Thank you for all your help, and as I promised before, as soon as I'm
proficient enough, I'll make sure I contribute to this newsgroup with good
answers to other people's questions.

Fernando

 

[Scott Allen]

Hi Fernando:

I don't know of any way you can really preserve the performance of
your site if other applications on the server are being bad. You might
want to check with the host and ask what sort of IIS and server
configurations they use. There are options with IIS 6 to throttle
connections and CPU usage.

Other than that, one has to shell out more money for the dedicated
server solution.

 

[Steven Cheng[MSFT]]

Hi Fernando,

Thanks for your reply and glad that your application is working well now.
As for the protecting your web application from others in a shared
enviroment. As Scott mentioned, we can't completely isolate our own app
from other app's affaction since they all share the same server. But if
you're hosting the app on a w2k3 server with IIS6 , we can make use of the
IIS6's Application Pool setting , in IIS6 each asp.net web application can
run in a separate application pool so as to be isolated from others. This
may helps to protect the application from being affected by some other web
apps. Here is an tech article in msdn which also mentioned this:

#Hosting Multiple ASP.NET Applications
http://msdn.microsoft.com/library/en-us/secmod/html/secmod93.asp?frame=true

Hope 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.)