I have a device that stores raw binary log data as a BLOB in a MySQL DB. That process is a blackbox to me (closed source) which I cannot change. I know, however, how to decode that BLOB and transform it into some human readable data. Inside that BLOB data stream various information is stored, such as dates, text messages, severity, errors, and so on I later want to present to the user calling my ASP.NET MVC application.
The way I'm doing it now is on the fly, meaning that the data is converted into a human readable format in my DAL layer of the ASP.NET application whenever the data is requested. The DAL layer in turn is consumed by the Model of the MVC application.
As the log data DB gets larger and larger I'm not sure if this is the right approach for a couple of reasons: For example, as the log data is binary, all sorting / filtering / etc. has to be done by querying ALL the records, converting it to a human readable format, and perform the sorting / filtering / ... on the converted in-memory dataset.
I thought about the alternatives and came to the following conclusion:
1.) Run a cron / scheduled / whatever job every n minutes and convert the new binary log data to something human readable and store that in the DB. I would have to keep track of the already converted rows, but that's easy. This intermediate layer however would not be part of my ASP.NET application and as such would have to be maintained and tested separately. This is not that important. However, going that route would mean that the user would always miss the log data that accumulated between the last transformation run and now, in the worst case n minutes. A possible workaround would be to run that cronjob within a very tight schedule (like every 10 seconds or so).
2.) Use an insert trigger on the MySQL DB that immediately converts the data. Unfortunately, I'm afraid that I cannot use SQL/PSM to convert the data so I'd need a custom UDF written in C. Unfortunately, I'm not much of a C programmer and I'm lacking the fundamentals on how to write custom UDFs for MySQL.
3.) Keep things the way the are now, which will surely degenerate performance over time as I cannot use efficient indices on the raw BLOB data or perform any filtering / sorting operations on the DB side.
So that's where I'm standing right now. The question is are there any OTHER approaches that come to your mind I haven't thought of? Has anybody ever done something similar, and if so, what approach did you take?
