Kendrick2

> 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#.

> 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.

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 :)

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.

How do web applications explode out to 3 Million lines of code? Yelp, to me, looks like a typical CRUD app and I would have been surprised if it were more than 100,000 lines of code. The software I develop is pretty large and typically doesn't surpass 40,000 sloc written in-house (i.e. excluding third party libs).

Does anyone here maintain such large codebases? Are they truly that big or are people just counting third party code and generated stuff?