"Automated" updates..

[Kiki]

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)
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...
Do you think that's possible and if so, is this the best solution?
Thank you so much in advance for you time..

 

[Bart Van der Donck]

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,

 

[TC]

We have an Access db in which people change data

You probably mean an MS Jet db. MS Jet is the actual database part of
MS Access. You are probably not using MS Access, at all.

When they do that, our SQL db gets updated too.

SQL is a data access language used by many database products, including
but not limited to MS Jet. AFAIK, there's no database product called
"SQL". Do you mean, MS SQL Server?

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)

Unless you are an experienced database software developer, the most
likely cause of poor query performance, in Jet, SQL Server, or any
other database product, is:
- incorrect table schema design;
- missing primary keys;
- inefficently written SQL statements, or
- various mixtures of the above.

Without more details, it's difficult to say much more. Personally I
think you are going off in the wrong direction, unless you have
explicitly excluded the causes noted above. Otherwise, it's like
saying: "My car doesn't work. How can I chop wood?" - the proposed
solution will not fix the specified problem.

HTH,
TC (MVP MSAccess)
http://tc2.atspace.com

 

[Kiki]

It helps a lot and thank you very much for your detailed response. I've
never seen the "create HTML when DB gets updated" approach, but I will
do some investigation on this. It's only one page that will get
updated; it has about 12 fields that will/could be changed.

Thanks again

 

[Kiki]

Unless you are an experienced database software developer, the most

likely cause of poor query performance, in Jet, SQL Server, or any
other database product, is:
- incorrect table schema design;
- missing primary keys;
- inefficently written SQL statements, or
- various mixtures of the above.

Yep, that's the big problem we're having: the "various mixtures of the
above"!
Our database team is rewriting a lot of tables/sps etc to rectify this
issue but it could take a lot of time, so a temp solution is thought to
be appropriate at this time.
Up until recently, there were a lot of uneccessary db calls. We are
slowly trying to minimize this by, for eg, creating an XML file for
historic data, store it and read that when a user is quering specific
data, instead of going to the database for retrieve it.
The data i'm reffering to now, is the Live data (it can be a stock
price or an exchange rate). The user will have their screen open and if
there's a change in the stock they are watching, the new price should
be displayed on the site.

Thanks

 

[TC]

The data i'm reffering to now, is the Live data (it can be a stock
price or an exchange rate). The user will have their screen open and if
there's a change in the stock they are watching, the new price should
be displayed on the site.

Ok, that takes us to this part of your original post:

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

I think the problem here, is that the server (where the database
lives), would not normally remember which clients (user PCs) were
currently displaying data. When a client requests data from a server,
the server supplies it, but then, effectively, forgets that client
instantly. So I don't think there is any way for the server to "push
out" changes to the "currently connected clients" screens. The whole
concept of "currently connected client", does not exist.

So the client side code would have to actively participate - perhaps by
asking for a data refresh every 'x' seconds (as the other respondent
suggested). But then you have the problem that the client is asking for
data when the data actually has not changed.

Maybe the client side code could use AJAX (asynchronous Javascript)
somehow? AJAX lets client side code get data from the server "behind
the scenes", without having to refresh the whole, current page. But
again, the client pulls the data in - the server does not push it out.

I'm not an expert in any of this (apart from the Access, Jet, and
general database aspects), so I trust that someoine else will jump in,
if anything above is misleading or wrong.

HTH,
TC (MVP MSAccess)
http://tc2.atspace.com