afoster009's avatar

Speed up query to large database using eloquent.

Hello there, i am trying to fetch over 50k records from my DB using eloquent but it just takes forever, and in all honestly 50k records isn't even that big of a deal.

Right now i am trying to use the built in chunk() method but that doesn't seem to help, nor do i really understand what its doing.

A little background on the setup i have here.

I have 2 models.

Formula Model:

class Formula extends Model
{
    //

    protected $table = 'Formula';
    protected $primaryKey = 'FormID';
    protected $hidden = ['SSMA_TimeStamp'];
    public function mix()
    {
        return $this->hasMany('App\Mix', 'FormID');
    }



}

Mix Model:

class Mix extends Model
{
    //
    protected $table = 'Mix';
    protected $primaryKey = 'FormID';
    protected $hidden = ['SSMA_TimeStamp'];
    public function formula(){
        return $this->belongsTo('App\Formula', 'FormID' )->select();
    }
}

Basically i am trying to grab all formulas and their respective mixes through an api call to this route.

Route::get('/formulas', function(){


    App\Formula::with('Mix')->chunk(200, function($formulas){
       foreach ($formulas as $formula){
          return $formula;
       }
    });


});

as it stands i get no JSON in my browser but when i var_dump($formula) i see some data.

So i must be doing something wrong here, is anyone able to point me in the right direction?

PS: Returning this:

return App\Formula::with('Mix')->limit(300)->get();

gets me 300 records of my desired data but when i try to fetch all of them i exceed my max execution time which is why i tried using chunk in the first place.

if more information is needed let me know.

Thanks in advance!

0 likes
10 replies
afoster009's avatar

I am not displaying the data in a view within laravel but rather i am building a REST api that will be used by another application

afoster009's avatar

Also to humor you this is the error when i slap paginate on the end

Tried to bind parameter number 2101. SQL Server supports a maximum of 2100 parameters

return App\Formula::with('Mix')->get()->paginate(50);

edit

 return App\Formula::with('Mix')->paginate(50);

damn near instant and kinda feel dumb.

afoster009's avatar

Although it technically works its still not the solution i am looking for. Having it paged like that just causes me more work in the client side :( any other suggestions or is this my best bet and i should just role with it

arthurvillar's avatar

Install the Debugbar (go to the docs here). Import that database into your local machine and see how many queries Laravel is actually making behind the scenes (you will probably see tons of repeated or seemingly unnecessary queries). You can fix this using through many different ways, but you first need to know exactly what is going on.

afoster009's avatar

I checked this out already and i am getting a bunch of the same query over and over. this one to be exact

[2018-02-25 06:16:57] local.INFO: select top 200 * from [Formula] order by [Formula].[FormID] asc  
[2018-02-25 06:16:57] local.INFO: select * from [Mix] where [Mix].[FormID] in (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) [8131,8132,8133,8141,8142,8143,8144,8145,8146,8147,8154,8155,8156,8157,8158,8281,8282,8283,11194,11195,12158,14981,14984,14986,14990,14992,14993,14996,14998,14999,15002,15007,15009,15013,15017,15020,15022,63679,67109,67118,67214,67297,68073,68414,68415,68416,68417,68418,68419,68420,68421,68422,68423,68424,68425,68426,68427,68428,68429,68430,68431,68432,68433,68689,68735,68763,68792,68793,68794,68795,68896,68898,68899,68917,68918,68919,68920,68921,68922,68923,68925,69203,69442,69641,69865,69953,69970,70527,70536,70545,70900,71603,71751,71908,71924,72479,73087,73405,73438,73463,73464,73556,73557,73558,73559,73560,73561,73562,73563,73564,73565,73566,73567,73568,73569,73578,73579,73590,73591,73596,73597,73598,73599,73600,75529,75565,80426,80427,80428,80429,80430,80431,80497,80554,80563,80628,80632,80634,80648,80653,80667,80679,80680,80681,80723,80744,80775,80776,80777,80778,80779,80786,80813,80815,80816,80819,80838,80839,80847,80848,80849,80850,80851,80852,80855,80864,80865,80958,80961,80966,80967,80970,80976,80982,81021,81182,81183,81380,81459,81487,81528,81529,81532,81550,81557,81565,81571,81662,81789,81864,81866,81877,81879,81882,81883,81886,81891,81893,81898,81919] 
[2018-02-25 06:16:58] local.INFO: select * from (select *, row_number() over (order by [Formula].[FormID] asc) as row_num from [Formula]) as temp_table where row_num between 201 and 400  
[2018-02-25 06:16:58] local.INFO: select * from [Mix] where [Mix].[FormID] in (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) [82092,82113,82166,82277,82278,82286,82339,82458,82480,82482,82487,82493,82500,82510,82513,82525,82527,82528,82590,83938,84272,84277,84376,84394,84624,84793,85070,85292,85301,85305,85723,85960,86573,87473,87541,87612,87617,87626,87636,87646,87703,87704,87712,87742,87749,87829,87865,88145,88399,88479,88974,89002,89404,89421,89422,89551,89613,90276,90354,90384,90636,91119,91163,91164,91313,91402,91488,91561,92116,92135,92615,93059,93082,93083,93092,93094,93095,93096,93097,93098,93099,93146,93148,93149,93150,93151,93152,93153,93154,93155,93156,93158,93159,93160,93161,93162,93163,93164,93165,93166,93187,93198,93210,93228,93229,93230,93233,93237,93246,93249,93250,93252,93256,93264,93265,93284,93286,93287,93291,93294,93301,93304,93305,93307,93314,93315,93327,93334,93335,93336,93370,93373,93374,93375,93461,93463,93465,93516,94092,94682,95765,95767,95768,95769,95770,95772,95773,95774,95775,95776,95778,95779,95780,95781,95782,95783,95784,95785,95786,95789,95790,95791,95792,95794,95795,95796,95797,95798,95799,95800,95801,95802,95803,95804,95806,95812,95813,95815,95816,95817,95818,95819,95820,95821,95822,95824,95825,95826,95827,95828,95829,95830,95831,95832,95833,95834,95835,95836,95838,95839] 
[2018-02-25 06:16:59] local.INFO: select * from (select *, row_number() over (order by [Formula].[FormID] asc) as row_num from [Formula]) as temp_table where row_num between 401 and 600  
[2018-02-25 06:17:00] local.INFO: select * from [Mix] where [Mix].[FormID] in (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) [95840,95841,95842,95843,95844,95845,95846,95847,95848,95849,95850,95851,95853,95854,95855,95856,95857,95858,95859,95861,95862,95863,95864,95865,95866,95867,95868,95869,95870,95871,95872,95874,95875,95876,95879,95880,95881,95883,95884,95885,95887,95888,95889,95890,95891,95892,95893,95894,95895,95896,95897,95898,95899,95900,95901,95902,95903,95904,95905,95906,95909,95910,95911,95913,95915,95916,95920,95922,95923,95924,95926,95927,95928,95929,95930,95931,95932,95933,95934,95935,95936,95937,95938,95939,95940,95941,95942,95943,95944,95946,95947,95948,95949,95951,95953,95954,95955,95956,95957,95959,95960,95961,95962,95963,95965,95966,95967,95969,95970,95971,95972,95973,95974,95977,95978,95979,95980,95983,95985,95986,95987,95988,95989,95990,95991,95992,95993,95995,95996,95997,95998,95999,96004,96006,96007,96008,96009,96011,96012,96013,96014,96015,96016,96018,96019,96020,96021,96022,96023,96024,96025,96026,96027,96028,96029,96030,96031,96032,96033,96034,96035,96036,96037,96038,96039,96040,96041,96042,96043,96044,96045,96046,96047,96048,96049,96050,96051,96052,96053,96054,96055,96056,96057,96058,96059,96060,96061,96062,96063,96064,96065,96066,96067,96068,96069,96070,96071,96072,96073,96074] 
[2018-02-25 06:17:00] local.INFO: select * from (select *, row_number() over (order by [Formula].[FormID] asc) as row_num from [Formula]) as temp_table where row_num between 601 and 800

using the chunk method.

rumm.an's avatar

But still, what would you do with 50k records loaded at once? I'd never load that much of data in one single request. You should use Pagination instead. If you are using vue, you can extract a pagination component, give this screencast a shot!

MikeHopley's avatar

Just to give a bit of context:

Look at what other big APIs do. For example, I use Vimeo. I can ask their API to return data for all my videos, but it doesn't give me the whole dataset at once.

Vimeo returns a (configurable) number of videos per page, as well as URLs for next and previous pages, and a page number. The consumer of the API is expected to make multiple API requests as needed.

You'll see something similar with any major API that can return lots of results. API calls get paginated.

1 like
shez1983's avatar

paginate() is the way forward.. ANY api would (and should) not let you get all the results... its pretty standard and correct..

the client has to DEAL with pagination themselves.. look at common apis they all have pagination...

having said that if you dont want to paginate then look at

  1. caching
  2. using RAW SQL - eloquent is very slow as it has to put all the db rows into a model and if there are LOTS then it will take time
  3. temp increase the execution time on that end point.

but really you SHOULD be paginating...

gregrobson's avatar

To add to @MikeHopley - an example of a paginating API is pipedrive.

https://developers.pipedrive.com/docs/api/v1/

You will typically have a maximum number or results per request and the API will advise on where the next batch can start. That's normally just an offset, but some APIs will give a timestamp if the data is temporal in nature (e.g. server logs)

Please or to participate in this conversation.