"Automated" updates..

Discussion in 'Javascript' started by Kiki, Jun 5, 2006.

  1. Kiki

    Kiki Guest

    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..
     
    Kiki, Jun 5, 2006
    #1
    1. Advertising

  2. 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
     
    Bart Van der Donck, Jun 5, 2006
    #2
    1. Advertising

  3. Kiki

    TC Guest

    Kiki wrote:

    > 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
     
    TC, Jun 5, 2006
    #3
  4. Kiki

    Kiki Guest

    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, Jun 5, 2006
    #4
  5. Kiki

    Kiki Guest

    > 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
     
    Kiki, Jun 5, 2006
    #5
  6. Kiki

    TC Guest

    Kiki wrote:

    > 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
     
    TC, Jun 5, 2006
    #6
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Lee Harris
    Replies:
    0
    Views:
    395
    Lee Harris
    Jul 8, 2003
  2. khawar
    Replies:
    2
    Views:
    463
    Tibby
    Jul 28, 2003
  3. Guy Incognito

    Automated asp.net processes

    Guy Incognito, Aug 25, 2003, in forum: ASP .Net
    Replies:
    2
    Views:
    545
    MS News \(MS ILM\)
    Aug 25, 2003
  4. Jeff
    Replies:
    1
    Views:
    373
    Tapio Kulmala
    Dec 2, 2005
  5. Replies:
    1
    Views:
    521
    S. Justin Gengo [MCP]
    Feb 4, 2006
Loading...

Share This Page