SQLServerCentral.com / Discuss Content Posted by Dinesh Priyankara / Article Discussions / Article Discussions by Author / Optimize Your Applications / Latest PostsInstantForum.NET v2.9.0SQLServerCentral.comhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 02 Aug 2015 13:04:31 GMT20RE: Optimize Your Applicationshttp://www.sqlservercentral.com/Forums/Topic99212-108-1.aspx<P>Hi Malcolm,</P><P>Yes, you can use Cache instead of Session/Application. All store your live reference but Session with StateServer and SQL Server. Have a look on this <A href="http://west-wind.com/weblog/posts/1214.aspx">http://west-wind.com/weblog/posts/1214.aspx</A>. This speaks about the way of storing objects by ASP.net with Cache and Session.</P><P>Dinesh</P>Thu, 10 Feb 2005 20:48:00 GMTDinesh PriyankaraRE: Optimize Your Applicationshttp://www.sqlservercentral.com/Forums/Topic99212-108-1.aspx<P>Good article...It's nice to see some C# code for a change. this works well for small amounts of data (like lookup lists), as storing to much data in memory can seriously degrade performance.</P><P>When dealing with larger recordsets it's a good idea to cache to disk instead of memory. The easiest way to do this in C# is using disconnected recordsets. Basically, the data is serialized to disk as a XML recordset.</P><P>Of course, dealing with updates to data is an added layer of complexity, but this is a problem with any caching technique. There are tons of solutions to this problem, though...they just take time to develop.</P><P>cl</P>Thu, 10 Feb 2005 16:08:00 GMTCalvin LawsonRE: Optimize Your Applicationshttp://www.sqlservercentral.com/Forums/Topic99212-108-1.aspx<P>Why aren't you using System.Web.Caching.Cache? That way you can control the latency.</P>Thu, 10 Feb 2005 03:42:00 GMTMalcolm MatthewsRE: Optimize Your Applicationshttp://www.sqlservercentral.com/Forums/Topic99212-108-1.aspx<P><FONT face=Tahoma><img src='images/emotions/smile.gif' height='20' width='20' border='0' title='Smile' align='absmiddle'><img src='images/emotions/smile.gif' height='20' width='20' border='0' title='Smile' align='absmiddle'></FONT></P><P><FONT face=Tahoma>If I am not mistaken, the url </FONT><A href="http://www.devx.com/asp/Article/16802/0/page/2"><FONT face=Tahoma>http://www.devx.com/asp/Article/16802/0/page/2</FONT></A><FONT face=Tahoma> speaks about ASP and <?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-com:office:smarttags" /><st1:City><st1:place>ADO</st1:place></st1:City> (vb 6.0 and com objects) that is not related to this topic.</FONT></P><P><FONT face=Tahoma>Anyway, it is better to use locking (Application.lock() and Application.UnLock()) when application variables are accessed. If you use session objects, of course you do not need to worry about it.</FONT></P>Wed, 11 Feb 2004 22:54:00 GMTDinesh PriyankaraRE: Optimize Your Applicationshttp://www.sqlservercentral.com/Forums/Topic99212-108-1.aspx<P>Ok I am not going to ding you on concept as I have tried this before. However here is a good starting article for the reasons why this is bad to do.</P><P><A href="http://www.devx.com/asp/Article/16802/0/page/2">http://www.devx.com/asp/Article/16802/0/page/2</A></P>Wed, 11 Feb 2004 11:00:00 GMTAntares686RE: Optimize Your Applicationshttp://www.sqlservercentral.com/Forums/Topic99212-108-1.aspx<P class=MsoNormal style="MARGIN: 0in 0in 0pt"><FONT face=Tahoma><img src='images/emotions/smile.gif' height='20' width='20' border='0' title='Smile' align='absmiddle'><img src='images/emotions/smile.gif' height='20' width='20' border='0' title='Smile' align='absmiddle'>In fact, what I wanted to show is how to cache most-frequent and static (considerably) data. If data is modified often (like invoice table), this is not a solution. It is crystal clear. But every database has some data that is considerably static. Then definitely this scenario can be applied. Remember there are two options: application and session. If data differs from user to user, session type must be used. Otherwise application type can be used. And do not forget, all data saves in server not in client side.</FONT></P><P class=MsoNormal style="MARGIN: 0in 0in 0pt"><?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p><FONT face=Tahoma> </FONT></o:p></P><P class=MsoNormal style="MARGIN: 0in 0in 0pt"><FONT face=Tahoma><img src='images/emotions/shocked.gif' height='20' width='20' border='0' title='Shocked' align='absmiddle'>I do not think that it is advisable to make tables auditable by adding columns like timestamp. <img src='images/emotions/cool.gif' height='20' width='20' border='0' title='Cool' align='absmiddle'>But there is a workaround. Let’s say you have application variable that stores employee data. So, all users will receive data from cache. If data entry part of employee is handled through one of the web pages in same application, you can update your employee application variable after data is inserted, modified, and deleted. Then there won’t be conflict. If this method is not preferred, you can use a dependency file as fhanlon stated.</FONT></P><P class=MsoNormal style="MARGIN: 0in 0in 0pt"><o:p><FONT face=Tahoma> </FONT></o:p></P><P class=MsoNormal style="MARGIN: 0in 0in 0pt"><FONT face=Tahoma>I have applied this scenario in my most application and I can say that there was an improvement of performance. So, do it and see.</FONT></P>Wed, 11 Feb 2004 05:15:00 GMTDinesh PriyankaraRE: Optimize Your Applicationshttp://www.sqlservercentral.com/Forums/Topic99212-108-1.aspx<P>A thought - you could do caching by a "last updated" table that keeps track of the table and the last time of modification. That table's timestamp column gets updated by a Insert/Delete/Update Trigger on the table in question.</P><P>Next question - if its a bunch of rows, is there a good way to say "refresh this datatable" instad of dt.clear(); da.fill(dt);</P><P>I suppose you could keep an "audit style" table (timestamp deleted/added/modified, recordno, oldrecord), but that's getting a little complicated compared to the above...</P>Tue, 10 Feb 2004 16:58:00 GMTthormjRE: Optimize Your Applicationshttp://www.sqlservercentral.com/Forums/Topic99212-108-1.aspx<P>With respect to how to handle database changes, some method of cache refresh has obviously got to be designed. What this is might depend on the volatility of the data. I would assume if the data is very volatile that no caching would be used but assuming that the data is somewhat static and caching is of benefit the method of refresh could range from a button on the screen where the user can refresh the data themselves to some method dependant on external events (such as time - refreshed every 20 minutes, or after a certain period of inactivity; or even refreshed based on some other event (for example, the contents of some file changing) To express the cache's dependency on a file, use the <CODE><FONT color=#003366>System.Web.Caching.CacheDependency</FONT></CODE> class. </P><P>eg. <FONT color=#003366> Dim depends As New _ System.Web.Caching.CacheDependency _ (Server.MapPath("Header.txt")) <FONT color=#000000>Application["Employees"]</FONT>.Insert("News", ds, depends) Else dataset = CType(<FONT color=#000000>Application["Employees"]</FONT>, DataSet) End If</FONT></P><P><FONT color=#003366></FONT>I don't think that deciding how to refresh cache is trivial nor is the decision to even use caching trivial. Like most things it depends. Overall Dinesh put forward some good thoughts. I look forward to the next article.</P>Tue, 10 Feb 2004 08:29:00 GMTfhanlonRE: Optimize Your Applicationshttp://www.sqlservercentral.com/Forums/Topic99212-108-1.aspx<P>In the case of some of one of our CMS's there is always some database traffic but the initial traffic is to determine if the object in the database is more up to date than the one in the web server cache or file store.</P><P>If the component in the database is more up to date than the one in the cache/filestore then the database component replaces the cache component.</P><P>Keeping stuff up to date on CMS sites is always seems to be a bit of a problem because you have to contend with the proxy server as well as the black magic that goes into the CMS caching.</P><P>As far as bog standard ASP/HTML sites are concerned I have had some success with sp_makewebtask and sp_runwebtask but if the worry is the load on the database server then sp_makewebtask/sp_runwebtask are probably not the best procedures to utilise.</P><P>You cannot eliminate database traffic altogether. The questions that I have to ask are</P><UL><LI>What is the traffic for rendering a dynamic component?</LI><LI>How long does it take a process to build a static component?</LI><LI>What is the overhead in querying the timestamp on the static component?</LI></UL><P>It may turn out that the traffic involved in rendering a dynamic component is lower than the processing involved in working out if a static component is out of date. In this case you would be daft not to use a dynamic component.</P><P>We tend to use the static component approach for objects that would normally require a larger processing load, but are themselves, mainly unchanging.</P>Tue, 10 Feb 2004 03:33:00 GMTDavid.PooleRE: Optimize Your Applicationshttp://www.sqlservercentral.com/Forums/Topic99212-108-1.aspx<P>My concern, like the other posters, would be how you handle database changes.</P><P>Performance gains are clearly evident via caching, but with a system with many users - certainly in our environment - the lookups we would most likely wish to cache are from the largest and most volatile of our tables.</P><P>I am fairly new to .net, and would be interested to hear if anyone has an elegant solution that would allow such caching but detect refreshes also.</P><P> </P>Tue, 10 Feb 2004 03:04:00 GMTrosscRE: Optimize Your Applicationshttp://www.sqlservercentral.com/Forums/Topic99212-108-1.aspx<P>We use a variety of content management systems and the approach that they take is to cache the results of a query in their own internal cache.</P><P>Another approach that we use where site performance is at a premium is to have a nightly process that produces hard-coded html for stuff like drop down boxes, left hand navigation etc.</P><P>In some cases the update has to give the illussion of being dynamic so we detect the file creation time of the html component and if this is earlier than the last update time in the database we regenerate the html component file.</P><P>I am not a big fan of application or session variables (session variables in particular) because in IIS they rely on the user browser allowing a cookie. </P><P>In the case of session variables they add a constraint as to the number of simultaneous users you can have on the site at once.</P><P>Still, its horses for courses and 10 points if it works and 0.1 point if it works elegantly. If you're presenting to a marketing type, several million points if it is the correct shade of pink.</P>Tue, 10 Feb 2004 02:05:00 GMTDavid.PooleRE: Optimize Your Applicationshttp://www.sqlservercentral.com/Forums/Topic99212-108-1.aspxNot trying to be a wet blanket, but isn't one of the tenants of the relational database to eliminate redundancy?Who (what) reloads the application variable when the employees table is altered?What about when there are multiple websites that draw data from a single source?Does the database really need to know anything about the interfaces that connect to it?In the cases of storing result sets in session variables things become even harder to manage, as there are likely to be many users that can access data from a shared table, ie a list of preferred locations. Any number of session variables may need to be updated when the base data is modified.Just my 2 cents...Mon, 09 Feb 2004 22:09:00 GMTvirasolOptimize Your Applicationshttp://www.sqlservercentral.com/Forums/Topic99212-108-1.aspxComments posted to this topic are about the content posted at <A HREF=http://www.sqlservercentral.com/columnists/dPriyankara/optimizeyourapplications.asp>http://www.sqlservercentral.com/columnists/dPrFri, 06 Feb 2004 22:10:00 GMTDinesh Priyankara