SQL and Flat Files... In harmony?

Question

I was just thinking, how quick it would be to store the actual data of an application in a flat file.

Now, you can't just go storing everything in a flat file... sometimes sorts and searches are required, and to go through directories and files recursively could be a pain.

Now, imagine, you stored all your search-able data in a database, and had a pointer field, that pointed to a data file?

This would be very specific per app, however- so long as all my search-able data is stored in the database, why should I store the actual data in a database?

(Locking, Data integrity aside) it would be faster, I am sure... but how much, and is it worth doing it?

Answer 1

There is no need to implement a SQL database just to perform searches. Lots of applications store their data in XML, and you can search in many ways, e.g., using Lucene. How fast it is entirely depends on the quantity of data and how you structure it - just like a database.

It can perform very fast, but can complicate things when you want to run more than one app server.

Answer 2

BTrieve was essential what you describe. Back in the DOS days it was a very fast database.

Answer 3

Well you often want to do things in queries beyond search on the data. For instance you might might not search on a field called cost_center, but you might have a case statment that processes things differently depending on the information in the field. Or you might need to concatenate information together. You might update one field based onthe information in another field. You might not search on a field today and need to search on it tomorrow.

A properly designed relational database can easily perform well with terrabytes of data.

And frankly you should never even consider "data integrity aside". If you don't have data integrity you don't have data.

As to whether what you want is a good idea, it depends on the type of data you are storing and the types of things you intend to do with it. There isn't enough information to say for sure.

Answer 4

Well "Locking, Data integrity aside" should mean a faster system. If you drop constraints you should improve performance.

But in practical terms, I don't think it's going to be faster. There's lot of development time behind RDBMSs and that's why they are quick. Sure, non-relational databases are performing better than them in highly parallel situations and scenarios which take advantage of their qualities, for instance. However, your idea does not offer an improvement such as exploiting parallelism... any performance advantage would come from dropping the qualities of RDBMSs...

Answer 5

As well as other answers...

• Sharing of data: how are multiple clients going to access data on a share?
• Backup/Restore: synching of text and "searchable"
• Security/permissions on text data
• Change anomalies