Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Ask HN: Generating reports on time series data without killing performance?

news.ycombinator.com

Post reply on HN
Print view View original

1–10 of 15 posts

Ask HN: Generating reports on time series data without killing performance?

#1

Post by Kendrick2 »

I have an application which contains approximately 10 million time series records growing at a rate of 1 million per year. This application generates reports on subsets of the time series data. These reports have poor performance characteristics because for each record in a time series, another subset of time series data must often be loaded as part of the computations behind the report. Effectively this can lead to N^2 performance. The current workaround is to implement these reports as stored procedures in the Oracle database where the time series data resides. This saves network roundtrips for every time series record.

I've found the stored procedures to be insufficiently flexible to handle complex requirements compared to modern programming languages (requirements come in for new reports on a regular basis). I'd like to generate these reports in application code (C#) but can't see a way around the performance issue. Has anyone dealt with similar challenges and how did you work around them?

Re: Ask HN: Generating reports on time series data without killing performance?

#2

Post by eschutte2 »

That volume of data should be handled pretty easily by the right indexes and joins. At one place with that order of magnitude of data we kept an offline synced clone of the data with indexes designed for the queries we needed to run (this is basically OLAP). Have you already looked into that?

Re: Ask HN: Generating reports on time series data without killing performance?

#3

Post by Kendrick2 »

eschutte2 wrote: post #2

That volume of data should be handled pretty easily by the right indexes and joins. At one place with that order of magnitude of data we kept an offline synced clone of the data with indexes designed for the queries we needed to run (this is basically OLAP). Have you already looked into that?

Yes currently all the reports operate quite efficiently on a set of indexes. The performance of the database is currently very good with the reports in PL/SQL. The problem arises when the reports are generated using application logic on another server. Let's say we have a report for the last 10,000 records. For each of those records, I have to query the database again for some other time series information. If the latency between the reporting server and database server is 2ms, that report is going to take 20 seconds to generate.

Edit: If you're saying mirror the time series data onto the application server to avoid the network roundtrip, I guess that is possible but we still face the SQL overhead with each query.

Re: Ask HN: Generating reports on time series data without killing performance?

#4

Post by eschutte2 »

Kendrick2 wrote: post #3
eschutte2 wrote: post #2

That volume of data should be handled pretty easily by the right indexes and joins. At one place with that order of magnitude of data we kept an offline synced clone of the data with indexes designed for the queries we needed to run (this is basically OLAP). Have you already looked into that?

Yes currently all the reports operate quite efficiently on a set of indexes. The performance of the database is currently very good with the reports in PL/SQL. The problem arises when the reports are generated using application logic on another server. Let's say we have a report for the last 10,000 records. For each of those records, I have to query the database again for some other time series information. If the la…

Yeah, you definitely don't want to be making those roundtrips. Why doesn't a join work?

Re: Ask HN: Generating reports on time series data without killing performance?

#5

Post by Kendrick2 »

eschutte2 wrote: post #4
Kendrick2 wrote: post #3

Earlier quoted context omitted.

Yes currently all the reports operate quite efficiently on a set of indexes. The performance of the database is currently very good with the reports in PL/SQL. The problem arises when the reports are generated using application logic on another server. Let's say we have a report for the last 10,000 records. For each of those records, I have to query the database again for some other time series information. If the la…

Yeah, you definitely don't want to be making those roundtrips. Why doesn't a join work?

Complex domain-specific requirements preclude simple joins unfortunately. This is why I'd like to redevelop these queries in C#. There are many imperative steps performed on each time series record to decide how these reports are generated.

To give a very broad example of one report: take 10,000 latest time series records from geographic location X. Now for each of those records load the latest 100 time series records from location Y and Z. Multiply all records from Y and Z together then divide by the value at location X. Throw in lots of business rules at every one of these steps so we have no chance of representing this as a tidy join operation and so that we can make no assumptions about the data we'll need to load to generate the report :)

Re: Ask HN: Generating reports on time series data without killing performance?

#6

Post by flukus »

Kendrick2 wrote: post #5
eschutte2 wrote: post #4

Earlier quoted context omitted.

Yeah, you definitely don't want to be making those roundtrips. Why doesn't a join work?

Complex domain-specific requirements preclude simple joins unfortunately. This is why I'd like to redevelop these queries in C#. There are many imperative steps performed on each time series record to decide how these reports are generated. To give a very broad example of one report: take 10,000 latest time series records from geographic location X. Now for each of those records load the latest 100 time series record…

Have you tried loading all the data required and then munging it in c#?

Edit - returning multiple result sets from the server is always an option too.

Re: Ask HN: Generating reports on time series data without killing performance?

#7

Post by Kendrick2 »

Kendrick2 wrote: post #5
eschutte2 wrote: post #4

Earlier quoted context omitted.

Yeah, you definitely don't want to be making those roundtrips. Why doesn't a join work?

Complex domain-specific requirements preclude simple joins unfortunately. This is why I'd like to redevelop these queries in C#. There are many imperative steps performed on each time series record to decide how these reports are generated. To give a very broad example of one report: take 10,000 latest time series records from geographic location X. Now for each of those records load the latest 100 time series record…

> Have you tried loading all the data required...

Sorry, I probably edited my post while you were typing. The amount of data to load is theoretically unbounded (could load the entire data set for a big report) and it's very difficult to estimate ahead of time which data needs to be loaded. This is because the data in the time series dictates which secondary sets of data will be loaded on a record-by-record basis.

Re: Ask HN: Generating reports on time series data without killing performance?

#8

Post by eschutte2 »

Kendrick2 wrote: post #5
eschutte2 wrote: post #4

Earlier quoted context omitted.

Yeah, you definitely don't want to be making those roundtrips. Why doesn't a join work?

Complex domain-specific requirements preclude simple joins unfortunately. This is why I'd like to redevelop these queries in C#. There are many imperative steps performed on each time series record to decide how these reports are generated. To give a very broad example of one report: take 10,000 latest time series records from geographic location X. Now for each of those records load the latest 100 time series record…

If it's not possible to do a few big queries to pull all the necessary base data (all the related Ys and Zs for the last 10,000 Xs) and then join/filter it yourself in code, then I think you are stuck with many queries. The options I can think of are: 1) keep the logic in stored procedures; 2) find a way to fetch the raw data in a few large queries, then join yourself; 3) let the reports be slow and generate them in the background; 4) write one complex SQL query that does all the crunching (if possible, you're saying it's not).

Re: Ask HN: Generating reports on time series data without killing performance?

#9

Post by Kendrick2 »

Kendrick2 wrote: post #5
eschutte2 wrote: post #4

Earlier quoted context omitted.

Yeah, you definitely don't want to be making those roundtrips. Why doesn't a join work?

Complex domain-specific requirements preclude simple joins unfortunately. This is why I'd like to redevelop these queries in C#. There are many imperative steps performed on each time series record to decide how these reports are generated. To give a very broad example of one report: take 10,000 latest time series records from geographic location X. Now for each of those records load the latest 100 time series record…

> If it's not possible to do a few big queries...

Yeah, this is the unfortunate conclusion that I arrived at too. I'm looking at making these reports go through a job queue which e-mails the result to the user when it's done. This would allow me to develop much more complex, but much slower queries in C#.

Post reply on HN