John, When the client requests for next set of data in your implementation, you know which query you are dealing with and so you get data corresponding to that query right? Similarly, when some data is modified(updated/deleted), I know which query got affected. At this point, I send a message back to the server with a request to refresh that particular resultset (which again is a thread). I use server and client here rather than servlets because my initial implementation was in Applets (client) and RMI (Server). I think this is more of an implementation issue and does not really pertain to servlets and so the solution should still work if you have a servlet instantiated like a Singleton which maintains such a table of query strings and vectors as you described below. Ramesh > Ramesh, > You stated > "I know which query in the above hash table was > updated and I run a background thread to requery the db for that > particular query..." > How do you know? We have thought about it but have not come up with a > good solution yet. I'd be intersted in hearing how you catch the > updates especially if you are using an Oracle DB. > Thanks, > -John > > ---------- Forwarded message ---------- > Date: Tue, 28 Sep 1999 21:57:07 -0700 (PDT) > From: Ramesh Vadlapatla <[EMAIL PROTECTED]> > Reply-To: [EMAIL PROTECTED] > To: John Overland <[EMAIL PROTECTED]> > Cc: [EMAIL PROTECTED], [EMAIL PROTECTED] > Subject: Re: Caching resultset at the server side.. > > I implemented a similar solution, which is described below: > > String query1 > Vector V1 > Hashtable H1 > > whenever a new query is invoked, I add it to the Hashtable > on the server: > H1.put(query1, V1); > > As long as the client is selecting, I maintain the table of queries in the > memory and take it out of the memory every 6 hours or so. > > If there is an update, I know which query in the above hash table was > updated and I run a background thread to requery the db for that > particular query and replace the new vector with the old one therefore the > data that the client is looking at is current most of the time. This is an > overhead if there are a lot updates, but it works for my application. > > On the otherhand, since my system involves lot of querying I store all the > queries and the corresponding rowid's of the data in tables, which I > refresh overnight. You can get fancier by seeing which queries are being > most used and keeping those in the db and deleting the rest. > > In the above solution, I don't have a way of refreshing the query each > time a new entry is put into the db as this new entry can correspond to > multiple queries, so I let this happen overnight but ofcourse this is very > much dependent on the application requirements. > > Ramesh > > On Tue, 28 Sep 1999, John Overland wrote: > > > Satish, > > > > They are not reflected immeadiately, only when the VectorPool times out and > > drops the resultset. You can force an update from the query side by passing > > a boolean value to tell it to get a fresh copy (Skips looking in the > > VectorPool). > > > > We have talked with our DBA about Oracle triggering such an update event but > > have not pursued it do to other pressing issues. > > > > -John > > > > -----Original Message----- > > From: Satish Kamatkar [mailto:[EMAIL PROTECTED]] > > Sent: Tuesday, September 28, 1999 5:56 PM > > To: [EMAIL PROTECTED] > > Subject: Re: Caching resultset at the server side.. > > > > > > In the event of the updates to the database, how do those changes get > > reflected in the vector cache? Thanks - Satish > > > > -----Original Message----- > > From: A mailing list for discussion about Sun Microsystem's Java Servlet > > API Technology. [mailto:[EMAIL PROTECTED]]On Behalf Of > > Jaganathan Jeyapaul > > Sent: Tuesday, September 28, 1999 1:28 PM > > To: [EMAIL PROTECTED] > > Subject: Re: Caching resultset at the server side.. > > > > > > Hi John, > > > > Your technique seems good.. I assume that when you talk about the vector > > that stores the resultset, you mean a custom object that wraps the resultset > > and gets stored in a Hashtable as value, with the hashed SQL statement as > > the key. Cool.. How do you activate purging in your servlet ? do you have a > > low priority thread that polls at periodic interval to check for redundant > > information in the hashtable ? > > > > Thanks for sharing the details. > > > > jagan j > > > > > > -----Original Message----- > > From: A mailing list for discussion about Sun Microsystem's Java Servlet > > API Technology. [mailto:[EMAIL PROTECTED]]On Behalf Of John > > Overland > > Sent: Monday, September 27, 1999 9:41 PM > > To: [EMAIL PROTECTED] > > Subject: Re: Caching resultset at the server side.. > > > > > > Jagan, > > > > We had a similar problem and have implemented 3 different pieces to solve > > the problem which so far is working quite well. The 3 pieces are as > > follows: > > > > 1) ConnectionPoolServlet - Gets X number of connections to the DB, 5 to > > start, grows if needed. > > > > 2) VectorTableServlet - Stores resultsets if desired with HASHED SQL > > Statement as the key. > > > > 3) QueryServlet - HASH's the SQL statement and checks to see if it exists in > > VectorTable, if not it gets a connection, performs the query and if flag is > > set stores it into the VectorTable. > > > > The result from any SQL query is a Vector which you can start displaying at > > any point to any point. > > > > We have set a purge flag in the VectorTableServlet to drop the resultset > > after X time (5 minutes) of inacivity on that particular query or to keep it > > permanantly. The end result allows us to do the following. > > > > 1) SQL queries without storing the result (Live DB views). > > 2) SQL queries which store the resultset which provide 5 major benefits. > > a) Incredible (subsecond) responses with multiple users asking for > > the same info. > > b) Ability to traverse the returned data everyway imaginable. > > c) Ability to sort resultset on any column. (Vector Sort) > > d) Ability to sub-search resultset (drill down). (Vector Search) > > e) Create permanent queries which is like on the fly system > > variables. > > > > > > As far as the number of records we have had in the VectorPool at any given > > time the maximum we have seen is around 40,000. The problem we had in > > getting it higher was that each new query had to be different otherwise it > > returned an existing set and add to that the 5 minute timeout, it was tough > > to keep it that high. Our vectors typically have 10-12 (20 char) fields and > > 1-2 (200-2000 text) fields returned. Typical Vector size hovers between 500 > > & 5000. > > > > John Overland > > Nortel Networks > > ____________________________________________________________________________ > > ______________ > > > > > > Hi All, > > > > > > > > I am fetching thousands of results from the database based on user > > query. > > > > But i need to show just few of them to the user and cache the rest at > > the > > > > server side. If the user wants to see more results then the cached > > > resultset > > > > should be queried and served to the user.. > > > > > > > > my question is what is the rightway to cache information (fetched from > > > > database..) at the server side.. > > > > > > > > Could someone me suggestions or pointers to the right direction.. > > > > > > > > I am using JSDK 2.1 / WEBLOGIC / JDBC. > > > > > > > > Thanks in Advance.. > > > > > > > > ================== > > > > Jagan J > > > > Java Consultant > > > > ================== > > > > > > > > > > > > > > > ___________________________________________________________________________ > > To unsubscribe, send email to [EMAIL PROTECTED] and include in the body > > of the message "signoff SERVLET-INTEREST". > > > > Archives: http://archives.java.sun.com/archives/servlet-interest.html > > Resources: http://java.sun.com/products/servlet/external-resources.html > > LISTSERV Help: http://www.lsoft.com/manuals/user/user.html > > > > ___________________________________________________________________________ > > To unsubscribe, send email to [EMAIL PROTECTED] and include in the body > > of the message "signoff SERVLET-INTEREST". > > > > Archives: http://archives.java.sun.com/archives/servlet-interest.html > > Resources: http://java.sun.com/products/servlet/external-resources.html > > LISTSERV Help: http://www.lsoft.com/manuals/user/user.html > > > > ___________________________________________________________________________ > > To unsubscribe, send email to [EMAIL PROTECTED] and include in the body > > of the message "signoff SERVLET-INTEREST". > > > > Archives: http://archives.java.sun.com/archives/servlet-interest.html > > Resources: http://java.sun.com/products/servlet/external-resources.html > > LISTSERV Help: http://www.lsoft.com/manuals/user/user.html > > > > ___________________________________________________________________________ > > To unsubscribe, send email to [EMAIL PROTECTED] and include in the body > > of the message "signoff SERVLET-INTEREST". > > > > Archives: http://archives.java.sun.com/archives/servlet-interest.html > > Resources: http://java.sun.com/products/servlet/external-resources.html > > LISTSERV Help: http://www.lsoft.com/manuals/user/user.html > > > > > > > > > > ___________________________________________________________________________ To unsubscribe, send email to [EMAIL PROTECTED] and include in the body of the message "signoff SERVLET-INTEREST". Archives: http://archives.java.sun.com/archives/servlet-interest.html Resources: http://java.sun.com/products/servlet/external-resources.html LISTSERV Help: http://www.lsoft.com/manuals/user/user.html
