SQL: improved performance for adding new articles

3 files changed, 24 insertions, 13 deletions

diff --git a/app/Controllers/feedController.php b/app/Controllers/feedController.php
index 36425ca9b..3326b2059 100755
--- a/app/Controllers/feedController.php
+++ b/app/Controllers/feedController.php
@@ -109,18 +109,23 @@ class FreshRSS_feed_Controller extends Minz_ActionController {
 						$nb_month_old = $this->view->conf->old_entries;
 						$date_min = time () - (3600 * 24 * 30 * $nb_month_old);
 
+						//MySQL: http://docs.oracle.com/cd/E17952_01/refman-5.5-en/optimizing-innodb-transaction-management.html
+						//SQLite: http://stackoverflow.com/questions/1711631/how-do-i-improve-the-performance-of-sqlite
+						$preparedStatement = $entryDAO->addEntryPrepare();
 						$transactionStarted = true;
-						$feedDAO->beginTransaction ();
+						$feedDAO->beginTransaction();
 						// on ajoute les articles en masse sans vérification
 						foreach ($entries as $entry) {
-							$values = $entry->toArray ();
-							$values['id_feed'] = $feed->id ();
-							$values['id'] = min(time(), $entry->date (true)) . uSecString();
+							$values = $entry->toArray();
+							$values['id_feed'] = $feed->id();
+							$values['id'] = min(time(), $entry->date(true)) . uSecString();
 							$values['is_read'] = $is_read;
-							$entryDAO->addEntry ($values);
+							$entryDAO->addEntry($values, $preparedStatement);
+						}
+						$feedDAO->updateLastUpdate($feed->id());
+						if ($transactionStarted) {
+							$feedDAO->commit();
 						}
-						$feedDAO->updateLastUpdate ($feed->id ());
-						$feedDAO->commit ();
 						$transactionStarted = false;
 
 						// ok, ajout terminé
@@ -265,22 +270,23 @@ class FreshRSS_feed_Controller extends Minz_ActionController {
 						$feedHistory = $this->view->conf->keep_history_default;
 					}
 
+					$preparedStatement = $entryDAO->addEntryPrepare();
 					$hasTransaction = true;
 					$feedDAO->beginTransaction();
 
 					// On ne vérifie pas strictement que l'article n'est pas déjà en BDD
 					// La BDD refusera l'ajout car (id_feed, guid) doit être unique
 					foreach ($entries as $entry) {
-						$eDate = $entry->date (true);
-						if ((!isset ($existingGuids[$entry->guid ()])) &&
+						$eDate = $entry->date(true);
+						if ((!isset($existingGuids[$entry->guid()])) &&
 							(($feedHistory != 0) || ($eDate  >= $date_min))) {
-							$values = $entry->toArray ();
+							$values = $entry->toArray();
 							//Use declared date at first import, otherwise use discovery date
 							$values['id'] = ($useDeclaredDate || $eDate < $date_min) ?
 								min(time(), $eDate) . uSecString() :
 								uTimeString();
 							$values['is_read'] = $is_read;
-							$entryDAO->addEntry ($values);
+							$entryDAO->addEntry($values, $preparedStatement);
 						}
 					}
 				}
diff --git a/app/Controllers/importExportController.php b/app/Controllers/importExportController.php
index 6b4c3e81a..ba172cc6d 100644
--- a/app/Controllers/importExportController.php
+++ b/app/Controllers/importExportController.php
@@ -266,6 +266,7 @@ class FreshRSS_importExport_Controller extends Minz_ActionController {
 			);
 			$entry->_tags($tags);
 
+			//FIME: Use entryDAO->addEntryPrepare(). Do not call entryDAO->listLastGuidsByFeed() for each entry. Consider using a transaction.
 			$id = $this->entryDAO->addEntryObject(
 				$entry, $this->view->conf, $feed->keepHistory()
 			);
diff --git a/app/Models/EntryDAO.php b/app/Models/EntryDAO.php
index 1775af63c..6f3f472f6 100644
--- a/app/Models/EntryDAO.php
+++ b/app/Models/EntryDAO.php
@@ -6,14 +6,18 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo {
 		return parent::$sharedDbType !== 'sqlite';
 	}
 
-	public function addEntry($valuesTmp) {
+	public function addEntryPrepare() {
 		$sql = 'INSERT INTO `' . $this->prefix . 'entry`(id, guid, title, author, '
 		     . ($this->isCompressed() ? 'content_bin' : 'content')
 		     . ', link, date, is_read, is_favorite, id_feed, tags) '
 		     . 'VALUES(?, ?, ?, ?, '
 		     . ($this->isCompressed() ? 'COMPRESS(?)' : '?')
 		     . ', ?, ?, ?, ?, ?, ?)';
-		$stm = $this->bd->prepare($sql);
+		return $this->bd->prepare($sql);
+	}
+
+	public function addEntry($valuesTmp, $preparedStatement = null) {
+		$stm = $preparedStatement === null ? addEntryPrepare() : $preparedStatement;
 
 		$values = array(
 			$valuesTmp['id'],