Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

On a SQL Server 2008 R2 DB, I'm running an aggregation query every hour or so, on data that's being constantly inserted into a table, and what I'd like to do is pull all the "new" records that were inserted, since the last time that query ran. An identity column can generally do this, since you can assume it's monotonically increasing, but according to this post (http://stackoverflow.com/questions/2828936/does-sql-server-guarantee-sequential-inserting-of-an-identity-column), you're not necessarily guaranteed that when you do the pull, that you've pulled every record before the record with the greatest id.

Question is, how can I do this? A timestamp with default value seems reasonable, but also seems to fail in my mind for similar reasons. Why? Well if the default value of a time column is [CurrentTime], then could it not happen that with parallel inserts, insert #1 has a greater timestamp than insert #2, if insert #1 is slightly delayed? I guess it really depends on when the time is pulled vs when the row is inserted: something which I have no insight into.

The only solution I can fathom that will avoid this issue is if I pull all records from [LastTimeJobRan] - [SomeConstantOffset] up to [CurrentTime] - [SomeConstantOffset]. In other words, assume I run the job at 1:05, I would then pull all records from 12:00 - 1:00, hoping that 5 minutes would be enough of a window to avoid any "stalls" from inserting (obviously a few seconds would suffice here methinks). From a high level, I'm basically staggering/delaying processing of the latest records that are currently being inserted.

The other solution I'm thinking of involves a combination of storing the time of the last run, and marking records as being "processed". The last time run (offset by a bit as well perhaps) is just an optimization to avoid pulling a lot of old rows that have already been marked.

Am I overthinking this problem? Are there guarantees that the DB makes that obviate this entire line of thought?

I'm sure this is a common enough problem, but I couldn't find much relating to this besides the above SO post. Perhaps I'm using the wrong keywords.

share|improve this question
add comment

3 Answers

This is a classic senario that we experienced with out datawarehouse. We already had timestamp column, but still to process new records from the last time the query ran was difficult just like you are currently facing.

What we did was - we added 3 columns - LATEST, END_OF_DAY and PROCESSED columns to our main table where the data was loaded for further processing.

LATEST = All the data that gets loaded is defaulted to 1. Meaning that the data is latest.

PROCESSED = Will be set to 1 for the data that is already read and processed by our ETL job. The new data will be having a default of 0.

END_OF_DAY = This is another flag that is useful just in-case if you want to process all the records for the entire day. Just our requirement. Your case might be different.

Note: Above is just a simple example of how a sort of similar issue like you mentioned was addresses. Obviously there were many factors and business requirements that lead us to introduce such extra fields.

Also, you can use Calendar Table and Date/Time Functions to supplement what I mentioned above.

On the top of above, I would suggest to look into Data Loading Performance Guide to get benefit when the data is loaded and the Isolation level (especially RCSI) of the database as if the data is being continuously loaded then there would be blocking issues. Test it, balance it and then implement it !!

share|improve this answer
add comment

In Oracle RDBMS, Materialized view with fast resfresh can handle your requirments. If you are not on Oracle, you can simulate the behavior: - Fire a trigger on each insert on the table to insert into a log table - Use the log table to update you agregate - delete from the log table when finished the rows which have been taken in the previous step

share|improve this answer
    
Running on SQL Server 2008 R2. Updated, thanks. –  DavidN Oct 10 '13 at 14:40
1  
Seems pretty expensive, both from a processing perspective (firing a trigger on each and every insert, and there are a LOT of rows), and from a storage perspective (double the cost for every as of yet unprocessed row). –  DavidN Oct 10 '13 at 14:43
add comment

You will always have the problem that a row that got created earlier gets comited after another row.

The only way to guarantee that you catch all rows and each one only once is be somehow marking them as processed. Two options for that come to mind:

  1. Adding an indicator column to the table. (You could even create a filtered index on that column.)
  2. Copying new ids into another table with the help of a trigger. (Just the id column not all columns)

The first solution is more straight forward without "hidden" features (triggers). However it requires an additional write to the table after the row was processed.

The second does not require a change in the schema of the column but it adds the overhead of the trigger call. If you keep up with processing the "queue" table will always be fairly small.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.