Love Fuel?    Donate

FuelPHP Forums

Ask your question about FuelPHP in the appropriate forum, or help others by answering their questions.
Query optimisation
  • Hello,

    Thanks to the profiler, i can see on several pages on my website there was a lot of queries, and some duplicated queries.
    After some research, i realize that i didn't call all related model in my Model_XX::query->get().

    With all the related, the count of query decrease a lot, but the laod time increase so much !

    I tried to split my query and thanks to this, the load time was better.

    I would like to understand why.

    To sum up :

    I had : 
    $model = Model_X1::query()->where('id', $id)->get_one();

    In my view i called $model->related1, and it worked, so i suppose the framework find itself the relation and did the good query. But the query was duplicated.

    I change my controller to have : 
    $model = Model_X1::query()->related('related1')->related('related2')->where('id', $id)->get_one();
    I didn't have any duplicated queries but the load time was very long.

    Finaly i split the query : 
    $model = Model_X1::query()->where('id', $id)->get_one();
    $model->related1 = Model_X2::query()->where('model_id', $id)->get();
    $model->related2 = Model_X3::query()->where('model_id', $id)->get();

    and it's better, but i don't know why.
    And i don't want to write again my queries trying when it will be better.

  • The ORM supports what we call "lazy loading", if you request a relation that wasn't loaded in the original query, the ORM fires a second query to load the relation. If you know you need the relational data, it is usually better to include it and use a join.

    Whether or not that is better or faster depends for a large part on your choice for RDBMS, the installation and tuning of it, the hardware it runs on, and your database design.

    In theory a join is faster than seperate queries, but it could well be that due to your local circumstances, that isn't the case for you. 

    If you use MySQL, the profiler also includes data from the query analyzer, which tells you how the query is run (in terms of indexes used, area sweeps, on-disk-sorting, etc), which in turn can tell you why a query is slower than expected.
  • OK, thanks a lot

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

In this Discussion