Page 1 of 1

duplicate queries in db operation

Posted: Fri Apr 19, 2019 7:41 am
by diegospm
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!

Re: duplicate queries in db operation

Posted: Sun Apr 21, 2019 6:28 pm
by grgr
Because the languages are variable - can change.

Re: duplicate queries in db operation

Posted: Sun Apr 21, 2019 9:34 pm
by diegospm
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.

Re: duplicate queries in db operation

Posted: Mon Apr 22, 2019 7:00 am
by D3MO
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.

Re: duplicate queries in db operation

Posted: Tue Apr 23, 2019 8:46 am
by diegospm
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.