2000 External Api Call in a job

Hello everyone one :) First thank you for everything here for the tutorial and also the forum what an amazing community :) I come from NodeJS yes this can happen :) And the community here is so nice thank you :)

So I'm building an app where I need to fetch NHL.com data from their api. I need to fetch the stats of all the player of the league once by day. It's 2096 api call I need to do.

In node I will have use a job with Promise.all etc.

But here right now my job is 9min long. Can I do something to improve here ? The external api is really quick. I think the issue is really insertOrUpdate the item to the db with eloquent.


class RefreshRosterJob implements ShouldQueue
{
    use Dispatchable, InteractsWithQueue, Queueable;

    public function handle()
    {
        $nhlApi = new NHLClient();
        $recordsApi = new RecordsNHLClient();

        $teams = NhlTeam::all();

        foreach ($teams as $team) {
            $recordsPlayers = $recordsApi->getAllPlayersForTeam($team->id);

            foreach ($recordsPlayers as $recordsPlayer) {
                $player = $nhlApi->getPlayerInfo($recordsPlayer->id);

                NhlPlayer::updateOrCreate([
                    'id' => $player->id,
                    'full_name' => $player->fullName,
                    'first_name' => $player->firstName,
                    'last_name' => $player->lastName,
                    'birth_country' => $player->birthCountry,
                    'birth_city' => $player->birthCity,
                    'birth_date' => $player->birthDate,
                    'birth_state_province' => $player->birthStateProvince,
                    'age' => $player->age,
                    'active' => $player->active,
                    'captain' => $player->captain,
                    'alternate_captain' => $player->alternateCaptain,
                    'roster_status' => $player->rosterStatus,
                    'rookie' => $player->rookie,
                    'height' => $player->height,
                    'weight' => $player->weight,
                    'shoot' => $player->shoot,
                    'jersey_number' => $player->jerseyNumber,
                    'position_id' => $player->position,
                    'team_id' => $player->teamId,
                ]);
            }
        }
    }
}

Should I chunk ? Should I make a job for each player ? Should I use raw sql to make it quicker and insert as bulk ?

mvd

5 years ago

Level 48

Hi @equimper

Now you are doing a query for each player, so yes a lot of queries. Bulk is a good idea but Eloqent does not support bulk for updateOrCreate

Maybe you can use something like this for bulk insert https://gist.github.com/RuGa/5354e44883c7651fd15c ?

davidifranco

5 years ago

Level 10

UpdateOrCreate takes two parameters.

1- an array of values to determine if the record exists. 2 - an array of values to persist along with the update or create.


// If there's a flight from Oakland to San Diego, set the price to .
// If no matching model exists, create one.
$flight = App\Flight::updateOrCreate(
    ['departure' => 'Oakland', 'destination' => 'San Diego'],
    ['price' => 99, 'discounted' => 1]
);

I would suggest that you narrow down the columns that determine if the record is new or to be updated. Put those columns in the first array of your updateOrCreate. Then using those same columns make a composite index in your database.

You should see improvement. However, with update or create, I do find that the larger your table grows... the longer your jobs will take to finish.

Please or to participate in this conversation.