TDMS vs SQLite

[russ_comer]

I am starting a brand new project. It is a logging application. I am building software to automate a firing log for a R&D Ballistics range. So I have about 20 data points to log per shot. (Timestamp, counter times, and other meta data)

At first glance I thought TDMS would work great for this but I need this to be on going for years and years to come. I need to be able to reopen the file each shot and append the new data to the existing data. TDMS is easier to work with than an SQLite database I just don't know if it's the better option. 

I also want to be able to filter and extract specific data. 

Has anyone ever made or built software like this? What would you recommend using?

Thanks in advance. 

[Jacobson-ni · Accepted solution]

Haven't used SQLite but TDMS is really optimized for streaming data and I personally wouldn't rely on it to hold and organize years of test results in some singular file. I'm not sure how often you will need to log a "shot" but it doesn't sound too much like a streaming application.

The fact that you want to be able to filter and extract data from years of tests seems to indicate the need for a relational database to me. 

[BowenM]

To expand on this a little bit...

TDMS is designed for single writes / channels of large datasets.  Each time you write to TDMS, there is about 30 bytes (if I recall correctly) of TDMS metadata attached.  This is in addition to your data and metadata.

What this means is that with your use case you will have very large TDMS files in the end that are difficult to seek and extract data from.  A SQLite database would be a much better option in my opinion.

You could however test this fairly easily.  Just generate a couple million fake data entries write to TDMS and SQLite.  Then check file size and seek time for each 🙂

[drjdpowell]

SQLite I'd very suited for that use case.  I would use JSON format for the meta info, as that can change over time.  This would not work if you had tons of data, but you don't.

[wiebe@CARYA]

With both options, you can use files as the first level of separation. So you don't need to make one huge TDMS file (or DB), you can make a new file if appropriate, and even use folders to order the data.

If you need to search inside all those files, it will be impractical. But you could make an index for that. It could work like a charm, or be totally impractical...

Lots of options... It depends a lot on details.

I would not use TDMS for anything but (fairly large sets of) sampled data.