AJAX, XML and Caching(?)

[Mariano]

The title is really reductive, i'll try to explain it better. Then I
use JSP to create dinamically an XML document, something like an SQL
query, and printing of DB result in an XML form. I have a JS too, that
realize XMLHttpRequest(), and information are read from that XML
document (JSP file). There are many JS function that could use the
same XML document, so everytime JSP file are called, SQL query
executed and XML document used, it can be usefull for DB load cause
SQL query is really simple, and all results filtering are realized in
Javascript with XPath. Now, the question is:
what can i do if I wanna have a DB load lighter than before???. For
example: I call a Javascript function that use the users.jsp XML
document, so JSP will create XML and JS will do some operations on
that information, now, if there is a second Javascript function, that
will use the same user.jsp XML document it will force JSP file to run
a new query on DB, but result of query (and relative XML document)
will be the same than before. What can i do to avoid the execution of
SQL query when it will return the same result of the last query

This is an example of my JSP file/XML Document.
<%@ page contentType="text/xml" pageEncoding="windows-1252"%>
<%@ page language="java" import="java.sql.*"%>
<%
Connection dbconn = null;
Class.forName("com.mysql.jdbc.Driver");
dbconn = DriverManager.getConnection("", "", "");
Statement stmt = dbconn.createStatement();
String query = "SELECT ...";
query += "FROM ...";
ResultSet rs = stmt.executeQuery(query);
%>
<radice>
<%
while (rs.next()) {
out.println("<tag>"+rs.getString("data")+"</tag>");
}
%>
</radice>.

Hope, that I've correctly explained my problem.

 

[Bart Van der Donck]

The title is really reductive, i'll try to explain it better. Then I
use JSP to create dinamically an XML document, something like an SQL
query, and printing of DB result in an XML form. I have a JS too, that
realize XMLHttpRequest(), and information are read from that XML
document (JSP file). There are many JS function that  could use the
same XML document, so everytime JSP file are called, SQL query
executed and XML document used, it can be usefull for DB load cause
SQL query is really simple, and all results filtering are realized in
Javascript with XPath. Now, the question is:
what can i do if I wanna have a DB load lighter than before???. For
example: I call a Javascript function that use the users.jsp XML
document, so JSP will create XML and JS will do some operations on
that information, now, if there is a second Javascript function, that
will use the same user.jsp XML document it will force JSP file to run
a new query on DB, but result of query (and relative XML document)
will be the same than before. What can i do to avoid the execution of
SQL query when it will return the same result of the last query

A classical design question.

- Create the XML file and make it writable.
- Add a new task in Cron Manager (or Task Manager, or whatever its
name); perform the query once a day [*] and give the XML-file the
updated content from the database.
- SQL will have much less load because the XML-file is now requested
as read-only file by the applications, and must not be generated from
DB every time.

[*] Or once an hour, or every 5 minutes, once a week, etc. All depends
on how "realtime-sensitive" your data is.

Hope this helps,

 

[Mariano]

Hope this helps,

Sure, but I can't know how frequently user(s) will make DB changed,
depends on how many users are online. So even if I will refresh XML
document every one second i could obtain old data, so I'll need a
different approach.

 

[Thomas 'PointedEars' Lahn]

Please *think* before you post and quote the minimum to *retain the context*
of what you are referring to. For example, isn't it obvious to you that
nobody who follows this thread would care about the "Hope this helps" line
and Bart's signature, but instead the suggestion that Bart made in reply to
your question? :-/

http://www.jibbering.com/faq/faq_notes/clj_posts.html#ps1Post

Sure, but I can't know how frequently user(s) will make DB changed,
depends on how many users are online. So even if I will refresh XML
document every one second i could obtain old data,

No, you could not. http://www.mnot.net/cache_docs/

so I'll need a different approach.

Your original question was:

what can i do if I wanna have a DB load lighter than before???.

And the answer is simple: you need a *server-side indicator* about whether
or not the database (or database table) was changed in the meantime. Only
if it was changed you make a new query, regardless how many requests are
coming in. Caching the query in an XML file server-side (I suppose that is
what Bart suggested; again, you have not quoted anything relevant) would be
a good idea. JSON may even be better as it would probably have less
overhead than XML.


HTH

PointedEars

 

[Bart Van der Donck]

Mariano wrote:

And the answer is simple: you need a *server-side indicator* about whether
or not the database (or database table) was changed in the meantime.  Only
if it was changed you make a new query, regardless how many requests are
coming in.  Caching the query in an XML file server-side (I suppose thatis
what Bart suggested; again, you have not quoted anything relevant) would be
a good idea.

In this case I think it's best to update the XML-file only when the
database is actually modified, e.g. in same program just after the SQL-
command(s). The read-actions can then still use a static XML-file
rather than going to the database all the time. This approach should
result in a considerable speed improvement especially on heavy-duty
systems.

(OP -> Watch out for possible read/write locks of the Operating
System)

 

[Thomas 'PointedEars' Lahn]

And the answer is simple: you need a *server-side indicator* about whether
or not the database (or database table) was changed in the meantime. Only
if it was changed you make a new query, regardless how many requests are
coming in. Caching the query in an XML file server-side (I suppose that is
what Bart suggested; again, you have not quoted anything relevant) would be
a good idea.

In this case I think it's best to update the XML-file only when the
database is actually modified, e.g. in same program just after the SQL-
command(s). The read-actions can then still use a static XML-file
rather than going to the database all the time. This approach should
result in a considerable speed improvement especially on heavy-duty
systems. [...]

Exactly my idea


PointedEars

 

[Dr J R Stockton]

In comp.lang.javascript message <[email protected]>, Fri,

Please *think* before you post and quote the minimum to *retain the context*
of what you are referring to.

That is why attribution lines should contain more than just a name or
nickname. Think through the advice you so liberally give, and, when it
makes sense, follow it thoroughly.