[Search] Eliminate extra queries for search request

[magento-engcom-team]

The current request time to SearchSerivce is too big (~300ms)
Need to optimize search request by removing/caching extra queries and

Repo: https://github.com/magento/storefront-search-ce
Service method: \Magento\SearchStorefront\Model\SearchService::searchProducts

With cached configuration we can expect only 3 requests to DB

• request to get synonyms: \Magento\SearchStorefrontSearch\Adapter\Query\Preprocessor\Synonyms::process
• request to get store/website data
• request to get attributes labels to build the response for layered navigation

All of them may be cached/optimized

Places to check:

• load Store in searchProducts method
  • cache SQL query results
• Optmize build query logic in Magento\SearchStorefrontElasticsearch\SearchAdapter\Query\Builder\Match::buildQueries
  • there are a lot of SQL requests for eav attributes like "color", "description" ,which not present in the search request

To check SQL queries and trace to them we can enable db log in app/etc/env.php:

# to enable db logs add into env.php:

   'db_logger' => [
        'output' => 'file',
        'log_everything' => 1,
        'query_time_threshold' => '0',
        'include_stacktrace' => 1
    ]

To test search without gRPC request:

<?php

use Magento\SearchStorefrontApi\Api\Data\ProductSearchRequest;
use Magento\SearchStorefrontApi\Api\Data\ProductSearchRequestMapper;

try {
    require __DIR__ . '/app/bootstrap.php';
} catch (\Exception $e) {
    exit(1);
}

$bootstrap = \Magento\Framework\App\Bootstrap::create(BP, $_SERVER);


$om = $bootstrap->getObjectManager();

/** @var ProductSearchRequestMapper $requestMapper */
$requestMapper = $om->create(ProductSearchRequestMapper::class);

$requestArray = [
    'phrase' => 'simple',
    'store' => '1',
    'include_aggregations' => false,
    'customer_group_id' => 0,
    'page_size' => 5,
    'filters' => [
//        [
//            'attribute' => 'color',
//            'eq' => 98
//        ],
//        [
//            'attribute' => 'material',
//            'in' => ['199','195']
//        ],
        [
            'attribute' => 'price',
            'range' => [
                'from' => 0,
                'to' => 30
            ]
        ]
    ]
];
$request = $requestMapper->setData($requestArray)->build();

/** @var Magento\SearchStorefront\Model\SearchService $searchService */
$searchService = $om->get(\Magento\SearchStorefront\Model\SearchService::class);
$res = $searchService->searchProducts($request);

print_r($res->getItems());
print_r($res->getFacets());
print('Items: ' . count($res->getItems()) . ', Facets: '.count($res->getFacets()));
//echo "\n time:", (\microtime(true) - $t), "\n";

[magento-engcom-team]

The issue was exported from the internal JIRA. The link to the original JIRA issue: https://jira.corp.magento.com/browse/SFAPP-174

[m2-assistant]

Hi @magento-engcom-team. Thank you for your report.
To help us process this issue please make sure that you provided sufficient information.

Please, add a comment to assign the issue: @magento I am working on this

---

• Join Magento Community Engineering Slack and ask your questions in #github channel.

[dani97]

@magento I am working on this

[dani97]

@mslabko to optimize multiple attribute calls in magento monolith call is made to CompositeFieldProvider implementation of FieldProviderInterface that fetches all field configurations. I will implement this in search storefront but should we contact it directly or through catalog storefront.

[dani97]

@mslabko the current implementation of FieldProviderInterface doesn't implement get all fields, when I inspected the magento-ce FieldProviderInterface implementation has dependency on module-eav. Should I wait until there is a GRPC service in catalog-storefront for attributes or include module-eav

[dani97]

@mslabko any suggestions on this?