Just Plain Working Smarter Not Harder
I was looking at the changes I'd made to the SQL in my web app to speed things up, which was really nice, but it ended up still being an issue. You see, the speed was nice when there were no INSERT statements hitting the database, but when I got into the thick of things today, I realized that the real issues were classic - locks, amount of data moved, etc. No way around the simple facts.
What ended up happening was the time climbed to the point that we got back to the 400 - 500 msec range. What I realized was that there's no "free lunch", and to get the times down to reasonable values I needed to look at what I was doing not just messing with the engine and tables.
What I had been doing was re-fetching the same data for the entire timespan and invalidating the cache when any data comes in from any of the sources. It's foolproof, it's just making me do the same work over and over and over again. The best I was able to do was a constant access time that increases during the day. But in reality, it's easy to think of a better solution.
Let's leave the table in-place and then "refresh" it by looking at the last acquired data point and then using that as the start time for the same request. I then could use that data to make an identical table, and then merge that table to the bottom of the existing table.
What I end up with is updating the last data point and then if there's more data, it's going to be added to the end of the table. If we have multiple requestors for the same table, the data will be added for each one. We put a nice little synchronization on the table being refreshed so that we don't have multiple edits at the same time, and all is OK.
What I've seen when I implemented this was a dramatic increase in speed. More than a factor of 10x on all the tables of data in the web app. It's going to make the load on the server a lot less, and we'll be able to handle a lot more users on the same hardware. Nice.
Really, this isn't a real surprise - you can work harder and get a little bit more, or you can work smarter and really make a huge difference. Should have done this earlier, but I didn't need it, so I didn't do it.