What’s most efficient way to query a LEFT JOIN wit

问题:

I have an entity “coin” linked with a oneToMany on prices. Prices is updated every minute and has millions of entries.

What I’m trying to achieve is a DQL query that will grab the last price to date.

Right now I see 2 ways to do it and I’m wondering which one is best in term of performance:

I could look for prices in the DB with ‘prices’.’lastupdated’ equal to the last update.

OR I could grab the last 100 price ids (we update 100 coins every minute and add 100 new rows in the Database) with a LIMIT 100 and ORDER BY ‘id’ DESC on my left join. I know it’s tricky to use LIMIT on a LEFT JOIN with doctrine but I found a solution here: How to limit results of a left-join with Doctrine (DQL) and here: https://www.colinodell.com/blog/201703/limiting-subqueries-doctrine-2-dql

I’m wondering what will take the least amount of ressources to execute that query.

Of course I’m using getArrayResults() and am using partials and doctrine cache.

What is your opinion on that? Thanks!

回答1:

I've been in similar situations. For example, I run a social commerce network and want to get all follower_ids from a business to update them that an action has been performed. It's the same if you want liker_ids, etc.

In this scenario, you are only interested in a value from one column (price for you) but based off queries involving different fields (coin_id, lastupdated). For this, I greatly advise using doctrine to send a native SQL query. It's orders of magnitude more efficient, evading costly doctrine hydration, etc.

I wrote a sample query in an entity repository for you.

<?php

namespace App\EntityRepository;

use Doctrine\ORM\EntityRepository;
use PDO;

class CoinPricesRepository extends EntityRepository
{
    public function queryLatestPricesForCoinId(int $coin_id, int $limit)
    {
        $sql = 'SELECT price FROM coin_prices WHERE coin_id = :coin_id ORDER BY lastupdated DESC LIMIT  = :limit;';
        $params['coin_id'] = $coin_id;
        $params['limit'] = $limit;

        $stmt = $this->getEntityManager()->getConnection()->prepare($sql);
        $stmt->execute($params);

        return $stmt->fetchAll(PDO::FETCH_COLUMN);
    }
}

回答2:

I have been working on optimizing a my Doctrine request and got some awesome perf improvement I’ll be sharing here if anyone is looking at a similar solution.

First, limit your left join with a where clause as much as possible Second, use partial objets Third, use Array results. This actually changes everything.

/**
 * @return Coins[] Returns an array of Crypto objects
 */

public function findOneByTickerRelationnal($ticker)
{
    $em = $this->getEntityManager();
    $updatesrepository = $em->getRepository(Updates::class);
    $updates = $updatesrepository->findOneBy(['id'=> 1 ]);

    // This is where I’ve been doing additional work to limit my left join as much as possible with a ‘with’ on left join
    $recentMarkets = $updates->getMarket();
    $recentPrices = $updates->getPrice();
    $recentSources = $updates->getSources();

    $cryptos = $this->createQueryBuilder('c')
        ->select('partial c.{id, name, ticker}’) //<= use of partial is a plus but you need to know exactly which fields you want
        ->leftJoin('c.prices', 'p','WITH', 'p.last_updated >= :recentPrices')
        ->addSelect('partial p.{id, price_usd, daily_volume_usd, change_1h, change_1d, change_7d, rank}')
        ->leftJoin('c.markets', 'm','WITH', 'm.last_updated >= :recentMarkets')
        ->addSelect('partial m.{id, cur_supply, market_cap, max_supply}')
        ->leftJoin('c.sources', 's','WITH', 's.last_updated >= :recentSources')
        ->addSelect('s')
        ->where('c.ticker = :ticker')
        ->setParameter('recentPrices', $recentPrices)
        ->setParameter('recentMarkets', $recentMarkets)
        ->setParameter('recentSources', $recentSources)
        ->setParameter('ticker', $ticker)
        ->getQuery()
        ->getArrayResult(); //<=Changes everything 

    $results = $cryptos[0];

    return $results;
}