Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Optimize Your Applications Expand / Collapse
Author
Message
Posted Friday, February 6, 2004 10:10 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, August 29, 2014 9:21 PM
Points: 175, Visits: 107
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/dPr
Post #99212
Posted Monday, February 9, 2004 10:09 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, October 21, 2004 9:17 PM
Points: 312, Visits: 1
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...



Post #99419
Posted Tuesday, February 10, 2004 2:05 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 10:32 AM
Points: 2,905, Visits: 1,825

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.



LinkedIn Profile
Newbie on www.simple-talk.com
Post #99433
Posted Tuesday, February 10, 2004 3:04 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, March 24, 2014 7:49 AM
Points: 181, Visits: 119

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.

 




Post #99446
Posted Tuesday, February 10, 2004 3:33 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 10:32 AM
Points: 2,905, Visits: 1,825

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.



LinkedIn Profile
Newbie on www.simple-talk.com
Post #99451
Posted Tuesday, February 10, 2004 8:29 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, September 5, 2014 9:04 AM
Points: 2,184, Visits: 1,976

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.CacheDependency class.  

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
Post #99511
Posted Tuesday, February 10, 2004 4:58 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, September 6, 2005 10:53 AM
Points: 107, Visits: 1

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




Post #99672
Posted Wednesday, February 11, 2004 5:15 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, August 29, 2014 9:21 PM
Points: 175, Visits: 107

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.

Post #99769
Posted Wednesday, February 11, 2004 11:00 AM
SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: Moderators
Last Login: Wednesday, September 17, 2014 7:25 AM
Points: 8,369, Visits: 740

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.

http://www.devx.com/asp/Article/16802/0/page/2




Post #99877
Posted Wednesday, February 11, 2004 10:54 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, August 29, 2014 9:21 PM
Points: 175, Visits: 107

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.

Post #99947
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse