Contact Information

  • AUTHOR Peeyush Jagtap
    Associate Technical Manager
  • PUBLISHED ON May 10, 2019

Yii 2 optimization while bulk import

If you want to upload large data in system, so there are some optimization tips below.

1. Data caching

Data caching is about storing some PHP variables in cache and retrieving it later from cache. It is also the foundation for more advanced caching features, such as query caching and page caching.

The following code is a typical usage pattern of data caching, where $cache refers to a cache component:

// try retrieving $data from cache
$data = $cache->get($key);

if ($data === false) {

// $data is not found in cache, calculate it from scratch

// store $data in cache so that it can be retrieved next time

$cache->set($key, $data);


// $data is available here

Cache Components

Data caching relies on the so-called cache components which represent various cache storage, such as memory, files, databases.Cache components are usually registered as application components so that they can be globally configurable and accessible.

The following code shows how to configure the cache application component to use memcached with two cache servers:

‘components’ => [
‘cache’ => [

‘class’ => ‘yii\caching\MemCache’,

‘servers’ => [


‘host’ => ‘server1’,

‘port’ => 11211,

‘weight’ => 100,



‘host’ => ‘server2’,

‘port’ => 11211,

‘weight’ => 50,




You can then access the above cache component using the expression Yii::$app->cache.Because all cache components support the same set of APIs, you can swap the underlying cache component with a different one by reconfiguring it in the application configuration without modifying the code that uses the cache. For example, you can modify the above configuration to use APC cache:

‘components’ => [

‘cache’ => [

‘class’ => ‘yii\caching\ApcCache’,



Cache APIs

All cache components have the same base class yii\caching\Cache and thus support the following APIs:

get(): retrieves a data item from cache with a specified key. A false value will be returned if the data item is not found in the cache or is expired/invalidated.

set(): stores a data item identified by a key in cache.

add(): stores a data item identified by a key in cache if the key is not found in the cache.

multiGet(): retrieves multiple data items from cache with the specified keys.

multiSet(): stores multiple data items in cache. Each item is identified by a key.

multiAdd(): stores multiple data items in cache. Each item is identified by a key. If a key already exists in the cache, the data item will be skipped.

exists(): returns a value indicating whether the specified key is found in the cache.

delete(): removes a data item identified by a key from the cache.

flush(): removes all data items from the cache.
Some cache storage, such as MemCache, APC, support retrieving multiple cached values in a batch mode, which may reduce the overhead involved in retrieving cached data. The APIs multiGet() and multiAdd() are provided to exploit this feature. In case the underlying cache storage does not support this feature, it will be simulated.Because yii\caching\Cache implements ArrayAccess, a cache component can be used like an array. The following are some examples:

$cache[‘var1’] = $value1; // equivalent to: $cache->set(‘var1’, $value1);

$value2 = $cache[‘var2’]; // equivalent to: $value2 = $cache->get(‘var2’);

Query Caching

Query caching is a special caching feature built on top of data caching. It is provided to cache the result of database queries.Query caching requires a DB connection and a valid cache application component. The basic usage of query caching is as follows, assuming $db is a yii\db\Connection instance:

$result = $db->cache(function ($db) {

// the result of the SQL query will be served from the cache

// if query caching is enabled and the query result is found in the cache

return $db->createCommand(‘SELECT * FROM customer WHERE id=1’)->queryOne();

Query caching can be used for DAO as well as ActiveRecord:

$result = Customer::getDb()->cache(function ($db) {

return Customer::find()->where([‘id’ => 1])->one();


Cache Flushing

When you need to invalidate all the stored cache data, you can call yii\caching\Cache::flush().You can flush the cache from the console by calling yii cache/flush as well.

– yii cache: lists the available caches in application

– yii cache/flush cache1 cache2: flushes the cache components cache1, cache2 (you can pass multiple component names separated with space)

– yii cache/flush-all: flushes all cache components in the application

2. Bulk insert

batchInsert() public method :- Creates a batch INSERT command.

For example,

$connection->createCommand()->batchInsert(‘user’, [‘name’, ‘age’], [

[‘Tom’, 30],

[‘Jane’, 20],

[‘Linda’, 25],

The method will properly escape the column names, and quote the values to be inserted.Note that the values in each row must match the corresponding column names.Also note that the created command is not executed until execute() is called.

public $this batchInsert ( $table, $columns, $rows )

$table string The table that new rows will be inserted into.

$columns array The column names

$rows array The rows to be batch inserted into the table

return $this The command object itself

3. Bulk update

There is no any function available in Yii2, but we have written one custom function, It may helps you a lot.Using the MySQL INSERT ON DUPLICATE KEY UPDATE.

$table – Name of the db table

$columns – db table column array

$rows – values array

$duplicates – fields array which you want to update

public function batchUpdate($table, $columns, $rows, $duplicates = [])


$db =Yii::$app->db;

if (($tableSchema = $db->getTableSchema($table)) !== null) {

$columnSchemas = $tableSchema->columns;

} else {

$columnSchemas = [];


$sql = $db->getQueryBuilder()->batchInsert($table, $columns, $rows);

if(!empty($duplicates)) {

$columnDuplicates = [];

foreach($duplicates as $i => $column) {

if(isset($columnSchemas[$duplicates[$i]])) {

$column = $db->quoteColumnName($column);

$columnDuplicates[] = $column . ‘ = VALUES(‘ . $column . ‘)’;



if(!empty($columnDuplicates)) {

$sql .= ‘ ON DUPLICATE KEY UPDATE ‘ . implode(‘,’, $columnDuplicates);



return $db->createCommand()->setSql($sql);


Note – Only you have to pass primary key / unique key in the $columns array and its not to be in $duplicates array.


Post Comments

Leave a Reply