Zend Framework 2 tablegateway making queries slower

0
votes

I am using zend framework 2 for a php project.

I have a function that runs almost 300 queries at time, and I am making this queries using zf2 tablegateway. My main problem is that it is taking almost 120 seconds to complete this action. After making a profiling with Byprofiler and Zenddeveloper tool, and extracing all queries, I test queries as standalone and all them takes 0.5s to complete, so my conclusion is that tablegateway or zf2 is adding a overload of 119 seconds.

What is happening, how can I avoid this? Any suggestion on using zf2?

EDIT Here are the query set:

SELECT `id` FROM `custom_field` WHERE `name` = 'color_id' AND `container_class` = 'product';
SELECT `id` FROM `custom_field` WHERE `name` = 'size_label1' AND `container_class` = 'product';
SELECT `link_id` FROM `container_link` WHERE `container_id` = 70127 AND `reason` = 'product_main_text';
SELECT `value` FROM `custom_field_int` WHERE `container_id` = 70128 AND `custom_field_id` = 334;
SELECT `value` FROM `custom_field_string` WHERE `container_id` = 70128 AND `custom_field_id` = 397;
SELECT `id` FROM `custom_field` WHERE `name` = 'color_id' AND `container_class` = 'product';
SELECT `id` FROM `custom_field` WHERE `name` = 'size_label1' AND `container_class` = 'product';
SELECT `link_id` FROM `container_link` WHERE `container_id` = 69917 AND `reason` = 'product_main_text';
SELECT `value` FROM `custom_field_int` WHERE `container_id` = 69918 AND `custom_field_id` = 334;
SELECT `value` FROM `custom_field_string` WHERE `container_id` = 69918 AND `custom_field_id` = 397;
SELECT `link_id` FROM `container_link` WHERE `container_id` = 69939 AND `reason` = 'product_main_text';
SELECT `value` FROM `custom_field_int` WHERE `container_id` = 69940 AND `custom_field_id` = 334;
SELECT `value` FROM `custom_field_string` WHERE `container_id` = 69940 AND `custom_field_id` = 397;
SELECT `link_id` FROM `container_link` WHERE `container_id` = 69925 AND `reason` = 'product_main_text';
SELECT `value` FROM `custom_field_int` WHERE `container_id` = 69926 AND `custom_field_id` = 334;
SELECT `value` FROM `custom_field_string` WHERE `container_id` = 69926 AND `custom_field_id` = 397;
SELECT `link_id` FROM `container_link` WHERE `container_id` = 69947 AND `reason` = 'product_main_text';
 SELECT `value` FROM `custom_field_int` WHERE `container_id` = 69948 AND `custom_field_id` = 334;
 SELECT `value` FROM `custom_field_string` WHERE `container_id` = 69948 AND `custom_field_id` = 397;
 SELECT `link_id` FROM `container_link` WHERE `container_id` = 69955 AND `reason` = 'product_main_text';
 SELECT `value` FROM `custom_field_int` WHERE `container_id` = 69956 AND `custom_field_id` = 334;
 SELECT `value` FROM `custom_field_string` WHERE `container_id` = 69956 AND `custom_field_id` = 397;
 SELECT `link_id` FROM `container_link` WHERE `container_id` = 69933 AND `reason` = 'product_main_text';
 SELECT `value` FROM `custom_field_int` WHERE `container_id` = 69934 AND `custom_field_id` = 334;
 SELECT `value` FROM `custom_field_string` WHERE `container_id` = 69934 AND `custom_field_id` = 397;
 SELECT `link_id` FROM `container_link` WHERE `container_id` = 69923 AND `reason` = 'product_main_text';
 SELECT `value` FROM `custom_field_int` WHERE `container_id` = 69924 AND `custom_field_id` = 334;
 SELECT `value` FROM `custom_field_string` WHERE `container_id` = 69924 AND `custom_field_id` = 397;
 SELECT `link_id` FROM `container_link` WHERE `container_id` = 69945 AND `reason` = 'product_main_text';
 SELECT `value` FROM `custom_field_int` WHERE `container_id` = 69946 AND `custom_field_id` = 334;
 SELECT `value` FROM `custom_field_string` WHERE `container_id` = 69946 AND `custom_field_id` = 397;
 SELECT `link_id` FROM `container_link` WHERE `container_id` = 69931 AND `reason` = 'product_main_text';
 SELECT `value` FROM `custom_field_int` WHERE `container_id` = 69932 AND `custom_field_id` = 334;
 SELECT `value` FROM `custom_field_string` WHERE `container_id` = 69932 AND `custom_field_id` = 397;
 SELECT `link_id` FROM `container_link` WHERE `container_id` = 69953 AND `reason` = 'product_main_text'; 
 SELECT `value` FROM `custom_field_int` WHERE `container_id` = 69954 AND `custom_field_id` = 334;
 SELECT `value` FROM `custom_field_string` WHERE `container_id` = 69954 AND `custom_field_id` = 397;
 SELECT `link_id` FROM `container_link` WHERE `container_id` = 69921 AND `reason` = 'product_main_text'; 
 SELECT `value` FROM `custom_field_int` WHERE `container_id` = 69922 AND `custom_field_id` = 334; 
 SELECT `value` FROM `custom_field_string` WHERE `container_id` = 69922 AND `custom_field_id` = 397; 
 SELECT `link_id` FROM `container_link` WHERE `container_id` = 69943 AND `reason` = 'product_main_text';
 SELECT `value` FROM `custom_field_int` WHERE `container_id` = 69944 AND `custom_field_id` = 334;
 SELECT `value` FROM `custom_field_string` WHERE `container_id` = 69944 AND `custom_field_id` = 397;
 SELECT `link_id` FROM `container_link` WHERE `container_id` = 69929 AND `reason` = 'product_main_text';
 SELECT `value` FROM `custom_field_int` WHERE `container_id` = 69930 AND `custom_field_id` = 334; 
 SELECT `value` FROM `custom_field_string` WHERE `container_id` = 69930 AND `custom_field_id` = 397;
 SELECT `link_id` FROM `container_link` WHERE `container_id` = 69951 AND `reason` = 'product_main_text';
 SELECT `value` FROM `custom_field_int` WHERE `container_id` = 69952 AND `custom_field_id` = 334;
 SELECT `value` FROM `custom_field_string` WHERE `container_id` = 69952 AND `custom_field_id` = 397; 
 SELECT `link_id` FROM `container_link` WHERE `container_id` = 69937 AND `reason` = 'product_main_text';
 SELECT `value` FROM `custom_field_int` WHERE `container_id` = 69938 AND `custom_field_id` = 334;
 SELECT `value` FROM `custom_field_string` WHERE `container_id` = 69938 AND `custom_field_id` = 397; 
 SELECT `link_id` FROM `container_link` WHERE `container_id` = 69959 AND `reason` = 'product_main_text';
 SELECT `value` FROM `custom_field_int` WHERE `container_id` = 69960 AND `custom_field_id` = 334;
 SELECT `value` FROM `custom_field_string` WHERE `container_id` = 69960 AND `custom_field_id` = 397; 
 SELECT `link_id` FROM `container_link` WHERE `container_id` = 69915 AND `reason` = 'product_main_text'; 
 SELECT `value` FROM `custom_field_int` WHERE `container_id` = 69916 AND `custom_field_id` = 334;
 SELECT `value` FROM `custom_field_string` WHERE `container_id` = 69916 AND `custom_field_id` = 397; 
 SELECT `link_id` FROM `container_link` WHERE `container_id` = 69919 AND `reason` = 'product_main_text';
 SELECT `value` FROM `custom_field_int` WHERE `container_id` = 69920 AND `custom_field_id` = 334;
 SELECT `value` FROM `custom_field_string` WHERE `container_id` = 69920 AND `custom_field_id` = 397; 
 SELECT `link_id` FROM `container_link` WHERE `container_id` = 69941 AND `reason` = 'product_main_text'; 
 SELECT `value` FROM `custom_field_int` WHERE `container_id` = 69942 AND `custom_field_id` = 334; 
 SELECT `value` FROM `custom_field_string` WHERE `container_id` = 69942 AND `custom_field_id` = 397;
 SELECT `link_id` FROM `container_link` WHERE `container_id` = 69927 AND `reason` = 'product_main_text'; 
 SELECT `value` FROM `custom_field_int` WHERE `container_id` = 69928 AND `custom_field_id` = 334;
 SELECT `value` FROM `custom_field_string` WHERE `container_id` = 69928 AND `custom_field_id` = 397; 
 SELECT `link_id` FROM `container_link` WHERE `container_id` = 69949 AND `reason` = 'product_main_text'; 
 SELECT `value` FROM `custom_field_int` WHERE `container_id` = 69950 AND `custom_field_id` = 334; 
 SELECT `value` FROM `custom_field_string` WHERE `container_id` = 69950 AND `custom_field_id` = 397; 
 SELECT `link_id` FROM `container_link` WHERE `container_id` = 69957 AND `reason` = 'product_main_text'; 
 SELECT `value` FROM `custom_field_int` WHERE `container_id` = 69958 AND `custom_field_id` = 334; 
 SELECT `value` FROM `custom_field_string` WHERE `container_id` = 69958 AND `custom_field_id` = 397; 
 SELECT `link_id` FROM `container_link` WHERE `container_id` = 69913 AND `reason` = 'product_main_text';
 SELECT `value` FROM `custom_field_int` WHERE `container_id` = 69914 AND `custom_field_id` = 334; 
 SELECT `value` FROM `custom_field_string` WHERE `container_id` = 69914 AND `custom_field_id` = 397; 
 SELECT `link_id` FROM `container_link` WHERE `container_id` = 69935 AND `reason` = 'product_main_text'; 
 SELECT `value` FROM `custom_field_int` WHERE `container_id` = 69936 AND `custom_field_id` = 334; 
 SELECT `value` FROM `custom_field_string` WHERE `container_id` = 69936 AND `custom_field_id` = 397;
 SELECT `id` FROM `custom_field` WHERE `name` = 'color_id' AND `container_class` = 'product'; 
 SELECT `id` FROM `custom_field` WHERE `name` = 'size_label1' AND `container_class` = 'product'; 
 SELECT `link_id` FROM `container_link` WHERE `container_id` = 70127 AND `reason` = 'product_main_text';
 SELECT `value` FROM `custom_field_int` WHERE `container_id` = 70128 AND `custom_field_id` = 334;
 SELECT `value` FROM `custom_field_string` WHERE `container_id` = 70128 AND `custom_field_id` = 397; 
 SELECT `id` FROM `custom_field` WHERE `name` = 'color_id' AND `container_class` = 'product'; 
 SELECT `id` FROM `custom_field` WHERE `name` = 'size_label1' AND `container_class` = 'product'; 
 SELECT `link_id` FROM `container_link` WHERE `container_id` = 70141 AND `reason` = 'product_main_text';
 SELECT `value` FROM `custom_field_int` WHERE `container_id` = 70142 AND `custom_field_id` = 334;
 SELECT `value` FROM `custom_field_string` WHERE `container_id` = 70142 AND `custom_field_id` = 397; 
 SELECT `link_id` FROM `container_link` WHERE `container_id` = 70147 AND `reason` = 'product_main_text'; 
 SELECT `value` FROM `custom_field_int` WHERE `container_id` = 70148 AND `custom_field_id` = 334; 
 SELECT `value` FROM `custom_field_string` WHERE `container_id` = 70148 AND `custom_field_id` = 397; 
 SELECT `link_id` FROM `container_link` WHERE `container_id` = 70169 AND `reason` = 'product_main_text'; 
 SELECT `value` FROM `custom_field_int` WHERE `container_id` = 70170 AND `custom_field_id` = 334;
 SELECT `value` FROM `custom_field_string` WHERE `container_id` = 70170 AND `custom_field_id` = 397; 
 SELECT `link_id` FROM `container_link` WHERE `container_id` = 70155 AND `reason` = 'product_main_text'; 
 SELECT `value` FROM `custom_field_int` WHERE `container_id` = 70156 AND `custom_field_id` = 334;
 SELECT `value` FROM `custom_field_string` WHERE `container_id` = 70156 AND `custom_field_id` = 397; 
 SELECT `link_id` FROM `container_link` WHERE `container_id` = 70163 AND `reason` = 'product_main_text'; 
 SELECT `value` FROM `custom_field_int` WHERE `container_id` = 70164 AND `custom_field_id` = 334; 
 SELECT `value` FROM `custom_field_string` WHERE `container_id` = 70164 AND `custom_field_id` = 397; 
 SELECT `link_id` FROM `container_link` WHERE `container_id` = 70153 AND `reason` = 'product_main_text'; 
 SELECT `value` FROM `custom_field_int` WHERE `container_id` = 70154 AND `custom_field_id` = 334; 
 SELECT `value` FROM `custom_field_string` WHERE `container_id` = 70154 AND `custom_field_id` = 397;
 SELECT `link_id` FROM `container_link` WHERE `container_id` = 70161 AND `reason` = 'product_main_text'; 
 SELECT `value` FROM `custom_field_int` WHERE `container_id` = 70162 AND `custom_field_id` = 334;
 SELECT `value` FROM `custom_field_string` WHERE `container_id` = 70162 AND `custom_field_id` = 397;
 SELECT `link_id` FROM `container_link` WHERE `container_id` = 70145 AND `reason` = 'product_main_text'; 
 SELECT `value` FROM `custom_field_int` WHERE `container_id` = 70146 AND `custom_field_id` = 334; 
 SELECT `value` FROM `custom_field_string` WHERE `container_id` = 70146 AND `custom_field_id` = 397;
 SELECT `link_id` FROM `container_link` WHERE `container_id` = 70151 AND `reason` = 'product_main_text'; 
 SELECT `value` FROM `custom_field_int` WHERE `container_id` = 70152 AND `custom_field_id` = 334; 
 SELECT `value` FROM `custom_field_string` WHERE `container_id` = 70152 AND `custom_field_id` = 397;
 SELECT `link_id` FROM `container_link` WHERE `container_id` = 70159 AND `reason` = 'product_main_text';
 SELECT `value` FROM `custom_field_int` WHERE `container_id` = 70160 AND `custom_field_id` = 334; 
 SELECT `value` FROM `custom_field_string` WHERE `container_id` = 70160 AND `custom_field_id` = 397; 
 SELECT `link_id` FROM `container_link` WHERE `container_id` = 70167 AND `reason` = 'product_main_text'; 
 SELECT `value` FROM `custom_field_int` WHERE `container_id` = 70168 AND `custom_field_id` = 334;
 SELECT `value` FROM `custom_field_string` WHERE `container_id` = 70168 AND `custom_field_id` = 397; 
 SELECT `link_id` FROM `container_link` WHERE `container_id` = 70143 AND `reason` = 'product_main_text';
 SELECT `value` FROM `custom_field_int` WHERE `container_id` = 70144 AND `custom_field_id` = 334; 
 SELECT `value` FROM `custom_field_string` WHERE `container_id` = 70144 AND `custom_field_id` = 397; 
 SELECT `link_id` FROM `container_link` WHERE `container_id` = 70149 AND `reason` = 'product_main_text'; 
 SELECT `value` FROM `custom_field_int` WHERE `container_id` = 70150 AND `custom_field_id` = 334;
 SELECT `value` FROM `custom_field_string` WHERE `container_id` = 70150 AND `custom_field_id` = 397; 
 SELECT `link_id` FROM `container_link` WHERE `container_id` = 70157 AND `reason` = 'product_main_text';
 SELECT `value` FROM `custom_field_int` WHERE `container_id` = 70158 AND `custom_field_id` = 334; 
 SELECT `value` FROM `custom_field_string` WHERE `container_id` = 70158 AND `custom_field_id` = 397; 
 SELECT `link_id` FROM `container_link` WHERE `container_id` = 70165 AND `reason` = 'product_main_text'; 
 SELECT `value` FROM `custom_field_int` WHERE `container_id` = 70166 AND `custom_field_id` = 334; 
 SELECT `value` FROM `custom_field_string` WHERE `container_id` = 70166 AND `custom_field_id` = 397; 
 SELECT `id` FROM `custom_field` WHERE `name` = 'color_id' AND `container_class` = 'product'; 
 SELECT `id` FROM `custom_field` WHERE `name` = 'size_label1' AND `container_class` = 'product'; 
 SELECT `link_id` FROM `container_link` WHERE `container_id` = 69969 AND `reason` = 'product_main_text'; 
 SELECT `value` FROM `custom_field_int` WHERE `container_id` = 69970 AND `custom_field_id` = 334;
 SELECT `value` FROM `custom_field_string` WHERE `container_id` = 69970 AND `custom_field_id` = 397;
 SELECT `link_id` FROM `container_link` WHERE `container_id` = 69963 AND `reason` = 'product_main_text'; 
 SELECT `value` FROM `custom_field_int` WHERE `container_id` = 69964 AND `custom_field_id` = 334;
 SELECT `value` FROM `custom_field_string` WHERE `container_id` = 69964 AND `custom_field_id` = 397; 
 SELECT `link_id` FROM `container_link` WHERE `container_id` = 69961 AND `reason` = 'product_main_text'; 
 SELECT `value` FROM `custom_field_int` WHERE `container_id` = 69962 AND `custom_field_id` = 334;
 SELECT `value` FROM `custom_field_string` WHERE `container_id` = 69962 AND `custom_field_id` = 397; 
 SELECT `link_id` FROM `container_link` WHERE `container_id` = 69967 AND `reason` = 'product_main_text'; 
 SELECT `value` FROM `custom_field_int` WHERE `container_id` = 69968 AND `custom_field_id` = 334; 
 SELECT `value` FROM `custom_field_string` WHERE `container_id` = 69968 AND `custom_field_id` = 397;
 SELECT `link_id` FROM `container_link` WHERE `container_id` = 69971 AND `reason` = 'product_main_text'; 
 SELECT `value` FROM `custom_field_int` WHERE `container_id` = 69972 AND `custom_field_id` = 334; 
 SELECT `value` FROM `custom_field_string` WHERE `container_id` = 69972 AND `custom_field_id` = 397; 
 SELECT `link_id` FROM `container_link` WHERE `container_id` = 69965 AND `reason` = 'product_main_text'; 
 SELECT `value` FROM `custom_field_int` WHERE `container_id` = 69966 AND `custom_field_id` = 334; 
 SELECT `value` FROM `custom_field_string` WHERE `container_id` = 69966 AND `custom_field_id` = 397;
 SELECT `id` FROM `custom_field` WHERE `name` = 'color_id' AND `container_class` = 'product'; 
 SELECT `id` FROM `custom_field` WHERE `name` = 'size_label1' AND `container_class` = 'product'; 
 SELECT `link_id` FROM `container_link` WHERE `container_id` = 70135 AND `reason` = 'product_main_text'; 
 SELECT `value` FROM `custom_field_int` WHERE `container_id` = 70136 AND `custom_field_id` = 334;
 SELECT `value` FROM `custom_field_string` WHERE `container_id` = 70136 AND `custom_field_id` = 397; 
 SELECT `link_id` FROM `container_link` WHERE `container_id` = 70129 AND `reason` = 'product_main_text'; 
 SELECT `value` FROM `custom_field_int` WHERE `container_id` = 70130 AND `custom_field_id` = 334; 
 SELECT `value` FROM `custom_field_string` WHERE `container_id` = 70130 AND `custom_field_id` = 397; 
 SELECT `link_id` FROM `container_link` WHERE `container_id` = 70139 AND `reason` = 'product_main_text';
 SELECT `value` FROM `custom_field_int` WHERE `container_id` = 70140 AND `custom_field_id` = 334;
 SELECT `value` FROM `custom_field_string` WHERE `container_id` = 70140 AND `custom_field_id` = 397;
 SELECT `link_id` FROM `container_link` WHERE `container_id` = 70133 AND `reason` = 'product_main_text'; 
 SELECT `value` FROM `custom_field_int` WHERE `container_id` = 70134 AND `custom_field_id` = 334; 
 SELECT `value` FROM `custom_field_string` WHERE `container_id` = 70134 AND `custom_field_id` = 397; 
 SELECT `link_id` FROM `container_link` WHERE `container_id` = 70137 AND `reason` = 'product_main_text'; 
 SELECT `value` FROM `custom_field_int` WHERE `container_id` = 70138 AND `custom_field_id` = 334; 
 SELECT `value` FROM `custom_field_string` WHERE `container_id` = 70138 AND `custom_field_id` = 397; 
 SELECT `link_id` FROM `container_link` WHERE `container_id` = 70131 AND `reason` = 'product_main_text'; 
 SELECT `value` FROM `custom_field_int` WHERE `container_id` = 70132 AND `custom_field_id` = 334; 
 SELECT `value` FROM `custom_field_string` WHERE `container_id` = 70132 AND `custom_field_id` = 397;

Please not that due to the datamodel comlexity this queries cannot be join or optimized in any way ( I break the 61 join limits if so).

EDIT2 Here are the main methods call to retrive values:

public function findTreeValues($table, $container_id){

    $ret = array();

    $sql = new Sql($this->tableGateway->adapter);
    $select = new \Zend\Db\Sql\Select;
    $select->columns( array('value') );
    $select->from(array('a'=> $table));
    $select->join(array('b'=>'field_set_value'), new Expression("a.value = b.id "), array('label', 'custom_field_id'));
    $select->where( array('a.container_id' => $container_id) );
    $select->order('b.label');

    $statement = $sql->prepareStatementForSqlObject($select);
    //echo $sql->getSqlStringForSqlObject($select);
    $values = $statement->execute();

    foreach ($values as $v){
        $ret[$v['custom_field_id']][$v['value']] = $v['label'];
    }
    return $ret;
}

public function getValue($custom_field_id, $container_id){

    $obj = $this->find($custom_field_id, $container_id);
    return $obj ? $obj->value : null;
}
1
mysqlzend-framework2
Its not necessary actual query time are 0.5 sec, As they may be cached as MySQL query buffer. You may need to clear cache / change parameters of atleast big queries to verify actual time. – kuldeep.kamboj
Yes I made test by clearing mysql cache, different times – albanx
Can you provide some sample code as what kind of queries are those and how are you running 300 of them at time? – Kunal Dethe
Is 300 or so requests sent to the server for executing those many queries? ZF2 does require a lot of function calls to just execute a single query, so it depends on how the queries are run. – Kunal Dethe
I added the query list. And we cannot run this queries in another ways (join problems). This queries are send in one request, (one ajax call I mean) – albanx

1 Answers

2
votes

After debugging and investigating, I found that ZF2 tablegateway makes type parsing of data retriven from db. For every single data, for example for an ID of type INT or for an field of type string it will make a type conversion to the correct php type.

Normally when you select from the db with mysql the return data is always string. This seems to be the slow part:

protected function executeSelect(Select $select)
{
    $selectState = $select->getRawState();
    if ($selectState['table'] != $this->table) {
        throw new Exception\RuntimeException('The table name of the provided select object must match that of the table');
    }

if ($selectState['columns'] == array(Select::SQL_STAR)
    && $this->columns !== array()) {
    $select->columns($this->columns);
}

// apply preSelect features
$this->featureSet->apply('preSelect', array($select));

// prepare and execute
$statement = $this->sql->prepareStatementForSqlObject($select);
$result = $statement->execute();

// build result set
$resultSet = clone $this->resultSetPrototype;
$resultSet->initialize($result);

// apply postSelect features
$this->featureSet->apply('postSelect', array($statement, $result, $resultSet));

return $resultSet;
}

By overriding this method with an simple extend of TableGateway class and removing the following:

// build result set $resultSet = clone $this->resultSetPrototype; $resultSet->initialize($result); $this->featureSet->apply('postSelect', array($statement, $result, $resultSet));

I got an incredible boost up of my application. Now even complicated queries are extremely fast. Hope this will come in help to some one.