Dataprovider Caching Is Driving Me Crazy!

ferminako · May 5, 2014, 9:00am

Hi everybody,

It’s my very first time using caching, and I’m not sure if what I’m doing is right or not.

In my web I have a search, you must enter country for exaple, and depending on the country you enter you are given a list of results.

To get this list of result I use a dataprovider and then I pass it to a gridview and then render it, so first of all I’m not sure if I can use cache for this search due to each search could be different.

So how can I manage cache to handle it? Should I have different dependencies for the most common countries and dependenig on wich one is searched I use one or another dependency?

Actually the sql search is extremly more complicated beacuse there are not only countries, but there are also cities, stablishments and more things…but is easier to explain myself with this simple example.

Anyway I enabled cache in config main :


'components'=>array(

    	'cache'=> array(

    		'class' => 'CDbCache'

    	),

And in my controller I tried this:


$dependency = new CDbCacheDependency('SELECT count(id) FROM countries');




		$dataProvider=new CActiveDataProvider(Countries::model()->cache(36000,$dependency,2), array(

		    'criteria'=>array(

		        'with'=>$dataProviderWith,

		        'condition'=>$parametros,

		        'having'=>$having,

		        'group'=>'t.id',

		        'order'=>$order

		    ),

		    'pagination'=>array(

		        'pageSize'=>8,

		    ),

		))

But it doesn’t work, because the time search takes is the same in the first time and in the second and so on… I can’t notice any time response improving due to cache handling.

Please help me!

ferminako · May 6, 2014, 6:55am

no one???

softark · May 6, 2014, 7:38am

Hi ferminako,

Your sample code looks fine to me.

Two things come to my mind.

Just for double check: Have you specified the right CDbConnection::queryCacheID? … I’m pretty sure you have.
You might be comparing the different things when you are measuring the cache performance. Didn’t you compare the performance for the first page with that of the second?

Please note that the query cache uses the SQL for its entry key. For example, all of these queries will create different cache entries.




Select * from countries offset 0 limit 10;

Select * from countries offset 10 limit 10;

Select * from countries where name like 'c%' offset 0 limit 10;

Select * from countries where name like 'j%' offset 0 limit 10;

Select * from countries where name like 'japan%' offset 0 limit 10;

The query cache will work just when you execute exactly the same SQL.

So, I’m afraid you will not be able to get much acceleration from the query caching for your page.

ferminako · May 6, 2014, 10:26am

It seems to work because I see in in the log the second time I’ve made the search :




Query result found in cache

in...

That means it works I guess… because the first time I’ve made the search, in the log I couldn’t see that, but the improvement is about 200ms more or less, I expected more… but in the other hand I suppose even getting not to much time response improvement, the server will be less loaded because it doesn’t have to execute the sql, so anyway I gain something right???

I’ve also tried with CFileCache, because is easier to remove cache to try different things, but the result is the same.

ferminako · May 6, 2014, 1:37pm

Because of the small improvement, I’ve gone one step further and I’ve activated the log of my local mysql where I’m doing the cache testing just to see if actually cache is working, because I don’t see dependency’s queries on yii log, having this example:




		$dependency = new CFileCacheDependency('SELECT  lastModDate FROM rst where id='.$id);

		$model=Rst::model()->cache(3600*24*7,$dependency)->findByPk($id);

First of all I’ve removed cached files in proyect/protected/runtime/cache

First time, so there is nothing cached, I execute the code I wrote above and I get this in sql log:


/opt/lampp/sbin/mysqld, Version: 5.5.27 (Source distribution). started with:

Tcp port: 3306  Unix socket: /opt/lampp/var/mysql/mysql.sock

...

		  331 Query	SELECT * FROM `rst` `t` WHERE `t`.`id`=381 LIMIT 1

		  331 Query	SELECT * FROM `rst` `t` WHERE `t`.`id`=381 LIMIT 1

		  331 Quit

Second time I get the log with a sql query less:


/opt/lampp/sbin/mysqld, Version: 5.5.27 (Source distribution). started with:

Tcp port: 3306  Unix socket: /opt/lampp/var/mysql/mysql.sock

...

		  331 Query	SELECT * FROM `rst` `t` WHERE `t`.`id`=381 LIMIT 1

		  331 Quit

So of course I suppose that caching is doing something, because there is one less sql query, but I’m wondering where is the sql query of dependency considering It must have done first in order to evaluate last modification date?

Because depending on the result, it should or not execute the main query…

And why is executing the query above if it is in cache? Because in yii log I found “Query result found in…”, so it shoudn’t been executed right?

Thank you!

softark · May 6, 2014, 7:49pm

It looks strange to me, too.

The log should be something like this:


/opt/lampp/sbin/mysqld, Version: 5.5.27 (Source distribution). started with:

Tcp port: 3306  Unix socket: /opt/lampp/var/mysql/mysql.sock

...

		  331 Query	SELECT  lastModDate FROM rst where id=381

		  331 Query	SELECT * FROM `rst` `t` WHERE `t`.`id`=381 LIMIT 1

		  331 Quit

and


/opt/lampp/sbin/mysqld, Version: 5.5.27 (Source distribution). started with:

Tcp port: 3306  Unix socket: /opt/lampp/var/mysql/mysql.sock

...

		  331 Query	SELECT  lastModDate FROM rst where id=381

		  331 Quit

softark · May 6, 2014, 10:18pm

Um? CFileCacheDependency?

ferminako · May 7, 2014, 6:33am

What you mean with that??? Im using CFileCache as cache system know, Is it wrong to use CFileCacheDependency in? :


     $dependency = new CFileCacheDependency('SELECT  lastModDate FROM rst where id='.$id);

What should I use? I tried changing:


'cache'=> array(

    		'class' => 'CFileCache'

    	),

To:


'cache'=> array(

    		'class' => 'CDbCache'

    	),

And :


     $dependency = new CDbCacheDependency('SELECT  lastModDate FROM rst where id='.$id);

And I can’t find the sql of dependency neither…

softark · May 7, 2014, 10:10am

It should be an instance of CDbCacheDependency whenever you want to check the result of a SQL for the dependency, no matter what kind of cache you are using.

You can use CFileCacheDependency when you want to check the last modification time of a certain file as the dependency.

http://www.yiiframework.com/doc/api/1.1/CFileCacheDependency

ferminako · May 7, 2014, 10:54am

I’m really sorry but I don’t understand exactly what you mean softark (I’m spanish and my english comprenhension sometimes is not enough…) but I think you are telling me that if I use CDbCacheDependency is only to check sql dependency, and If I use CFileCacheDependency is to check the modification date of a dependency file?

The thing is that dependency sql is not executing, and I don’t know why. In this case:


'cache'=> array(

                'class' => 'CFileCache'

        ),


 $dependency = new CFileCacheDependency('SELECT  lastModDate FROM rst where id='.$id);

                $model=Rst::model()->cache(3600*24*7,$dependency)->findByPk($id);

What I want, is to check if the field ‘lastModDate’ in rst table, if it is different from the last time I executed this code, is because the rst information has been modified, so the main sql should be executed.

Could you please tell me wich things would you change in order to find why dependency is not working properly??

EDIT:_________________________________________________________________________

I’ve just tried the follow:


'cache'=> array(

                'class' => 'CDbCache'

        ),


 $dependency = new CDbCacheDependency('SELECT  lastModDate FROM rst where id='.$id);

                $model=Rst::model()->cache(3600*24*7,$dependency)->findByPk($id);

But prevously I executed Yii::app()->cache->flush(); and now I can find the sql of dependency executing so I’m gonna make some test and I’ll give you the results.

Thank you so much.

softark · May 7, 2014, 11:23am

In this case, you must use CDbCacheDependency because you want to query something from db as the dependency checking.




    $dependency = new CDbCacheDependency('SELECT  lastModDate FROM rst where id='.$id);

    $model=Rst::model()->cache(3600*24*7,$dependency)->findByPk($id);

The code above is always right, regardless of the cache media (CFileCache, CDbCache, … etc) you use.

Note that there is no coupled connection between CDbCache and CDbCacheDependency. CDbCacheDependency must be used with CFileCache, CApcCache, CMemCache, or anything else when you want to check ‘lastModDate’ of ‘rst’ table.

Please double check the result when you use CDbCacheDependency … I believe it should log the sql for the dependency.

[EDIT]

Oh, I was a little too late. Sorry.

ferminako · May 9, 2014, 7:10am

softark:

In this case, you must use CDbCacheDependency because you want to query something from db as the dependency checking.
    $dependency = new CDbCacheDependency('SELECT  lastModDate FROM rst where id='.$id);

    $model=Rst::model()->cache(3600*24*7,$dependency)->findByPk($id);
The code above is always right, regardless of the cache media (CFileCache, CDbCache, … etc) you use.

Note that there is no coupled connection between CDbCache and CDbCacheDependency. CDbCacheDependency must be used with CFileCache, CApcCache, CMemCache, or anything else when you want to check ‘lastModDate’ of ‘rst’ table.

Please double check the result when you use CDbCacheDependency … I believe it should log the sql for the dependency.

[EDIT]

Oh, I was a little too late. Sorry.

No problem Softark, u heped me a lot, now caching is running on my local proyect, so now I’m gonna try to make it works on my hosted server.

Thank you a lot!!!