Re: "Automated" updates..

Kiki wrote:

Hello all. Probably I should be sending this to a different group so
apologies if this seem out of place.
We have an Access db in which people change data - as they do. When
they do that, our SQL db gets updated too. These data need to be
instantly displayed on the website.
Currently, a SQL DB call is made every few seconds and it's causing
big performance issues (which is why this service is not very popular
with our clients)
What they want to avoid now is for every person that's online to hit
the SQL DB to get the data every time something changes (which is very
often, approx. once or twice/ minute)

I think this is rather a design/strategy issue than a coding one. It
wouldn't be possible in client-side js anyhow.

The most common approach (at least, in my experience) is to generate
new HTML file/s whenever the database updates, and then regenerate
that/those web page/s that is/are affected by the SQL command. Even if
the database gets updated twice a minute, you'ld still gain a lot of
CPU, because your DB's only have to give their RAM to SQL's
update-actions.

The software that updates the HTML will normally require far less
resources from the machine compared to SQL-lookups for every surfer. I
say 'normally', because that obviously depends on the size of each web
page and how many pages are affected by the update-actions. But I think
you would always win here, unless the HTML pages are extremely
large/numerous or unless you would have very few visitors. The nature
of your data (and likely future evolution thereof) should be among the
most important factors when making such decisions. Especially if you're
dealing with larger and/or complex structured data, I would definitely
counsel this approach.

Once the data is stored in DB and the HTML is written, database can
cooldown to 0 because surfers can fetch their content then as ordinary
web pages. Basically, you could save out all SQL queries of your
vistors in this scenario. You would put yourself in a safe position
towards the future too (e.g. if your traffic would mount to 500% or
so).

Can a script be written so when something gets updated, parallel to the
SQL DB call, a file with the new data will be created on the servers
from which the front-end will be updated? Ie, the data from the Access
db will be written onto a file, then use a javascript function (timeout
15 seconds or something) to check if the file has been updated, and if
it has, display the data...

One workaround could be to link only to unique pages by adding a random
query string after the actual URL, like:

<A HREF="page.htm?KMKE65d2ZSDEELH56h">page</A>

If the content is that "seconds"-sensible, I would recommend this for
browser unbuffering too.

Javascript could automatically refresh the page every 15 seconds with a
new query string, so the surfer gets the most recent data at any time.
If you want to check whether the database was actually updated, you
would need to go to the database again. You could also check if the
creation/change date of the HTML-file has been altered the last 15
seconds, but then you're interacting to the underlying OS and that
would require CPU again.

Hope this helps,

--
Bart

.

Relevant Pages

  • Re: Access vs SQL
    ... > to SQL. ... Are you using the database primarily as a storage place, ... If you have many updates then you need to constantly compact it. ... No additional cost to your clients. ...
    (microsoft.public.dotnet.languages.vb)
  • Re: SQL Script failing when using IF BEGIN END
    ... I am preparing the SQL updates for an application so that the current ... We start with a default database and apply the latest ... all of the scripts would run as a really long ...
    (microsoft.public.sqlserver.tools)
  • ASP.net SQL Insert Problem
    ... I'm trying to write a simple asp.net page which updates some data in a SQL ... //assign a parameter to our adapter command ... My problem is that when I press submit the database doesn't update. ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: SQL Script failing when using IF BEGIN END
    ... The connection to SQL Server will be broken by the RAISERROR. ... I am preparing the SQL updates for an application so that the current ... We start with a default database and apply the latest ... all of the scripts would run as a really ...
    (microsoft.public.sqlserver.tools)
  • Re: SQL Insertion problem
    ... In this case SQL Express. ... for adding links easaly to the database I have on my homepage. ... HTML code I want but when I use a FormView or DetailsView I get the HTML ...
    (microsoft.public.dotnet.framework.aspnet)
Loading