duplicate queries in db operation

Quote

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!
diegospm
Newbie
Posts
17
Joined
Thu Apr 18, 2019 8:49 am
Top

Re: duplicate queries in db operation

Quote

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

Because the languages are variable - can change.

-
Image Image Image Image
VIEW ALL EXTENSIONS * EXTENSION SUPPORT * WEBSITE * CUSTOM REQUESTS

User avatar
grgr
Active Member
Posts
1527
Joined
Mon Mar 28, 2011 4:08 pm
Location - UK
Top

Re: duplicate queries in db operation

Quote

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.
diegospm
Newbie
Posts
17
Joined
Thu Apr 18, 2019 8:49 am
Top

Re: duplicate queries in db operation

Quote

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
D3MO
Active Member
Posts
418
Joined
Mon Apr 04, 2011 6:57 am
Top

Re: duplicate queries in db operation

Quote

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.
diegospm
Newbie
Posts
17
Joined
Thu Apr 18, 2019 8:49 am
Top
Post Reply
Return to “Concepts”
Who is online

Users browsing this forum: No registered users and 127 guests