I have an array of ids (eventids
) which can be million or billion ids inside the table. I am using a for loop to go through each id and retrieve an item from a dynamo db table using the query operation in PHP. However, because the query operation goes over a large amount of info in my table, it takes too long the query operation to do its work. So, in this case I don't get any result because I get a time out. The following code tries to do this operation, which is failing for large amounts of info. I am wondering if you have any recommendation or suggestions on how to make the query operation faster and doable for large amounts of ids inside the $sfweventarrayRDS
array?
for ($j = 0 ; $j < count($sfweventarrayRDS) ; $j++){
$keyconditions = array(
"eventid" => array(
"ComparisonOperator" => ComparisonOperator::EQ,
"AttributeValueList" => array(
array(Type::NUMBER => $sfweventarrayRDS[$j]["eventid"])
)
)
);
$sfweventarrayDynamo = Dynamo::getItems("eventlocation",$keyconditions,$limit);
if (count($sfweventarrayDynamo) > 0){
$timediffepoch = $sfweventarrayDynamo[0]["edatecreated"]["N"] - $sfweventarrayRDS[$j]["edatecreated"];
$timediffstandard = new DateTime("@$timediffepoch");
if ($timediffstandard->format('i') >= 5){
$starttimeepoch = $sfweventarrayRDS[$j]["edatecreated"];
$endtimeepoch = $sfweventarrayDynamo[0]["edatecreated"]["N"];
$starttimestandard = new DateTime("@$starttimeepoch");
$endtimestandard = new DateTime("@$endtimeepoch");
$each_event = array("eventid" => $sfweventarrayDynamo[0]["eventid"]["N"],
"organizationid" => $sfweventarrayRDS[$j]["organizationid"],
"userid" => $sfweventarrayDynamo[0]["userid"]["N"],
"starttime" => $starttimestandard->format('Y-m-d H:i:s'),
"endtime" => $endtimestandard->format('Y-m-d H:i:s'),
"location" => $sfweventarrayRDS[$j]["location"],
"startlatitude" => $sfweventarrayRDS[$j]["latitude"],
"startlongitude" => $sfweventarrayRDS[$j]["longitude"],
"endlatitude" => $sfweventarrayDynamo[0]["latitude"]["N"],
"endlongitude" => $sfweventarrayDynamo[0]["longitude"]["N"]);
array_push($safewalkeventsDynamo, $each_event);
}
}
}
There can be two possible solutions that can improve your query operation:
Use batch get: If you already know event IDs and want to query the db to get more information, then batch get would be ideal. You create a chunk of 100 IDs (max allowed) and call DynamoDB to get information in one go. Repeat until you get all the IDs. Here is the documentation link.
You can increase your tables' read capacity according to your needs. (This will result in paying more money for dynamoDB.)