So I'm comfortable using subqueries to get the data I need, however I'm going to be running this query on 250 nodes every 2 minutes.
The subqueries take the node caption to get the nodeid, and then to get the Custom Poller assignment ID and onto the label row and actual data. However that outputs all of the data collected, and what I want is just the most recent poll.
So I plan to take the queries that drill down to the data, and cache the output of those so I only run them infrequently so that those queries don't generate load when the data isn't changing.
But when I hit the Orion.NPM.CustomPollerStatusOnNode table, I am presented with data going back to the begining of time. What is the best way to query this table in a way I am not touching every row to find the most current row for each node.
If I have 250 to 400 node ID's (a reasonable growth estimate) what is the most efficient way to pull that data over? I can do a query for each node, or I can combine the node ID's in one query and pull it all over together. But just because *I* think one method is more efficient than another doesn't make it true, so I'm hoping to get some insight regarding the best way to do this.
To put some context on it, we are one of the largest retailers, with a highly scrutinized and professionally optimized set of SolarWinds installations, with the database engines on the best purpose built database hardware. And even dividing the environment across two different SolarWinds instances, we are extremely bottlenecked at the database... So every query I make is on top of an already saturated database engine has a cost. The data extraction is a legal requirement that must happen. The heart of the requirement is to retain the fine resolution trend data 20 times longer than SolarWinds possibly can by moving it into a big-data cloud.
So I think the question boils down to the lightest database touch possible while fetching the most recent entries for a particular family of nodes from the Orion.NPM.CustomPollerStatusOnNode table. I'm pretty good with sql, but my methods probably resemble brute force more than optimized queries. And it doesn't help that additional layers of abstraction may exist between me and the data,
