Optimize Your Applications
-
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/dPr
-
Not 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...
-
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.
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.
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.
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.
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.
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.
-
My concern, like the other posters, would be how you handle database changes.
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.
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.
-
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.
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.
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.
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.
You cannot eliminate database traffic altogether. The questions that I have to ask are
- What is the traffic for rendering a dynamic component?
- How long does it take a process to build a static component?
- What is the overhead in querying the timestamp on the static component?
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.
We tend to use the static component approach for objects that would normally require a larger processing load, but are themselves, mainly unchanging.
-
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
System.Web.Caching.CacheDependencyclass.eg. Dim depends As New _
System.Web.Caching.CacheDependency _
(Server.MapPath("Header.txt"))
Application["Employees"].Insert("News", ds, depends)
Else
dataset = CType(Application["Employees"], DataSet)
End If
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.
Francis
-
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.
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);
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...
-
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.
I do not think that it is advisable to make tables auditable by adding columns like timestamp. 
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.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.
-
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.
-
If I am not mistaken, the url http://www.devx.com/asp/Article/16802/0/page/2 speaks about ASP and ADO (vb 6.0 and com objects) that is not related to this topic.
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.
-
Why aren't you using System.Web.Caching.Cache? That way you can control the latency.
-
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.
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.
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.
cl
Signature is NULL
-
Hi Malcolm,
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 http://west-wind.com/weblog/posts/1214.aspx. This speaks about the way of storing objects by ASP.net with Cache and Session.
Dinesh
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply