Post by letxobnav » Thu Jul 18, 2019 6:51 pm

Prepared sql statements are intended to speed up multiple identical sql queries as the sql server only has to interpret the textual sql statement only once into it's binary form and just substitutes the variables.
This is an example on how you could implement it for the product list in OC.
Since the getProducts function calls the getProduct function many times in sequence, this is a good point to start.
I have not done any performance measurements on it yet though.



The default DB classes do not have the methods implemented so we have to add those.

system/library/db.php

add function:

Code: Select all

	public function prepare($query) {
		return $this->adaptor->prepare($query);
	}


system/library/db/mysqli.php

add function:

Code: Select all

	public function prepare($query) {
		return $this->connection->prepare($query);
	}


catalog/model/catalog/product.php

add functions:

Code: Select all

	// set the prepared statement
	public function getProductPrepare() {
		
		// default product query with the variable $product_id replaced by ? placeholder
		$prep = $this->db->prepare("SELECT DISTINCT *, pd.name AS name, p.image, m.name AS manufacturer, (SELECT price FROM " . DB_PREFIX . "product_discount pd2 WHERE pd2.product_id = p.product_id AND pd2.customer_group_id = '" . (int)$this->config->get('config_customer_group_id') . "' AND pd2.quantity = '1' AND ((pd2.date_start = '0000-00-00' OR pd2.date_start < NOW()) AND (pd2.date_end = '0000-00-00' OR pd2.date_end > NOW())) ORDER BY pd2.priority ASC, pd2.price ASC LIMIT 1) AS discount, (SELECT price FROM " . DB_PREFIX . "product_special ps WHERE ps.product_id = p.product_id AND ps.customer_group_id = '" . (int)$this->config->get('config_customer_group_id') . "' AND ((ps.date_start = '0000-00-00' OR ps.date_start < NOW()) AND (ps.date_end = '0000-00-00' OR ps.date_end > NOW())) ORDER BY ps.priority ASC, ps.price ASC LIMIT 1) AS special, (SELECT points FROM " . DB_PREFIX . "product_reward pr WHERE pr.product_id = p.product_id AND pr.customer_group_id = '" . (int)$this->config->get('config_customer_group_id') . "') AS reward, (SELECT ss.name FROM " . DB_PREFIX . "stock_status ss WHERE ss.stock_status_id = p.stock_status_id AND ss.language_id = '" . (int)$this->config->get('config_language_id') . "') AS stock_status, (SELECT wcd.unit FROM " . DB_PREFIX . "weight_class_description wcd WHERE p.weight_class_id = wcd.weight_class_id AND wcd.language_id = '" . (int)$this->config->get('config_language_id') . "') AS weight_class, (SELECT lcd.unit FROM " . DB_PREFIX . "length_class_description lcd WHERE p.length_class_id = lcd.length_class_id AND lcd.language_id = '" . (int)$this->config->get('config_language_id') . "') AS length_class, (SELECT AVG(rating) AS total FROM " . DB_PREFIX . "review r1 WHERE r1.product_id = p.product_id AND r1.status = '1' GROUP BY r1.product_id) AS rating, (SELECT COUNT(*) AS total FROM " . DB_PREFIX . "review r2 WHERE r2.product_id = p.product_id AND r2.status = '1' GROUP BY r2.product_id) AS reviews, p.sort_order FROM " . DB_PREFIX . "product p LEFT JOIN " . DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id) LEFT JOIN " . DB_PREFIX . "product_to_store p2s ON (p.product_id = p2s.product_id) LEFT JOIN " . DB_PREFIX . "manufacturer m ON (p.manufacturer_id = m.manufacturer_id) WHERE p.product_id = ? AND pd.language_id = '" . (int)$this->config->get('config_language_id') . "' AND p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "'");
		return $prep;
	}
	
	// fetch product data using the prepared statement
	public function getProductPrepared($product_id,$prep) {
		$prep -> bind_param('i', $product_id);
		$prep -> execute();
		$result = $prep -> get_result();
		$num_of_rows = $result->num_rows;
		$row = $result->fetch_assoc();
		if ($num_of_rows) {
			$product_data = array(
				'product_id'       => $row['product_id'],
				'name'             => $row['name'],
				'description'      => $row['description'],
				'meta_title'       => $row['meta_title'],
				'meta_description' => $row['meta_description'],
				'meta_keyword'     => $row['meta_keyword'],
				'tag'              => $row['tag'],
				'model'            => $row['model'],
				'sku'              => $row['sku'],
				'upc'              => $row['upc'],
				'ean'              => $row['ean'],
				'jan'              => $row['jan'],
				'isbn'             => $row['isbn'],
				'mpn'              => $row['mpn'],
				'location'         => $row['location'],
				'quantity'         => $row['quantity'],
				'stock_status'     => $row['stock_status'],
				'image'            => $row['image'],
				'manufacturer_id'  => $row['manufacturer_id'],
				'manufacturer'     => $row['manufacturer'],
				'price'            => ($row['discount'] ? $row['discount'] : $row['price']),
				'special'          => $row['special'],
				'reward'           => $row['reward'],
				'points'           => $row['points'],
				'tax_class_id'     => $row['tax_class_id'],
				'date_available'   => $row['date_available'],
				'weight'           => $row['weight'],
				'weight_class_id'  => $row['weight_class_id'],
				'length'           => $row['length'],
				'width'            => $row['width'],
				'height'           => $row['height'],
				'length_class_id'  => $row['length_class_id'],
				'subtract'         => $row['subtract'],
				'rating'           => round($row['rating']),
				'reviews'          => $row['reviews'] ? $row['reviews'] : 0,
				'minimum'          => $row['minimum'],
				'sort_order'       => $row['sort_order'],
				'status'           => $row['status'],
				'date_added'       => $row['date_added'],
				'date_modified'    => $row['date_modified'],
				'viewed'           => $row['viewed']
			);
		} else {
			return false;
		}
		return $product_data;
	}

in function getProducts (same file)

change:

Code: Select all

		foreach ($query->rows as $result) {
			$product_data[$result['product_id']] = $this->getProduct($result['product_id']);
		}

into:

Code: Select all

		// prepare statement product query
		$prep = $this->getProductPrepare();
		foreach ($query->rows as $result) {
			// fetch product data using prepared statement
			$product_data[$result['product_id']] = $this->getProductPrepared($result['product_id'],$prep);
			
			// old statement
			//$product_data[$result['product_id']] = $this->getProduct($result['product_id'],true);
		}
		
		$prep->free_result();
		$prep->close();


enjoy.

Crystal Light Centrum Taiwan
Extensions: MailQueue | SUKHR | VBoces

“Data security is paramount at [...], and we are committed to protecting the privacy of anyone who is associated with our [...]. We’ve made a lot of improvements and will continue to make them.”
When you know your life savings are gone.


User avatar
Expert Member

Posts

Joined
Fri Aug 18, 2017 4:35 pm
Location - Taiwan

Post by straightlight » Thu Jul 18, 2019 8:22 pm

Using the prepare statement this way requires double locations to pull the queries which can create more complications during troubleshooting. The best path would be by keeping a single SQL statement by modifying the system/library/db files in the folder.

Dedication and passion goes to those who are able to push and merge a project.

Regards,
Straightlight
Programmer / Opencart Tester


Legendary Member

Posts

Joined
Mon Nov 14, 2011 11:38 pm
Location - Canada, ON

Post by letxobnav » Thu Jul 18, 2019 8:50 pm

perhaps, then again, the product query currently used for a single product and the one used when fetching the product details for the category lists should never have been the same. Now that query is shared (for convenience I guess) and all data is queried and mapped into an array most of which is never used in the category listings.

Crystal Light Centrum Taiwan
Extensions: MailQueue | SUKHR | VBoces

“Data security is paramount at [...], and we are committed to protecting the privacy of anyone who is associated with our [...]. We’ve made a lot of improvements and will continue to make them.”
When you know your life savings are gone.


User avatar
Expert Member

Posts

Joined
Fri Aug 18, 2017 4:35 pm
Location - Taiwan

Post by straightlight » Thu Jul 18, 2019 9:26 pm

Nevertheless, my PDO extension, for instance, I used to published on the Marketplace before the core version was released was able to do this without a single model modification. The prepare method statement needs to work along with the query method inside the library constructors.

Dedication and passion goes to those who are able to push and merge a project.

Regards,
Straightlight
Programmer / Opencart Tester


Legendary Member

Posts

Joined
Mon Nov 14, 2011 11:38 pm
Location - Canada, ON
Who is online

Users browsing this forum: jagall, moreduff and 268 guests