Delivering Kohana ORM from unnecessary database queries

ORM is undoubtedly a powerful and convenient thing, but requests are generated not only not always optimal, but also superfluous. When creating an object, the ORM must know the information about all the fields of the corresponding database table. Which leads to unwanted queries to the database.

Problem

When creating a model object using ORM, the query SHOW FULL COLUMNS FROM `tablename` is executed and the protected $ _table_columns object field is populated with an array of field data.

protected _table_columns => array(8) ( "id" => array(4) ( "type" => string(3) "int" "is_nullable" => bool FALSE ) "email" => array(4) ( "type" => string(6) "string" "is_nullable" => bool FALSE ) ... 

The screenshot shows the last query to the database (clickable)


Moreover, ORM :: factory () each time creates a new instance of the object and, therefore, calls several methods in succession using the

 ORM::factory('model_name')->method_1() ORM::factory('model_name')->method_2() 

generates 2 identical queries SHOW FULL COLUMNS FROM (even if $ _table_columns is not needed at all in a particular case). Also loading related models generates requests for each of the models — calling ORM :: factory ('user') -> with ('profile') -> with ('photo') . It turns out every second request to the database in the project (actively using ORM) - SHOW FULL COLUMNS FROM .
')

One solution

The solution to the problem is very simple, but for some reason not described anywhere - manually fill this array in each model (of course at the end of the project development). Attempting to fill it in manually for several dozen large tables is like shooting yourself in the leg. Therefore, within a few hours, a universal solution was found - write the Optimize class, which recursively traverses the model folder, selects the extends ORM record that does not contain the protected $ _table_columns record and generates this array for the model using ORM :: factory ('model') - > list_columns () and the slightly modified “native” method Debug :: vars ();
The code of the class itself - under the spoiler



It was decided not to do the array entry with the fields automatically into the class code of the model - anyway, you cannot guess the formatting of the code. Therefore, everything is displayed on the screen in the form:

 Model_Option protected $_table_columns = array( 'id' => array( 'type' => 'int', 'is_nullable' => FALSE, ), 'name' => array( 'type' => 'string', 'is_nullable' => FALSE, ), 

The class itself (placed in /application/classes/optimize.php). Calling a method from anywhere:

  echo Optimize::list_columns(); 

The proof of the method is the absence of last_query in the printed model object.


Other found solutions - blogocms.ru/2011/05/kohana-uskoryaem-orm - caching table structure. More simple solution, but less optimal in speed.

Profiling and tests

An attempt was made to measure the speed (I do not pretend to measure accuracy). We write a little synthetic test

 $token = Profiler::start('Model', 'User'); ORM::factory('user')->with('profile')->with('profile:file'); Profiler::stop($token); echo View::factory('profiler/stats'); 

And run it 10 times. We get that without filling in the $ _table_columns arrays, on average, the entire work of the framework takes 0.15 seconds, of which 0.005 seconds. on requests SHOW FULL COLUMNS FROM.
With filled $ _table_columns - an average of 0.145 seconds. Growth of 3.3%

Let's write a more real test with a sample of several records and the use of related models.

 $token = Profiler::start('Model', 'User'); for ($index = 0; $index < 10; $index++) { ORM::factory('user')->with('profile')->with('profile:file')->get_user(array(rand(1,100), rand(1,100))); } Profiler::stop($token); echo View::factory('profiler/stats'); 

Without filling in the $ _table_columns arrays, on average, the entire work of the framework takes 0.18 seconds, of which 0.015 seconds. on queries to the database to fill in arrays with table fields. Hence the increase is smaller - 2.8%

Of course, in a real project, the numbers will greatly depend on the code itself and the work with ORM. The expected decrease in the number of queries to the database is 1.5 - 3 times in a project using an ORM, which will greatly relieve the MySQL server. But the same requests are repeated and MySQL is cached - so a specific speed increase will be around 2-3%.

There is one clear solution minus - on a project that works on a live server and is being actively developed in parallel - you need to add each new field to the $ _table_columns array manually, and for the new tables to generate the entire array.

PS Co-author of the article - unix44 , who is not greedy - can give an invite.