Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Aug 2006
    Location
    Leeds, UK
    Posts
    104

    Unanswered: Optimal solution for getting timestamp of last entry

    Hi

    I'm trying to find the optimal way of getting the timestamp of the last updated entry in an mssql database. A database is updated only about 5 times a minute, how ever a request for the time of the last entry could be around 1 per second. For this reason i was thinking of having a separate table which has a single row which is updated everytime a new entry is updated in the main table. I would then only need a simple SELECT statement and need very little processing power.

    Is this the best method, or can you think of any others i could use?

    many thanks

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    What's the scope? Database wide or table specific?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    single table, single row? a potential bottleneck, but only 5/minute should be no problem, so yeah, that would be the simplest solution

    but if this is just for your main table, consider an index on the datetime column descending, so that TOP 1 gets your last update easily
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by r937
    but if this is just for your main table, consider an index on the datetime column descending, so that TOP 1 gets your last update easily
    How does SQL Server handle a monotonically decreasing index? Presumably with regular page splits? Like you say low activity will mitigate this somewhat.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    oh dear, i'm in over my head here

    what is monotonically? no music? and how is monotonically different from sequentially? from consecutively?

    and what's a page split?

    sheesh, i should stick to stuff i know
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    SELECT MAX(upd_dt) FROM (
    SELECT MAX(upd_dt) AS upd_dt FROM tbl1 UNION ALL
    SELECT MAX(upd_dt) AS upd_dt FROM tbl2 UNION ALL
    SELECT MAX(upd_dt) AS upd_dt FROM tbl3 UNION ALL
    SELECT MAX(upd_dt) AS upd_dt FROM tbl4 UNION ALL
    SELECT MAX(upd_dt) AS upd_dt FROM tbl5 UNION ALL
    SELECT MAX(upd_dt) AS upd_dt FROM tbl6) AS XXX

    ???????????????????????????????????????????

    You gotta give us more to go on
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Aug 2006
    Location
    Leeds, UK
    Posts
    104
    Hi, its a pretty simple database, there are only two tables, one with all the data, and the other which will hold the single row with the latest time stamp.

    I realise i can SELECT the top1 descending record from the main table, but won't that be more work than simply selecting a single value?

    ...or is selecting top1 more viable than loosing out on 'bottle necking' when selecting a single row..!
    Last edited by mattock; 07-25-07 at 10:30.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    more work? for you or for the server?

    i'll bet if you timed the cpu cycles, it'd be pretty close
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Aug 2006
    Location
    Leeds, UK
    Posts
    104
    Quote Originally Posted by r937
    more work? for you or for the server?

    i'll bet if you timed the cpu cycles, it'd be pretty close
    lol - i don't mind doing the work! - i just want this to be as optimal as possible...

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    How many rows in the table?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by r937
    oh dear, i'm in over my head here
    ....
    sheesh, i should stick to stuff i know
    I don't think so - I think you just sometimes pretend you know less than you do so no one asks you anything remotely resembling a dba question
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Aug 2006
    Location
    Leeds, UK
    Posts
    104
    In the main table, there will be approximately 1000 new rows per day.

    I suppose this leads onto another question, are there any limits other than disk space, eg on the cpu that i should be aware of?
    Last edited by mattock; 07-25-07 at 10:49.

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Sorry I meant in total
    Testimonial:
    pootle flump
    ur codings are working excelent.

  14. #14
    Join Date
    Aug 2006
    Location
    Leeds, UK
    Posts
    104
    well theres zero rows at the moment because im in the middle of building the application and setting up the database. But i am expecting approx 1000 new rows a day - not sure what limits are advisable as to how many rows before i need to do something, eg create a new table or whatever?

  15. #15
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by mattock
    well theres zero rows at the moment because im in the middle of building the application and setting up the database. But i am expecting approx 1000 new rows a day - not sure what limits are advisable as to how many rows before i need to do something, eg create a new table or whatever?
    You won't get close to the sort of volume where you need to start getting canny at 1000 inserts a day.

    Agreed with Rudy - just put the column in your table and then select MAX() of that column to get the last update. Index the column. I think I would index ASC but there you go. Triger to update it.

    BTW - one table - is it normalised?
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •