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 of a model, ORM must know information about all fields of the corresponding database table. Which leads to unwanted database queries.

Problem


When creating a model object using ORM, the SHOW FULL COLUMNS FROM `tablename` query 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 () creates a new instance of the object each time and, therefore, calls several methods in a row using the construct
image


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

generates 2 identical SHOW FULL COLUMNS FROM queries (even if $ _table_columns is not needed at all in a particular case). Also loading related models generates queries for each of the models - a call to ORM :: factory ('user') -> with ('profile') -> with ('photo') . Every second query to the database in the project (actively using ORM) comes out - 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 (naturally at the end of the project). Trying to fill it in manually for dozens of large tables is like shooting yourself in the leg. Therefore, in a few hours, a universal solution was found - to write the Optimize class, which recursively traverses the models 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 a slightly redone “native” method Debug :: vars ();
The code of the class itself is under the spoiler

watch class code Optimize
class Optimize{
    private static $files = array();
    /**
     * Returns database tables columns list
     * 
     * @uses find_models()
     * @uses _dump_simple()
     */
    public static function list_columns()
    {
        $dir = APPPATH . "classes/model";
        self::find_models($dir);
        foreach (self::$files as $model) {
            $file_text = file_get_contents($model);
            if(preg_match('/extends +ORM/i', $file_text) && !preg_match('/_table_columns/i', $file_text)){
                preg_match("/(class\sModel_)(\w+)?(\sextends)/", $file_text, $match);
                $model_name = preg_replace("/(class\sModel_)(.*?)(\sextends)/", "$2", $match[0]);
                echo '

Model_'.ucfirst($model_name).'

'; $columns[] = ORM::factory(strtolower($model_name))->list_columns(); $output = array(); foreach ($columns as $var) { $output[] = self::_dump_simple($var, 1024); } echo '
protected $_table_columns = ' . substr(implode("\n", $output), 0, -1) . ';
'; echo '========================================================'; } } } public static function find_models($in_dir) { if (preg_match("/_vti[.]*/i", $in_dir)) { return; } if ($dir_handle = @opendir($in_dir)) { while ($file = readdir($dir_handle)) { $path = $in_dir . "/" . $file; if ($file != ".." && $file != "." && is_dir($path) && $file != '.svn') { self::find_models($path); } if (is_file($path) && $file != ".." && $file != "." && strtolower(substr(strrchr($path, '.'), 1))=='php') { self::$files[] = $path; } } } } protected static function _dump_simple(& $var, $length = 128, $limit = 10, $level = 0) { if ($var === NULL) { return 'NULL,'; } elseif (is_bool($var)) { return ($var ? 'TRUE' : 'FALSE') . ','; } elseif (is_float($var)) { return $var . ','; } elseif (is_string($var)) { return "'" . $var . "',"; } elseif (is_array($var)) { $output = array(); $space = str_repeat($s = ' ', $level); static $marker; if ($marker === NULL) { $marker = uniqid("\x00"); } if ($level < $limit) { $output[] = "array("; $var[$marker] = TRUE; foreach ($var as $key => & $val) { if ($level == 1 && !in_array($key, array('type', 'is_nullable'))) continue; if ($key === $marker) continue; if (!is_int($key)) { $key = "'" . htmlspecialchars($key, ENT_NOQUOTES, Kohana::$charset) . "'"; } $output[] = "$space$s$key => " . self::_dump_simple($val, $length, $limit, $level + 1); } unset($var[$marker]); $output[] = "$space),"; } return implode("\n", $output); } else { return htmlspecialchars(print_r($var, TRUE), ENT_NOQUOTES, Kohana::$charset) . ','; } } } // End Optimize



It was decided not to do an array of fields with fields automatically in the model class code - you still can’t guess with 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 (hosted in /application/classes/optimize.php). Call a method from anywhere:
 echo Optimize::list_columns();

The proof of the method is the lack of last_query in the printed object of the model. Other solutions found - blogocms.ru/2011/05/kohana-uskoryaem-orm - caching the table structure. A simpler solution, but also less optimal in speed.
image



Profiling and tests


An attempt was made to measure speed (I do not pretend to measure accuracy). Write a small 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 the $ _table_columns arrays on average, the whole work of the framework takes 0.15 seconds, of which 0.005 seconds. to SHOW FULL COLUMNS FROM requests.
With filled $ _table_columns - an average of 0.145 seconds. Growth 3.3%

We will write a more real test with a selection 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 the $ _table_columns arrays , an average of 0.18 seconds is spent on the whole framework operation, of which 0.015 seconds. queries to the database to populate arrays with table fields. Therefore, the growth is smaller - 2.8%

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

There is one obvious minus of the solution - 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 generate the entire array for new tables.

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

Also popular now: