Skip to content

Instantly share code, notes, and snippets.

@andronex
Last active August 20, 2020 11:28

Revisions

  1. andronex revised this gist May 14, 2015. 1 changed file with 87 additions and 53 deletions.
    140 changes: 87 additions & 53 deletions exampleQueries
    Original file line number Diff line number Diff line change
    @@ -1,27 +1,11 @@
    Все примеры расположены в порядке уменьшения нагрузки на БД и при равенстве кол-ва запросов в порядке уменьшения кол-ва строк кода. Сверху самый ресурсоёмкий по кол-ву запросов к БД.
    ///////////////////////////////////////////////////////////
    Пустая страница MODX при отключенном кэше:
    кол-во зпросов/время запросов/время выполнения PHP кода/общее время
    6/0.0002 s/0.0336 s/0.0338 s
    ***********************************************************
    1-ый код с pdoFetch, избранными методами:
    $pdo = $modx->getService('pdoFetch');
    $pdo->setConfig(array(
    'class' => 'modResource',
    'where' => 'modResource.id = 200',
    'select' => 'id,pagetitle'
    ));
    $pdo->makeQuery();
    $pdo->addSelects();
    $pdo->addWhere();
    $query = $pdo->prepareQuery();
    $query->execute();
    $res = $query->fetch(PDO::FETCH_ASSOC);
    print_r($res);
    1-ый код с pdoFetch, все методы:

    Array ( [id] => 200 [pagetitle] => test 196 )
    9 699 328: Memory usage
    8/0.0004 s/0.0340 s/0.0344 s
    ***********************************************************
    2-ой код с pdoFetch, все методы:
    $pdo = $modx->getService('pdoFetch');
    $pdo->setConfig(array(
    'class' => 'modResource',
    @@ -31,56 +15,65 @@ $pdo->setConfig(array(
    $res = $pdo->run();
    print_r($res);

    Вывод:
    Array
    (
    [id] => 200
    [pagetitle] => test 196
    [idx] => 1
    [link] =>
    )
    Кол-во запросов и время:
    9 699 328: Memory usage
    11/0.0008 s/0.0334 s/0.0342 s
    ***********************************************************
    3-ий код с pdoFetch, getArray:
    $pdo = $modx->getService('pdoFetch');
    $res = $pdo->getArray('modResource', '', array('class' => 'modResource',
    'where' => 'modResource.id = 200',
    'select' => 'id,pagetitle'));
    print_r($res);
    2-ой код без pdoFetch, без $o->_lazy = array() с getObject:

    Array ( [id] => 200 [pagetitle] => test 196 )
    0.0008011: Total time
    9 699 328: Memory usage
    10/0.0013 s/0.0339 s/0.0352 s
    $q = $modx->newQuery('modResource');
    $q->select('modResource.id,modResource.pagetitle');
    $q->where(array(
    'id' => 200
    ));
    if($o = $modx->getObject('modResource', $q)){
    //$o->_lazy = array();
    print_r($o->toArray());
    }

    Вывод:
    Array ( [id] => 200 [type] => document [contentType] => text/html [pagetitle] => test 196 [longtitle] => [description] => [alias] => test-196 [link_attributes] => [published] => 1 [pub_date] => 0 [unpub_date] => 0 [parent] => 0 [isfolder] => [introtext] => [content] => [richtext] => 1 [template] => 1 [menuindex] => 199 [searchable] => 1 [cacheable] => 1 [createdby] => 1 [createdon] => 2015-05-14 15:48:23 [editedby] => 0 [editedon] => 0 [deleted] => [deletedon] => 0 [deletedby] => 0 [publishedon] => 2015-05-14 15:48:23 [publishedby] => 1 [menutitle] => [donthit] => [privateweb] => [privatemgr] => [content_dispo] => 0 [hidemenu] => [class_key] => modDocument [context_key] => web [content_type] => 1 [uri] => [uri_override] => 0 [hide_children_in_tree] => 0 [show_in_tree] => 1 [properties] => )
    Кол-во запросов и время:
    10/0.0005 s/0.0336 s/0.0341 s
    ***********************************************************
    4-ый код с pdoFetch, getCollection:
    3-ий код с pdoFetch, getCollection:

    $pdo = $modx->getService('pdoFetch');
    $res = $pdo->getCollection('modResource', '', array('class' => 'modResource',
    'where' => 'modResource.id = 200',
    'select' => 'id,pagetitle'));

    print_r($res);

    Вывод:
    Array ( [0] => Array ( [id] => 200 [pagetitle] => test 196 ) )
    0.0006490: Total time
    Кол-во запросов и время:
    9 699 328: Memory usage
    10/0.0004 s/0.0321 s/0.0325 s
    ***********************************************************
    5-ый код без pdoFetch, prepare execute:
    $q = $modx->newQuery('modResource');
    $q->select('modResource.id,modResource.pagetitle');
    $q->where(array(
    'id' => 200
    ));
    4-ый код с pdoFetch, getArray:

    $q->prepare();
    $q->stmt->execute();
    $result = $q->stmt->fetch(PDO::FETCH_ASSOC);
    print_r($result);
    $pdo = $modx->getService('pdoFetch');
    $res = $pdo->getArray('modResource', '', array('class' => 'modResource',
    'where' => 'modResource.id = 200',
    'select' => 'id,pagetitle'));
    print_r($res);

    Array ( [id] => 200 [pagetitle] => test 196 ) 8/0.0007 s/0.0331 s/0.0338 s
    Вывод:
    Array ( [id] => 200 [pagetitle] => test 196 )
    Кол-во запросов и время:
    9 699 328: Memory usage
    10/0.0013 s/0.0339 s/0.0352 s
    ***********************************************************
    6-ой код без pdoFetch, getObject и $o->_lazy = array():
    5-ый код без pdoFetch, getObject и $o->_lazy = array():

    $q = $modx->newQuery('modResource');
    $q->select('modResource.id,modResource.pagetitle');
    $q->where(array(
    @@ -91,26 +84,67 @@ if($o = $modx->getObject('modResource', $q)){
    print_r($o->toArray());
    }

    Array ( [id] => 200 [type] => document [contentType] => text/html [pagetitle] => test 196 [longtitle] => [description] => [alias] => [link_attributes] => [published] => [pub_date] => 0 [unpub_date] => 0 [parent] => 0 [isfolder] => [introtext] => [content] => [richtext] => 1 [template] => 0 [menuindex] => 0 [searchable] => 1 [cacheable] => 1 [createdby] => 0 [createdon] => 0 [editedby] => 0 [editedon] => 0 [deleted] => [deletedon] => 0 [deletedby] => 0 [publishedon] => 0 [publishedby] => 0 [menutitle] => [donthit] => [privateweb] => [privatemgr] => [content_dispo] => 0 [hidemenu] => [class_key] => modDocument [context_key] => web [content_type] => 1 [uri] => [uri_override] => 0 [hide_children_in_tree] => 0 [show_in_tree] => 1 [properties] => ) 9/0.0005 s/0.0316 s/0.0321 s
    Вывод:
    Array ( [id] => 200 [type] => document [contentType] => text/html [pagetitle] => test 196 [longtitle] => [description] => [alias] => [link_attributes] => [published] => [pub_date] => 0 [unpub_date] => 0 [parent] => 0 [isfolder] => [introtext] => [content] => [richtext] => 1 [template] => 0 [menuindex] => 0 [searchable] => 1 [cacheable] => 1 [createdby] => 0 [createdon] => 0 [editedby] => 0 [editedon] => 0 [deleted] => [deletedon] => 0 [deletedby] => 0 [publishedon] => 0 [publishedby] => 0 [menutitle] => [donthit] => [privateweb] => [privatemgr] => [content_dispo] => 0 [hidemenu] => [class_key] => modDocument [context_key] => web [content_type] => 1 [uri] => [uri_override] => 0 [hide_children_in_tree] => 0 [show_in_tree] => 1 [properties] => )
    Кол-во запросов и время:
    9/0.0005 s/0.0316 s/0.0321 s
    ***********************************************************
    7-ой код без pdoFetch, без $o->_lazy = array() с getObject:
    6-ой код с pdoFetch, избранными методами без pdoFetch::Run():

    $pdo = $modx->getService('pdoFetch');
    $pdo->setConfig(array(
    'class' => 'modResource',
    'where' => 'modResource.id = 200',
    'select' => 'id,pagetitle'
    ));
    $pdo->makeQuery();
    $pdo->addSelects();
    $pdo->addWhere();
    $query = $pdo->prepareQuery();
    $query->execute();
    $res = $query->fetch(PDO::FETCH_ASSOC);
    print_r($res);

    Вывод:
    Array ( [id] => 200 [pagetitle] => test 196 )
    Кол-во запросов и время:
    9 699 328: Memory usage
    8/0.0004 s/0.0340 s/0.0344 s
    ***********************************************************
    7-ой код без pdoFetch, prepare и execute:

    $q = $modx->newQuery('modResource');
    $q->select('modResource.id,modResource.pagetitle');
    $q->where(array(
    'id' => 200
    ));
    if($o = $modx->getObject('modResource', $q)){
    //$o->_lazy = array();
    print_r($o->toArray());
    }
    $q->prepare();
    $q->stmt->execute();
    $result = $q->stmt->fetch(PDO::FETCH_ASSOC);
    print_r($result);

    Array ( [id] => 200 [type] => document [contentType] => text/html [pagetitle] => test 196 [longtitle] => [description] => [alias] => test-196 [link_attributes] => [published] => 1 [pub_date] => 0 [unpub_date] => 0 [parent] => 0 [isfolder] => [introtext] => [content] => [richtext] => 1 [template] => 1 [menuindex] => 199 [searchable] => 1 [cacheable] => 1 [createdby] => 1 [createdon] => 2015-05-14 15:48:23 [editedby] => 0 [editedon] => 0 [deleted] => [deletedon] => 0 [deletedby] => 0 [publishedon] => 2015-05-14 15:48:23 [publishedby] => 1 [menutitle] => [donthit] => [privateweb] => [privatemgr] => [content_dispo] => 0 [hidemenu] => [class_key] => modDocument [context_key] => web [content_type] => 1 [uri] => [uri_override] => 0 [hide_children_in_tree] => 0 [show_in_tree] => 1 [properties] => ) 10/0.0005 s/0.0336 s/0.0341 s
    Вывод:
    Array ( [id] => 200 [pagetitle] => test 196 )
    Кол-во запросов и время:
    8/0.0007 s/0.0331 s/0.0338 s
    ***********************************************************
    8-ой код, чистый PDO с SQL кодом:
    8-ой код, чистый PDO с SQL запросом непосредственно в скрипте:

    $q = 'SELECT `modResource`.`id`, `modResource`.`pagetitle` FROM `modx_site_content` AS `modResource` WHERE `modResource`.`id` = 200';
    $o = $modx->prepare($q);
    $o->execute();
    $res = $o->fetch(PDO::FETCH_ASSOC);
    print_r($res);

    Array ( [id] => 200 [pagetitle] => test 196 ) 8/0.0005 s/0.0295 s/0.0300 s
    Вывод:
    Array ( [id] => 200 [pagetitle] => test 196 )
    Кол-во запросов и время:
    8/0.0005 s/0.0295 s/0.0300 s
    ///////////////////////////////////////////////////////////
    Вывод: желательно использовать последние три способа выборки. Последний всё же не рекомендуется, но вполне возможно на "скорую руку".

    Для изучения:
    1. http://community.modx-cms.ru/blog/modx-xpdo/9693.html
    2. https://bezumkin.ru/training/course2/3006/
    3. https://modx.pro/help/5268/#comment-37676
    4. http://habrahabr.ru/post/152123/
  2. andronex created this gist May 14, 2015.
    116 changes: 116 additions & 0 deletions exampleQueries
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,116 @@
    Пустая страница MODX при отключенном кэше:
    кол-во зпросов/время запросов/время выполнения PHP кода/общее время
    6/0.0002 s/0.0336 s/0.0338 s
    ***********************************************************
    1-ый код с pdoFetch, избранными методами:
    $pdo = $modx->getService('pdoFetch');
    $pdo->setConfig(array(
    'class' => 'modResource',
    'where' => 'modResource.id = 200',
    'select' => 'id,pagetitle'
    ));
    $pdo->makeQuery();
    $pdo->addSelects();
    $pdo->addWhere();
    $query = $pdo->prepareQuery();
    $query->execute();
    $res = $query->fetch(PDO::FETCH_ASSOC);
    print_r($res);

    Array ( [id] => 200 [pagetitle] => test 196 )
    9 699 328: Memory usage
    8/0.0004 s/0.0340 s/0.0344 s
    ***********************************************************
    2-ой код с pdoFetch, все методы:
    $pdo = $modx->getService('pdoFetch');
    $pdo->setConfig(array(
    'class' => 'modResource',
    'where' => 'modResource.id = 200',
    'select' => 'id,pagetitle'
    ));
    $res = $pdo->run();
    print_r($res);

    Array
    (
    [id] => 200
    [pagetitle] => test 196
    [idx] => 1
    [link] =>
    )
    9 699 328: Memory usage
    11/0.0008 s/0.0334 s/0.0342 s
    ***********************************************************
    3-ий код с pdoFetch, getArray:
    $pdo = $modx->getService('pdoFetch');
    $res = $pdo->getArray('modResource', '', array('class' => 'modResource',
    'where' => 'modResource.id = 200',
    'select' => 'id,pagetitle'));
    print_r($res);

    Array ( [id] => 200 [pagetitle] => test 196 )
    0.0008011: Total time
    9 699 328: Memory usage
    10/0.0013 s/0.0339 s/0.0352 s
    ***********************************************************
    4-ый код с pdoFetch, getCollection:
    $pdo = $modx->getService('pdoFetch');
    $res = $pdo->getCollection('modResource', '', array('class' => 'modResource',
    'where' => 'modResource.id = 200',
    'select' => 'id,pagetitle'));

    print_r($res);

    Array ( [0] => Array ( [id] => 200 [pagetitle] => test 196 ) )
    0.0006490: Total time
    9 699 328: Memory usage
    10/0.0004 s/0.0321 s/0.0325 s
    ***********************************************************
    5-ый код без pdoFetch, prepare execute:
    $q = $modx->newQuery('modResource');
    $q->select('modResource.id,modResource.pagetitle');
    $q->where(array(
    'id' => 200
    ));

    $q->prepare();
    $q->stmt->execute();
    $result = $q->stmt->fetch(PDO::FETCH_ASSOC);
    print_r($result);

    Array ( [id] => 200 [pagetitle] => test 196 ) 8/0.0007 s/0.0331 s/0.0338 s
    ***********************************************************
    6-ой код без pdoFetch, getObject и $o->_lazy = array():
    $q = $modx->newQuery('modResource');
    $q->select('modResource.id,modResource.pagetitle');
    $q->where(array(
    'id' => 200
    ));
    if($o = $modx->getObject('modResource', $q)){
    $o->_lazy = array();
    print_r($o->toArray());
    }

    Array ( [id] => 200 [type] => document [contentType] => text/html [pagetitle] => test 196 [longtitle] => [description] => [alias] => [link_attributes] => [published] => [pub_date] => 0 [unpub_date] => 0 [parent] => 0 [isfolder] => [introtext] => [content] => [richtext] => 1 [template] => 0 [menuindex] => 0 [searchable] => 1 [cacheable] => 1 [createdby] => 0 [createdon] => 0 [editedby] => 0 [editedon] => 0 [deleted] => [deletedon] => 0 [deletedby] => 0 [publishedon] => 0 [publishedby] => 0 [menutitle] => [donthit] => [privateweb] => [privatemgr] => [content_dispo] => 0 [hidemenu] => [class_key] => modDocument [context_key] => web [content_type] => 1 [uri] => [uri_override] => 0 [hide_children_in_tree] => 0 [show_in_tree] => 1 [properties] => ) 9/0.0005 s/0.0316 s/0.0321 s
    ***********************************************************
    7-ой код без pdoFetch, без $o->_lazy = array() с getObject:
    $q = $modx->newQuery('modResource');
    $q->select('modResource.id,modResource.pagetitle');
    $q->where(array(
    'id' => 200
    ));
    if($o = $modx->getObject('modResource', $q)){
    //$o->_lazy = array();
    print_r($o->toArray());
    }

    Array ( [id] => 200 [type] => document [contentType] => text/html [pagetitle] => test 196 [longtitle] => [description] => [alias] => test-196 [link_attributes] => [published] => 1 [pub_date] => 0 [unpub_date] => 0 [parent] => 0 [isfolder] => [introtext] => [content] => [richtext] => 1 [template] => 1 [menuindex] => 199 [searchable] => 1 [cacheable] => 1 [createdby] => 1 [createdon] => 2015-05-14 15:48:23 [editedby] => 0 [editedon] => 0 [deleted] => [deletedon] => 0 [deletedby] => 0 [publishedon] => 2015-05-14 15:48:23 [publishedby] => 1 [menutitle] => [donthit] => [privateweb] => [privatemgr] => [content_dispo] => 0 [hidemenu] => [class_key] => modDocument [context_key] => web [content_type] => 1 [uri] => [uri_override] => 0 [hide_children_in_tree] => 0 [show_in_tree] => 1 [properties] => ) 10/0.0005 s/0.0336 s/0.0341 s
    ***********************************************************
    8-ой код, чистый PDO с SQL кодом:
    $q = 'SELECT `modResource`.`id`, `modResource`.`pagetitle` FROM `modx_site_content` AS `modResource` WHERE `modResource`.`id` = 200';
    $o = $modx->prepare($q);
    $o->execute();
    $res = $o->fetch(PDO::FETCH_ASSOC);
    print_r($res);

    Array ( [id] => 200 [pagetitle] => test 196 ) 8/0.0005 s/0.0295 s/0.0300 s