Retrival of Data too Slow using CDbcriteria

Hi guys!

Me and my fellow is using Yii framework as our tool for development. We started using it last April and as we go along we have learned a lot. But now we are having a problem with retriving data from several tables–it takes too long. Below is an example of how we retrieve data. Can someone help us please in making data retrieval faster?

$criteria=new CDbCriteria();

$criteria->alias = 'Item';

$criteria->distinct = true;

$criteria->join = 'LEFT JOIN newscountry ON Item.ID = newscountry.NEWSITEM_ID 

		LEFT JOIN newscategory ON Item.ID = newscategory.NEWSITEM_ID

		LEFT JOIN category ON newscategory.CATEGORY_ID = category.ID 

		LEFT JOIN newssubcategory ON Item.ID = newssubcategory.NEWSITEM_ID

		LEFT JOIN subcategory ON newssubcategory.SUBCATEGORY_ID = subcategory.ID

		LEFT JOIN newssource ON Item.SOURCE_ID = newssource.ID

		LEFT JOIN newssubsubcategory ON Item.id = newssubsubcategory.newsitem_id

		LEFT JOIN subsubcategory ON newssubsubcategory.subsubcategory_id = subsubcategory.id';


$criteria->condition = '(Item.SOURCE_ID = :sourceid OR :sourceid = "") AND (newscountry.COUNTRY_CODE = :countryCode OR :countryCode = "") AND (newscategory.CATEGORY_ID = :categoryid OR :categoryid = "") AND (Item.STATUS = :status OR :status = "") AND (newssubcategory.SUBCATEGORY_ID = :subcategoryid OR :subcategoryid = "") AND (newssubsubcategory.SUBSUBCATEGORY_ID = :subsubcategoryid OR :subsubcategoryid = "")';


$criteria->params = array(':sourceid'=>$source,':countryCode'=>$countryCode, ':categoryid'=>$category, ':status'=>$status,'subcategoryid'=>$subcategoryid,':subsubcategoryid'=>$subsubcategoryid);		


$criteria->order = $order;

$criteria->limit = "2";

$criteria->offset = $offset;

Thanks for anyone who could suggest how would we be able to improve the code. By the way, the tables has an average records/data of 12,000. lol.

Eight way join - check.

Dirty big WHERE clause, requiring the comparison of many columns - check.

Don’t think it’s fair to blame CDbcriteria for that. Maybe you need to look at the db, is it possible to denormalize a bit? How about your use of indexes? Depending on your database implementation you could look at how your query optimiser is seeing this.

Honestly this is a DBA question.

Hi ansherina,

I do agree with Luke. Its not a good Database practice you are following here.Try to redesign your database schema for better performance.

There is nothing wrong with CDCriteria and Yii too.Check your code and an approach first what you are using here.

Thank you guys!

Tables have so many records. Thousands. My DBA told me the reason for not having a fast result was because of the distinct. He gave me an idea what to do to enhance my SQL. Thank you for your reply guys. I will post here later what I have made to make it faster.

you should rethink your database design as well, there’s a lot room for improvement.

I had the same problem. Cdbcriteria is an inefficient way to retrieve data because it returns the whole object of Active Record which has so many garbage data that we dont want. Thats why it takes so much time to load your data especially when working with large datasets.
Solution? Go to your model, make a bunch of get functions like most frameworks have and fill them with query builder commands (Yii Query Builder). This way you only retrieve the values you want.
I dont know why nobody mentions this.