Creating fixed-length tables

Question

Creating fixed-length tables

Viewing 4 posts - 16 through 18 (of 18 total)

You must be logged in to reply to this topic. Login to reply

Sergiy SSC Guru Points: 110208 More actions · Answer 1

No, Jeff, I'm getting on another kind of error.

Your solution will work only in "Single-point real time event recording system".

Only such kind of system could guarantee that Identity order in the table correlates with the actual order of events recorded in this table.

If there is human input, or new records arrived by some kind of messaging system (say, in XML files) then due to any delay in data transmission (e.g., one of data channels was off for some time) the later event may appear in the table before the earlier one.

And your method will keep 50 latest inserted, which not always must be latest happened events.

I would not consider rely on identity for such kind of tasks at all.

_____________
Code for TallyGenerator

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 2

Serqiy and Barry,

Heh... I finally drank enough coffee and now I get it... and I agree. Serqiy is absolutely correct.

Actually, my fault... All I saw was "the latest 50 rows" and that's pretty much open to interpretation until you see WHAT Barry is trying to use it for... a "Digital Dashboard". Depending on his requirements, neither "last 50 inserted" nor "latest 50 by date" may accomplish what he's looking for. It may be that he needs to preload the table with "settings to monitor" and, using updates, apply each new row by date/time to the setting it represents. If an unexpected row appears, it may represent a setting missed in the original requirements and, again depending on requirements, automatically be added to the table or, possibly, be added to a "do you want to add this setting?" table.

To summarize, because of the "Digital Dashboard" requirement, this should probably be a "merge" or "upsert" instead of simple deletions of all but the "last" 50.

Thanks for the feedback, Serqiy...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Barry Rein SSC Rookie Points: 31 More actions · Answer 3

Hi Everyone,

Thank you all very much for taking the time to answer my query.

I should have been clearer in my initial posting. I want to keep the *most recent* 50 entries. My dashboard will display moving graphs, and as a new (chronological) entry arrive, the oldest one will drop off.

Also, there may be dozens of tables running in parallel, which is why I'm concerned about overhead.

I wish I could give you a number of new rows per second, but we're not that far along yet. Let's assume that a 'simpler' method (i.e. low overhead) method of dropping the last row would be more desirable than a 'complex' method.

Thanks again,

Barry

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 4

Then you need to use something like what DC Clark posted based on a date column...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)