Retrieving Data From Real Time Interface (RTI)

[TKnight]

Hi,

For a project i'm working on I need to take data from a RTI every few minutes or so. The data then needs a bit of processing before it can be posted to another table which users will be constantly querying. I'm thinking about creating a second back end just to handle the "Real Time" table and appending the processed data to where it needs to go.

I'm just wondering if there are any issues that might crop up regarding large-ish appends to a table that is likely to be queried during the process?

Thanks,

Tom

 

[ezfriend]

Once it's in the second back end, I would probably then append a chunk of records at a time to the production BE database.

 

[The_Doc_Man]

There are ways to handle this, but the issue that is going to be most important is how you lock the data closest to the RTI.

I would fix it so that somehow I could mark data with something that lets me know when it was acquired. What I would want to avoid is to have a background processing cycle try to read a part of the table that is in the process of being updated. If there is a way to identify import cycles, use it. Or, if you know that there will be enough time to set a "state variable" in the record, then I would do this.

Have the RTI data go into the table with state-variable set to 0. When the RTI input is complete for the moment, IMMEDIATELY go back and update the state-variable to 1. Then use queries to only process data in state 1 AND mark that data as transitioning to state 2. You could then dispose of data in state 2. This concept could be extended to as many states as you needed for your processing. The idea is to make it possible to identify data ready to be folded into your processing scheme, distinguishing it from data being acquired (state = 0) and data already processed (state = 2).

Then don't let the processing code set a lock on anything defined by the RTI. Instead, when you update the repository data from the processed record in state 1, reset the state variable to 2.

The other way to do this is if the RTI can time-tag or sequence-tag the data, you can perhaps run your processing based on that tag as a guide to when it is safe to process.

The common theme here is that to make this work, you must design your needs into the DB, and your need is to know when to process or not process.