So we recently ran in to an issue where we wanted to get a large number (700+) items from a metric table in a RDS warehouse DB quickly. This metric table has a layout something similar to this:
ID, Value1, Value2, Value3, Date
What we really want is to effectively run a query that goes through each 700 items and makes a small select for data regarding that item. Something similar to this select * from table where Value1 = 3 and Value2 = 1 and Value3 = 501 and date > '2015-06-01'. The problem is that 700 tiny requests end up being too slow since the round trip to DB just kills you. So we tried a larger query but basically we have to just say select * from table where date > 2015-03-01 and Value3 = 501. This has the problem of pulling way too much data, since we have to expand our scope to include way more data than each individual item would need. And practically speaking SQL doesn't like huge where statements like select * from table where (Value1 = 3 and Value2 = 1 and Value3 = 501 and date > '2015-06-01') or (Value1 = 2 and Value2 = 1 and Value3 = 501 and date > '2015-05-01')or (Value1 = 5 and Value2 = 1 and Value3 = 501 and date > '2015-06-01'). I'm not aware of anything that handles this kind of quick look up.
Even things like redis have long round trip times when you need to make lots of little requests, and I'm not familiar with any persistance layer that can do this kind of larger complex key lookup. I realize you can create large sql statements to do it, but it seems rather like a cludge. Maybe there is nothing that handles this, but I figured I would see if anyone was aware of something that is built to handle these kinds of lookups.
Just a point of reference we're looking for sub 100 ms grab of data, so when you have a round trip of even 1 ms, across 700 items it gets too slow.