Info block from the definitive guide:
Quote
Info: The AR implementation in Yii is very efficient. When eager loading a hierarchy of related objects involving N HAS_MANY or MANY_MANY relationships, it will take N+1 SQL queries to obtain the needed results. This means it needs to execute 3 SQL queries in the last example because of the posts and categories properties. Other frameworks take a more radical approach by using only one SQL query. At first look, this approach seems more efficient because fewer queries are being parsed and executed by DBMS. It is in fact impractical in reality for two reasons. First, there are many repetitive data columns in the result which takes extra time to transmit and process. Second, the number of rows in the result set grows exponentially with the number of tables involved, which makes it simply unmanageable as more relationships are involved.
I'm writing quite big portal now, so I'm very concerned about efficiency…
I'm using InnoDB tables (see mysql performance blog, it is faster, than MyISAM and I have tests for it)
That's why I was running some tests. Let's take the live example:
There is a site about clothes. There are categories (dresses, t-shirts etc), each category has size categories (dress sizes, generic sizes, UK sizes, european sizes etc), each size category holds sizes itself. For example category "generic sizes" holds S,M,L, XL, XXL.
See attached scheme.
Now running 2 tests in the actionIndex of the controller:
$t = microtime(true);
$cmd = Yii::app()->db->createCommand('SELECT s.*, sc.*
FROM Size s
INNER JOIN SizeCategory sc ON s.sizeCategoryID=sc.sizeCategoryID
INNER JOIN CategorySizeCategory csc ON csc.sizeCategoryID=sc.sizeCategoryID
WHERE csc.categoryID=:cID');
$cmd->bindParam(':cID', $this->categoryID, PDO::PARAM_INT);
$sizes = $cmd->queryAll();
$interval = microtime(true)-$t;
$t = microtime(true);
$category = Category::model()->with(array('sizeCategories' => array('sizes')))->findByPk($this->categoryID);
$szs = $category->sizeCategories;
$interval2 = microtime(true)-$t;
$this->render('index', array('sizes' => $sizes, 'int' => $interval, 'sizes2' => $szs, 'int2' => $interval2));
First variant - low-level SQL pus binding params.
Second - using relational AR queries.
Here are relations for:
Category:
public function relations()
{
return array(
'sizeCategories'=>array(self::MANY_MANY, 'SizeCategory', 'CategorySizeCategory(categoryID, sizeCategoryID)', 'order' => 'sizeCategory'),
'products'=>array(self::HAS_MANY , 'Product', 'categoryID', 'order' => 'name'),
'tags'=>array(self::HAS_MANY , 'CategoryTag', 'categoryID', 'order' => '??.clicksNum', 'limit' => Yii::app()->params['maxCategoryTags']),
);
}
SizeCategory:
public function relations()
{
return array(
'sizes'=>array(self::HAS_MANY, 'Size', 'sizeCategoryID', 'order'=>'size'),
);
}
Here is DB structure for these tables:
-- -----------------------------------------------------
-- Table `everystyle`.`Category`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `everystyle`.`Category` (
`categoryID` INT(5) NOT NULL AUTO_INCREMENT ,
`name` VARCHAR(100) NOT NULL ,
`clicksNum` INT(11) NOT NULL DEFAULT 0 ,
`gender` ENUM('male','female','boys','girls') NOT NULL ,
PRIMARY KEY (`categoryID`) )
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `everystyle`.`SizeCategory`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `everystyle`.`SizeCategory` (
`sizeCategoryID` INT(11) NOT NULL ,
`sizeCategory` VARCHAR(100) NULL ,
PRIMARY KEY (`sizeCategoryID`) )
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `everystyle`.`Size`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `everystyle`.`Size` (
`sizeID` INT(11) NOT NULL AUTO_INCREMENT ,
`size` VARCHAR(45) NULL ,
`sizeCategoryID` INT(11) NOT NULL ,
PRIMARY KEY (`sizeID`) ,
INDEX `fk_Size_sizeCategory` (`sizeCategoryID` ASC) ,
CONSTRAINT `fk_Size_sizeCategory`
FOREIGN KEY (`sizeCategoryID` )
REFERENCES `everystyle`.`SizeCategory` (`sizeCategoryID` )
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `everystyle`.`CategorySizeCategory`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `everystyle`.`CategorySizeCategory` (
`categoryID` INT(11) NOT NULL ,
`sizeCategoryID` INT(11) NOT NULL ,
INDEX `fk_CategorySizeCategory_Category` (`categoryID` ASC) ,
INDEX `fk_CategorySizeCategory_sizeCategory` (`sizeCategoryID` ASC) ,
PRIMARY KEY (`categoryID`, `sizeCategoryID`) ,
CONSTRAINT `fk_CategorySizeCategory_Category`
FOREIGN KEY (`categoryID` )
REFERENCES `everystyle`.`Category` (`categoryID` )
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_CategorySizeCategory_sizeCategory`
FOREIGN KEY (`sizeCategoryID` )
REFERENCES `everystyle`.`SizeCategory` (`sizeCategoryID` )
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
Results of test is:
0.00061607360839844 - SQL 0.028649091720581 - ActiveRecord
Active record is 46(!!!) times slower, than simple SQL!
Here is the secret of "efficiency" (stated in the definitive guide). While SQL query uses 1 query with 2 joins, relational query with active record uses 9 (!!!) queries. See application log:
2008/12/16 11:03:53 [trace] [system.db.CDbCommand] query with SQL: SELECT s.*, sc.* FROM Size s INNER JOIN SizeCategory sc ON s.sizeCategoryID=sc.sizeCategoryID INNER JOIN CategorySizeCategory csc ON csc.sizeCategoryID=sc.sizeCategoryID WHERE csc.categoryID=:cID 2008/12/16 11:03:53 [trace] [system.db.CDbCommand] query with SQL: SHOW COLUMNS FROM `SizeCategory` 2008/12/16 11:03:53 [trace] [system.db.CDbCommand] query with SQL: SHOW CREATE TABLE `SizeCategory` 2008/12/16 11:03:53 [trace] [system.db.CDbCommand] query with SQL: SHOW COLUMNS FROM `Size` 2008/12/16 11:03:53 [trace] [system.db.CDbCommand] query with SQL: SHOW CREATE TABLE `Size` 2008/12/16 11:03:53 [trace] [system.db.CDbCommand] query with SQL: SELECT `Category`.`categoryID` AS t0_c0, `Category`.`intName` AS t0_c1, `Category`.`name` AS t0_c2, `Category`.`clicksNum` AS t0_c3, `Category`.`gender` AS t0_c4 FROM `Category` WHERE `Category`.`categoryID`=1 2008/12/16 11:03:53 [trace] [system.db.CDbCommand] query with SQL: SHOW COLUMNS FROM `CategorySizeCategory` 2008/12/16 11:03:53 [trace] [system.db.CDbCommand] query with SQL: SHOW CREATE TABLE `CategorySizeCategory` 2008/12/16 11:03:53 [trace] [system.db.CDbCommand] query with SQL: SELECT `Category`.`categoryID` AS t0_c0, t1.`sizeCategoryID` AS t1_c0, t1.`sizeCategory` AS t1_c1 FROM `Category` LEFT OUTER JOIN `CategorySizeCategory` sizeCategories_t1 ON `Category`.`categoryID`=sizeCategories_t1.`categoryID` LEFT OUTER JOIN `SizeCategory` t1 ON t1.`sizeCategoryID`=sizeCategories_t1.`sizeCategoryID` WHERE `Category`.`categoryID`=1 ORDER BY sizeCategory 2008/12/16 11:03:53 [trace] [system.db.CDbCommand] query with SQL: SELECT t1.`sizeCategoryID` AS t1_c0, t2.`sizeID` AS t2_c0, t2.`size` AS t2_c1, t2.`sizeCategoryID` AS t2_c2 FROM `SizeCategory` t1 LEFT OUTER JOIN `Size` t2 ON t2.`sizeCategoryID`=t1.`sizeCategoryID` WHERE t1.`sizeCategoryID` IN (2, 1) ORDER BY size
Any comments on this?
