How can you optimize a query to fetch a large number of records?

Question

PHP

Tell me how you can optimize the request of the GetList method to select a large number of records from the infoblock. There is a sports news infoblock (sport_news) whose elements have the sport type property (SPORT_TYPE) - this property is a binding to the Sports infoblock element. For example, if there is a sports news about football, it has SPORT_TYPE - record ID Football in the infoblock Sports.

function sportType($arItem){
  $dbProperty = CIBlockElement::getProperty(
      	$arItem['IBLOCK_ID'], 
      	$arItem['ID'], 
      	array("sort", "asc"), 
      	array()
      );
  while ($arProperty = $dbProperty->GetNext()) {
      if ($arProperty['VALUE']) {
          if($arProperty['CODE'] == 'SPORT_TYPE'){
          	$arItem['SPORT_TYPE'][] =  getElementByID($arProperty['VALUE'], $arProperty['LINK_IBLOCK_ID']);
          }
     }
  }
  return $arItem;
}


function sportNews($iblockId, $sportTypeId)
{
  $arSelect = array("*", "PROPERTY_*");
  $arFilter = array("IBLOCK_ID"=>$iblockId, "=PROPERTY_SPORT_TYPE"=>$sportTypeId);
  $arItems = [];
  $res = CIBlockElement::GetList(array(), $arFilter, false, array("nPageSize"=>500), $arSelect);
  while($arItem = $res->fetch()){
    $arItems[] = sportType($arItem); // получаем значение свойства вид спорта (тип Привязка к элементам)
  }
  return$arItems;
}
      
$iblockId = 10; // ID инфоблок новости спорта
$sportTypeId = 5; // ID элемента инфоблока виды спорта (футбол)
sportNews($iblockId, $sportTypeId)

How can this code be optimized to work with large selections of records? It may be possible to remove the sportType function call from the while loop. In this case, how would it be better to get the values ​​of the SportType property and where to call the function? Is it possible to use caching here in the code?

Answer 1

You now first select all elements with their properties from the database (CIBlockElement::GetList), then in the loop for each element you additionally load all its properties again (CIBlockElement::getProperty), and then with another request in the loop you get data about the associated element (getElementByID), which will be the same everywhere. you select with a filtration just on this property.
I would suggest doing this: first download all the news, collect the IDs of the related sports, then load the necessary information on the types of sports with one separate request, and shove it into the news. An example code would be:

function sportNews($iblockId, $sportTypeId) {
    $arSelect = ["*", "PROPERTY_*", "PROPERTY_SPORT_TYPE"]; // Добавить в выборку поле типов спорта. Отдельно с указанием кода - чтобы можно было к нему обращаться по коду, а не по ID
    $arFilter = ["IBLOCK_ID" => $iblockId, "=PROPERTY_SPORT_TYPE" => $sportTypeId];
    $arItems  = [];
    $res      = CIBlockElement::GetList([], $arFilter, false, ["nPageSize" => 500], $arSelect);

    $sportTypes = []; // Массив, в который мы собираем ID связанных типов спорта

    while ($arItem = $res->fetch()) {
        $sportTypes = array_merge($sportTypes, $arItem['PROPERTY_SPORT_TYPE_VALUE']); // Собираем в массив
        $arItems[] = $arItem;
    }


    if (!empty($sportTypes)) {
        $sportTypes = array_unique($sportTypes);
        $arFilterSport = ['ID' => $sportTypes, 'IBLOCK_ID' => 'ИД Инфоблока типов спорта'];
        $resSport      = CIBlockElement::GetList([], $arFilterSport);

        $sportReference = [];
        while ($arItem = $resSport->fetch()) {
            $sportReference[$arItem['ID']] = $arItem; // Раскладываем выбранные типы спорта в ассоциативный массив по ID'шникам
        }

        foreach ($arItems as $ind => $arItem) { // Проходимся по всем новостям
            $arItems[$ind]['SPORT_TYPE'] = [];
            foreach ($arItem['PROPERTY_SPORT_TYPE_VALUE'] as $sportTypeId) {
                $arItems[$ind]['SPORT_TYPE'][] = $sportReference[$sportTypeId]; // Рассовываем в них загруженные типы спорта
            }
        }
    }

    return $arItems;
}

$iblockId = 10; // ID инфоблок новости спорта
$sportTypeId = 5; // ID элемента инфоблока виды спорта (футбол)
sportNews($iblockId, $sportTypeId);

Answer 2

Thank you very much, I used your hint to parse the list of all current mirrors of the Fonbet bookmaker on this resource , now I will leave it. I also set up an automatic domain replacement in case of blocking the site by the RKN.