Post by diegospm » Fri Apr 19, 2019 7:41 am

Hi,

I noticed that some operations on the models perform unnecessary queries (from my point of view).

For example:

Code: Select all

$this->db->query("DELETE FROM " . DB_PREFIX . "product_description WHERE product_id = '" . (int)$product_id . "'");

foreach ($data['product_description'] as $language_id => $value) {
$this->db->query("INSERT INTO " . DB_PREFIX . "product_description SET product_id = '" . (int)$product_id . "', language_id = '" . (int)$language_id . "', name = '" . $this->db->escape($value['name']) . "', description = '" . $this->db->escape($value['description']) . "', tag = '" . $this->db->escape($value['tag']) . "', meta_title = '" . $this->db->escape($value['meta_title']) . "', meta_description = '" . $this->db->escape($value['meta_description']) . "', meta_keyword = '" . $this->db->escape($value['meta_keyword']) . "'");
}
Could be like that:

Code: Select all

foreach ($data['product_description'] as $language_id => $value) {
$this->db->query("UPDATE " . DB_PREFIX . "product_description SET language_id = '" . (int)$language_id . "', name = '" . $this->db->escape($value['name']) . "', description = '" . $this->db->escape($value['description']) . "', tag = '" . $this->db->escape($value['tag']) . "', meta_title = '" . $this->db->escape($value['meta_title']) . "', meta_description = '" . $this->db->escape($value['meta_description']) . "', meta_keyword = '" . $this->db->escape($value['meta_keyword']) . "' WHERE product_id = '" . (int)$product_id . "'");
}
Is there any specific reason for deletion and addition to occur instead of the update?

Thanks!

Newbie

Posts

Joined
Thu Apr 18, 2019 8:49 am

Post by grgr » Sun Apr 21, 2019 6:28 pm

Because the languages are variable - can change.

Image Image Image Image Image Image Image Image


User avatar
Active Member

Posts

Joined
Mon Mar 28, 2011 4:08 pm
Location - UK

Post by diegospm » Sun Apr 21, 2019 9:34 pm

grgr wrote:
Sun Apr 21, 2019 6:28 pm
Because the languages are variable - can change.
But in the example I mentioned I also inserted the language_id to be updated.

The difference is that in the current mode a new DB key is used and in the case I mentioned the key will be reused.

This occurs with other inserts, such as produtc_filter, product_related, and so on.

I still do not understand the reason for this duplicate operation.

Newbie

Posts

Joined
Thu Apr 18, 2019 8:49 am

Post by D3MO » Mon Apr 22, 2019 7:00 am

Your update sql will update all languages with the last in foreach found:)
Your Where clause:

Code: Select all

WHERE product_id = '" . (int)$product_id . "'"
you forgot to add and language_id check in where clause

And as you can delete languages anytime and add new ones. You can end up with lots of entries in database for non existing (anymore) languages with descriptions. But yes looking from the view of performance delete and insert is 2 processes:) and update is 1:) Update should be faster as it doesnt require to reindex after update, after delete and insert it has to reindex.


diegospm wrote:
Sun Apr 21, 2019 9:34 pm

But in the example I mentioned I also inserted the language_id to be updated.

The difference is that in the current mode a new DB key is used and in the case I mentioned the key will be reused.

This occurs with other inserts, such as produtc_filter, product_related, and so on.

I still do not understand the reason for this duplicate operation.

Opencart Expert | voldemaras@gmail.com
Skype - programanija | Gtalk - voldemaras@gmail.com
Extensions for Opencart @ https://www.opencartextensions.eu / or Opencart Marketplace

Need Custom Module? debug third party module or simply have any question related to Opencart? feel free to contact directly for a live chat session:) - INSTANT LIVE CHAT


User avatar
Active Member

Posts

Joined
Mon Apr 04, 2011 6:57 am

Post by diegospm » Tue Apr 23, 2019 8:46 am

Ah yes, I did not pay attention to this small detail ;D

My intention was precisely to raise the discussion about performance.

But in that case, could not we do a check (if) before? Obviously if this were not to compromise performance, which is the crux of the matter.
D3MO wrote:
Mon Apr 22, 2019 7:00 am
Your update sql will update all languages with the last in foreach found:)
Your Where clause:

Code: Select all

WHERE product_id = '" . (int)$product_id . "'"
you forgot to add and language_id check in where clause

And as you can delete languages anytime and add new ones. You can end up with lots of entries in database for non existing (anymore) languages with descriptions. But yes looking from the view of performance delete and insert is 2 processes:) and update is 1:) Update should be faster as it doesnt require to reindex after update, after delete and insert it has to reindex.


diegospm wrote:
Sun Apr 21, 2019 9:34 pm

But in the example I mentioned I also inserted the language_id to be updated.

The difference is that in the current mode a new DB key is used and in the case I mentioned the key will be reused.

This occurs with other inserts, such as produtc_filter, product_related, and so on.

I still do not understand the reason for this duplicate operation.

Newbie

Posts

Joined
Thu Apr 18, 2019 8:49 am
Who is online

Users browsing this forum: No registered users and 10 guests