The schema caching in Yii

Question

Here http://www.yiiframework.com/doc/guide/1.1/en/topics.performance we can see the following information:

"If the application is using Active Record, we should turn on the schema caching to save the time of parsing database schema. This can be done by configuring the CDbConnection::schemaCachingDuration property to be a value greater than 0."

I have some questions for it:

1. Why should we use schema caching?

2. How does schema caching work?

3. Where can I see the tests?

Answer 1

If you set Yii to log database queries ('enableParamLogging' => true in the db settings of your config file) you can see that Yii queries the database frequently for ActiveRecord queries.

For example: Say you have a model named User and you tell Yii to get you a user by primary key, Yii will query the database three(!) times. It will do a show columns query, then a show create table query, then finally it will query the database for the actual data. Those first two queries are so Yii knows the schema of your user table. If the round trip time from your application server to your database server is 100ms (if it's really this slow, you should do something about it), then those two queries to get the schema will add a minimum of 200ms to your application response time. It will do this for every single request that populates your User model. Depending on how your application is written, it might even do that multiple times in a single request.

If, on the other hand, you tell Yii to use schema caching, Yii will check the cache to see if it already knows the schema to your user table and if it does, it will use that instead of hitting the database. There is still latency involved in checking the cache, but hopefully it's less than or equal to the latency to the database server. Say you're using a Redis server for caching and it also has a latency of 100ms (also ridiculously long). Caching the schema will still be faster than querying the database, because Yii will only need to query the cache a single time to retrieve the schema, versus two trips to the database.

So in this example switching from querying a high latency database for the schema to querying a high latency cache server will still save you time. In practice, your cache should be much lower latency than your database and therefore save you much more time and if your application is remotely complicated, Yii will be loading schema from multiple tables on each request so schema caching can make a huge difference in request response time.