I am currently on a task of processing 4 million records to produce a report. It takes me about 10 seconds in the code.
You could always use a sliding window type algorithm to keep up with the grown of the data.
Ask HN: Generating reports on time series data without killing performance?
11–15 of 15 posts
Re: Ask HN: Generating reports on time series data without killing performance?
#12I think you're probably wrong that you can't handle it in stored procedures (packages). You may even be able to do a lot of the processing in a single SQL statement? Try this first, then PL/SQL. Do the bits you can't do in PL/SQL in a java class stored on the database...
Re: Ask HN: Generating reports on time series data without killing performance?
#131. 10 million records is not big data.
2. If the rate of data acquisition is 1 million records a year, then 9 million records are the same as last year. There's no reason to hit the database again, because time series data [to be time series data] does not change.
3. This suggests that processing the data from the Oracle database is not a requirement...i.e. it could be moved to another system and processed there. Again, it's small data and doesn't change so duplication presents neither a storage issue nor a consistency issue.
4. The size and static nature of the data suggest that it might fit into memory on a single, moderately speced PC. An AWS type approach is also possible.
5. The right data storage format depends on the workload...maybe something column oriented?
Good luck.
Re: Ask HN: Generating reports on time series data without killing performance?
#14If the requirements allow, consider replicating data in memory with SQLite :memory: option. 10 million records will be nothing for it.
Let us know.
Re: Ask HN: Generating reports on time series data without killing performance?
#15When working with time series it is a good practice to move the data in memory and vectorise both the data and the data-manipulation logic. Doing so you can take advantage of the battle tested and fast linear algebra libraries like BLAS and LAPACK (or even move the calculation to the GPU). The last time I worked with time series data I used python-pandas. Even without former python knowledge it took only a couple of weeks to get productive. It has also a wrapper around SQLalchemy so you don't have to deal directly with the ORM.