Tag Archives: doctrine

optimization-icon

Doctrine 1.2 (Symfony 1.4) performance tips & tricks

Sure Doctrine is awesome, but sometimes (every time?), it is also a memory killer. Hopefully there are some handy tricks that can be used on several occasions. Here are some Doctrine 1.2 performance tips & tricks I’ve collected over the time working with Symfony 1.4. Hope they’re useful to you guys!

If you know more performance tips, please leave them in the comments.

 

 

Tip #1

Create a task environment to run tasks and disable the profiler on databases.yml:

1
2
3
4
task:
  doctrine
:
    param
:
      profiler
: false

 

Tip #2

Enable Doctrine’s auto free query objects

1
Doctrine_Manager::connection()->setAttribute(Doctrine_Core::ATTR_AUTO_FREE_QUERY_OBJECTS, true);

 

Tip #3

Free Doctrine objects when they are no longer needed

1
2
$doctrine_object->free(true);
$doctrine_object = null;

 

Tip #4

Force Doctrine connection clear after performing needed Doctrine operations

1
2
3
4
5
6
7
Doctrine_Manager::connection()->connect();

// code code ...
$doctrine_object = Doctrine_Query::create()->from('Modal a')->limit(1)->fetchOne();
// code code...

Doctrine_Manager::connection()->clear();

 

Tip #5

Hydrate array when Model objects are not needed.

1
$doctrine_object = Doctrine_Query::create()->from('Modal a')->limit(1)->execute(array(), Doctrine_Core::HYDRATE_ARRAY);

 

Tip #6

Enable APC caching on Doctrine queries.

1
2
Doctrine_Manager::connection()->setAttribute(Doctrine::ATTR_QUERY_CACHE, new Doctrine_Cache_Apc(array('prefix' => 'fancyapcprefix_')));
Doctrine_Manager::connection()->setAttribute(Doctrine_Core::ATTR_RESULT_CACHE, new Doctrine_Cache_Apc(array('prefix' => 'fancyapcprefix_')));

 

Tip #7

Perform bulk inserts instead of one-by-one inserts.

1
2
3
4
5
6
7
8
9
10
11
$record1 = new Model()
$record1->setName('Example #1');
$record2 = new Model()
$record2->setName('Example #2');

$col = new Doctrine_Collection('Model');
$col->add($record1);
$col->add($record2);
$col->add($record3);
$col->add($recordN);
$col->save();

from this post

 

You can read more on Doctrine 1.2 performance here, here, here and here.

sfDoctrineGuardPlugin is evil (on delete cascade)

It happened once, a while ago and it happened again… We’ve deleted a user on the sf_guard_user table and ALL records from ALL tables with that user’s ID (on created_by / updated_by field), got dropped!

Some wise guy thought it made sense to delete all user’s related entries when you delete the user :S

Heres how to change that weird behaviour. On all your schema models, change the old “Signable: ~”, to:

1
2
3
4
5
6
ActAs:
  Signable:
    created:
      onDelete: set null
    updated:
      onDelete: set null

Form choice filter auto sort by name

The following snippet will auto-sort all sfWidgetFormDoctrineChoice elements on symfony filters.

On BaseFormFilterDoctrine.class.php:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
abstract class BaseFormFilterDoctrine extends sfFormFilterDoctrine
{

    public function setup()
    {
        $guesses = array('name', 'title', 'description', 'subject', 'keywords', 'id');

        foreach ($this->getWidgetSchema()->getFields() as $f)
        {
            if ($f instanceof sfWidgetFormDoctrineChoice)
            {
                $model = $f->getOption('model');
                $table = Doctrine::getTable($model);
                $order_by = null;
                foreach ($guesses as $descriptionColumn)
                {
                    if ($table->hasColumn($descriptionColumn))
                    {
                        $order_by = $descriptionColumn;
                        break;
                    }
                }
               
                if (isset($order_by) && $order_by)
                {
                    $f->setOption('order_by', array($order_by, 'asc'));
                }
            }
           
        }

    }

}

Debugging doctrine:data-load

I know it drives you MAD when you try to load a bunch of features and get an error like:

1
SQLSTATE[23000]: Integrity CONSTRAINT violation: 1062 Duplicate entry '16' FOR KEY 'PRIMARY'

Luckily there’s an undocumented argument on doctrine’s data-load task:

–trace

1
2
3
4
5
6
7
8
9
10
$ php symfony doctrine:data-load --trace

>> doctrine Loading data fixtures from "/home/bla/public_html/site/data/fixtures"
>> doctrine Loading data fixtures from "/home/bla/public_html/site/plugins/sfDoctrineGuardPlugin/data/fixtures"
>> Doctrine_Connection_Mysql exec : SET NAMES 'UTF8' - ()
>> Doctrine_Connection_Mysql exec : DELETE FROM table_a - ()
>> Doctrine_Connection_Mysql exec : DELETE FROM table_b - ()
>> Doctrine_Connection_Mysql exec : DELETE FROM sf_guard_user - ()
>> Doctrine_Connection_Statement execute : INSERT INTO sf_guard_user (algorithm, is_active, is_super_admin, first_name, last_name, email_address, username, salt, password, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) - (sha1, 1, 1, bla, blah, blah@adclick.pt, blah, blah48ca1e4247258a0ec29f9d1aac8, blah32ed46c3a74deebb071456114e4406eba42e, 2012-01-12 18:07:02, 2012-01-12 18:07:02)
...

Criar schema.yml a partir do workbench

Para quem utiliza a ferramenta Workbench para desenhar os modelos da base de dados, e depois não quer ter o trabalho de passar os nomes todos para o schema.yml, pode tirar partido desta script.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
<?php
/*
 *  The MIT License
 *
 *  Copyright (c) 2010 Johannes Mueller <circus2(at)web.de>
 *
 *  Permission is hereby granted, free of charge, to any person obtaining a copy
 *  of this software and associated documentation files (the "Software"), to deal
 *  in the Software without restriction, including without limitation the rights
 *  to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
 *  copies of the Software, and to permit persons to whom the Software is
 *  furnished to do so, subject to the following conditions:
 *
 *  The above copyright notice and this permission notice shall be included in
 *  all copies or substantial portions of the Software.
 *
 *  THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
 *  IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
 *  FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
 *  AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
 *  LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
 *  OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
 *  THE SOFTWARE.
 */


// show errors
error_reporting(E_ALL);

// lets stop the time
$start = microtime(true);


// enable autoloading of classes
require_once('../lib/MwbExporter/Core/SplClassLoader.php');
$classLoader = new SplClassLoader();
$classLoader->setIncludePath('../lib');
$classLoader->register();

// show a simple text box with the output
echo '<textarea cols="100" rows="50">';

    // configure your output
    $setup = array(
        'extendTableNameWithSchemaName' => true
    );

    // create a formatter
    $formatter = new \MwbExporter\Formatter\Doctrine1\Yaml\Loader($setup);
   
    // parse the mwb file
    $mwb = new \MwbExporter\Core\Workbench\Document('data/er.mwb', $formatter);
   
    // show the export output of the mwb file
    echo $mwb->display();
 
echo "</textarea>";

// save as zip file in current directory and use .yml as file endings
echo "<br><br>";
echo $mwb->zipExport(__DIR__, 'yml');

// show some information about used memory
echo "<br><br>";
echo (memory_get_peak_usage(true) / 1024 / 1024) . " MB used";
echo "<br>";

// show the time needed to parse the mwb file
$end = microtime(true);
echo  sprintf('%0.3f', $end-$start) . " sec needed";

Atenção: Não dispensa verificação do código gerado!!!

Links úteis:
Workbench: http://www.mysql.com/downloads/workbench/
Repositório do projecto: git://github.com/johmue/mysql-workbench-schema-exporter.git

Obrigado ao johmue. :P