YII SQL Query Optimization

Question

I have a huge list of identifiers that I need to query through the table to find out if these identifiers are available in the table, if they bring their model.

Since there are several thousand identifiers, this process is very slow, since I use CActiveRecord :: find () mothod

ex. $book = Book::model()->find('book_id=:book_id', array(':book_id'=>$product->book_id));

I even indexed all possible keys, but no improvements. Any suggestions for improving execution speed?

early:)

Answer 1

1) Make a list of book IDs

 foreach $product in Product-List $book_ids[$product->book_id] = $product->book_id; 

Now request all book models (with index book_id )

 $books = Book::model()->findAll(array( 'index' => 'book_id', 'condition' => 'book_id IN (' . implode(',', $book_ids). ')', )); 

Integrate $books into your code, I believe that you are browsing all the products.

 foreach $product in Product-List if( isset($books[$product->book_id]) ) $model = $books[$product->book_id] 

---

2) Another way (I just assume that you have a product model)

in the product model add a relation to the book

 public function relations() { ....... 'book'=>array(self::HAS_ONE, 'Book', 'book_id'), ....... } 

When you receive the list of products, add the condition 'with' => array('book') with any of CActiveDataProvider or CActiveRecord ...

 //Example $productList = Product::model()->findAll(array( 'with' => array('book'), )); foreach( $productList as $product ) { ....... if( $product->book != null ) $model = $product->book; ...... } 

In any case, you can reduce SQL queries.

Answer 2

It is better if you use schema caching, because Yii retrieves the schema every time we execute the request. This will improve the performance of your request.

You can enable schema caching by doing some configuration in the config/main.php .

 return array( ...... 'components'=>array( ...... 'cache'=>array( 'class'=>'system.caching.CApcCache', // caching type APC cache ), 'db'=>array( ........... 'schemaCachingDuration'=>3600, // life time of schema caching ), ), ); 

Another thing you can get for a specific column of a table, which will also improve performance.

You can do this using CDbCriteria with the find CActiveRecord method.

 $criteria = new CDbCriteria; $criteria->select = 'book_id'; $criteria->condition = 'book_id=:book_id'; $criteria->params = array(':book_id'=>$product->book_id); $book = Book::model()->find($criteria); 

I would suggest you use any nosql database if you process thousands of records, if that suits.