Many duplicate queries!?
416 views
Skip to first unread message
Viper
May 24, 2015, 10:58:57 AM5/24/15
to joomla-...@googlegroups.com
Hi!
I see strange errors in the debug console. Many duplicate queries in many places.
At least in article manager I see two identical queries:
and when edit article:
It's happening on the fresh Joomla installation, w/o any extensions or plugins.
Can anyone explain what's wrong?
PS! Joomla 3.4.1
I see strange errors in the debug console. Many duplicate queries in many places.
At least in article manager I see two identical queries:
SELECT u.id AS value, u.name AS text
FROM uxul5_users AS u
INNER JOIN uxul5_content AS c
ON c.created_by = u.id
GROUP BY u.id, u.name
ORDER BY u.name
and when edit article:
SELECT *
FROM uxul5_content
WHERE `id` = '1'
SELECT `t`.`id`
FROM `uxul5_tags` AS t
INNER JOIN `uxul5_contentitem_tag_map` AS m
ON `m`.`tag_id` = `t`.`id`
AND `m`.`type_alias` = 'com_content.article'
AND `m`.`content_item_id` IN ( 1)
It's happening on the fresh Joomla installation, w/o any extensions or plugins.
Can anyone explain what's wrong?
PS! Joomla 3.4.1
Robert G Mears
Jun 6, 2015, 1:47:20 PM6/6/15
to joomla-...@googlegroups.com
I've seen this too. Also Joomla 3.4.1
Sergio Manzi
Jun 6, 2015, 8:50:47 PM6/6/15
to joomla-...@googlegroups.com
Viper, Robert,
can you please give more details?
I think the only place where the second query can be generated is JHelperTags::getTagIds() (/libraries/cms/helper/tags.php, lines 464-473)
can you please give more details?
I think the only place where the second query can be generated is JHelperTags::getTagIds() (/libraries/cms/helper/tags.php, lines 464-473)
--
You received this message because you are subscribed to the Google Groups "Joomla! CMS Development" group.
To unsubscribe from this group and stop receiving emails from it, send an email to joomla-dev-cm...@googlegroups.com.
To post to this group, send email to joomla-...@googlegroups.com.
Visit this group at http://groups.google.com/group/joomla-dev-cms.
For more options, visit https://groups.google.com/d/optout.
Viper
Jun 7, 2015, 2:54:45 AM6/7/15
to joomla-...@googlegroups.com
Not only on this place. At least all 'edit item' views:
$this->form = $this->get('Form');
$this->item = $this->get('Item');
If the first line get the form and bind data into the inputs, second line get the same data from DB with the same queries. I known that the second line sometimes is necessary for other things.
$this->form = $this->get('Form');
$this->item = $this->get('Item');
If the first line get the form and bind data into the inputs, second line get the same data from DB with the same queries. I known that the second line sometimes is necessary for other things.
Sergio Manzi
Jun 7, 2015, 8:57:04 AM6/7/15
to joomla-...@googlegroups.com
OK, now I see what you mean!
In my test site home page (multilingual, a "popular tags" module published, home page is "Featured articles") I have different results depending on if I activate or not the generation of alternate tags in language filter:
Without Alt tags generation:
1 series of 6 duplicated queries
all brings back to JROOT\administrator\components\com_languages\helpers\multilangstatus.php:94
With Alt tags generation:
2 series: of duplicated queries:
1st: like above, but 9 queries
2nd: 2 queries bringing back to JROOT\plugins\system\languagefilter\languagefilter.php:608 and then to JROOT\administrator\components\com_menus\helpers\menus.php:283
I think I can fix the first series quite easly: hold on for a PR
Unsure about the second... have to look...
What are the sources of your duplicated queries?
Thanks!
Sergio
In my test site home page (multilingual, a "popular tags" module published, home page is "Featured articles") I have different results depending on if I activate or not the generation of alternate tags in language filter:
Without Alt tags generation:
1 series of 6 duplicated queries
all brings back to JROOT\administrator\components\com_languages\helpers\multilangstatus.php:94
With Alt tags generation:
2 series: of duplicated queries:
1st: like above, but 9 queries
2nd: 2 queries bringing back to JROOT\plugins\system\languagefilter\languagefilter.php:608 and then to JROOT\administrator\components\com_menus\helpers\menus.php:283
I think I can fix the first series quite easly: hold on for a PR
Unsure about the second... have to look...
What are the sources of your duplicated queries?
Thanks!
Sergio
Sergio Manzi
Jun 7, 2015, 9:19:35 AM6/7/15
to joomla-...@googlegroups.com
... have a look at https://github.com/joomla/joomla-cms/pull/7136
and see if everything is OK and if at least some of your duplicates
are now gone...
Sergio Manzi
Jun 7, 2015, 10:59:59 AM6/7/15
to joomla-...@googlegroups.com
Viper
Jun 7, 2015, 11:58:14 AM6/7/15
to joomla-...@googlegroups.com
Maybe partially fixed. But still duplicate
Look at the screenshot http://postimg.org/image/ojmsco34z/ for layout=edit
Look at the screenshot http://postimg.org/image/ojmsco34z/ for layout=edit
Sergio Manzi
Jun 7, 2015, 12:17:30 PM6/7/15
to joomla-...@googlegroups.com
Viper,
from the image there isn't much I can tell...
What would be interesting is to have the "Call stack" for each of the duplicated queries.
In any case, as they say, "your mileage may vary", and not two sites are the same and hence do not generate the same workload (executed queries).
I guess some duplication is inevitable as parametric queries are executed in different contexts and they just "happens" to be the same because called with the same parameters (but in other situation they might be called with different parameters).
... or, I think, we should "cache" our queries at an upper level (driver.php) based on the hash of the queries themselves.
Or... we could use Memcached (http://en.wikipedia.org/wiki/Memcached), if available.
In other cases, like the two I have fixed in my PRs, the queries are not parametric at all and thus it is always a waste of time to execute them more than once.
Regards,
Sergio
from the image there isn't much I can tell...
What would be interesting is to have the "Call stack" for each of the duplicated queries.
In any case, as they say, "your mileage may vary", and not two sites are the same and hence do not generate the same workload (executed queries).
I guess some duplication is inevitable as parametric queries are executed in different contexts and they just "happens" to be the same because called with the same parameters (but in other situation they might be called with different parameters).
... or, I think, we should "cache" our queries at an upper level (driver.php) based on the hash of the queries themselves.
Or... we could use Memcached (http://en.wikipedia.org/wiki/Memcached), if available.
In other cases, like the two I have fixed in my PRs, the queries are not parametric at all and thus it is always a waste of time to execute them more than once.
Regards,
Sergio
Hannes Papenberg
Jun 7, 2015, 1:32:36 PM6/7/15
to joomla-...@googlegroups.com
Duplicate queries are only really an issue with very expensive queries
and/or queries that are executed very often. With that I mean not only
several times per pageload, but on each pageload. Since you have a ratio
of maybe 1 edit view per 10,000 pageviews, duplicate queries in that
regard are neglectable and not really something to worry about. However,
if we are talking about loading the component parameters of a component
for example, executing the same query each time actually does get
expensive, since a standard Joomla page retrieves maybe 4-7 different
component parameter sets. Since it does that on each pageload, we have
70,000 queries per 10,000 pageloads. In the edit view case however, we
would only have 2 additional queries per 10,000 pageloads. So if you
optimize the system to only load the component parameters once per
pageload for all components, you are able to optimize away 60,000
additional queries per 10,000 pageloads.
Long story short: duplicate queries in the backend are neglectable,
duplicate queries in the frontend should only concern you if they are on
almost every pageload and they are either very expensive or there are
more than 2 duplicates.
Hannes
Am 07.06.2015 um 18:17 schrieb Sergio Manzi:
> Viper,
>
> from the image there isn't much I can tell...
>
> What would be interesting is to have the "Call stack" for each of the
> duplicated queries.
>
> In any case, as they say, "/your mileage may vary/", and not two sites
> are the same and hence do not generate the same workload (/executed
> queries/).
>
> I guess *some *duplication is inevitable as parametric queries are
> executed in different contexts and they just "/happens/" to be the
> same because called with the same parameters (/but in other situation
> they might be called with different parameters/).
>>>> *Without Alt tags**generation:*
>>>>> <http://www.tutor.studiofive.biz/#dbg-query-23>
>>>>> 3 duplicates: #13
>>>>> <http://www.tutor.studiofive.biz/#dbg-query-13> #16
>>>>> <http://www.tutor.studiofive.biz/#dbg-query-16> #25
>>>>> <http://www.tutor.studiofive.biz/#dbg-query-25>
>>>>> 3 duplicates: #14
>>>>> <http://www.tutor.studiofive.biz/#dbg-query-14> #17
>>>>> <http://www.tutor.studiofive.biz/#dbg-query-17> #26
>>>>> <http://www.tutor.studiofive.biz/#dbg-query-26>
and/or queries that are executed very often. With that I mean not only
several times per pageload, but on each pageload. Since you have a ratio
of maybe 1 edit view per 10,000 pageviews, duplicate queries in that
regard are neglectable and not really something to worry about. However,
if we are talking about loading the component parameters of a component
for example, executing the same query each time actually does get
expensive, since a standard Joomla page retrieves maybe 4-7 different
component parameter sets. Since it does that on each pageload, we have
70,000 queries per 10,000 pageloads. In the edit view case however, we
would only have 2 additional queries per 10,000 pageloads. So if you
optimize the system to only load the component parameters once per
pageload for all components, you are able to optimize away 60,000
additional queries per 10,000 pageloads.
Long story short: duplicate queries in the backend are neglectable,
duplicate queries in the frontend should only concern you if they are on
almost every pageload and they are either very expensive or there are
more than 2 duplicates.
Hannes
Am 07.06.2015 um 18:17 schrieb Sergio Manzi:
> Viper,
>
> from the image there isn't much I can tell...
>
> What would be interesting is to have the "Call stack" for each of the
> duplicated queries.
>
> are the same and hence do not generate the same workload (/executed
> queries/).
>
> I guess *some *duplication is inevitable as parametric queries are
> executed in different contexts and they just "/happens/" to be the
> same because called with the same parameters (/but in other situation
> they might be called with different parameters/).
> ... or, I think, we should "cache" our queries at an upper level
> (driver.php) based on the /hash /of the queries themselves.
>>>> 1 series of 6 duplicated queries
>>>> all brings back to
>>>> JROOT\administrator\components\com_languages\helpers\multilangstatus.php:94
>>>>
>>>> *With Alt tags generation:*
>>>> all brings back to
>>>> JROOT\administrator\components\com_languages\helpers\multilangstatus.php:94
>>>>
>>>> 2 series: of duplicated queries:
>>>> 1st: like above, but 9 queries
>>>> 2nd: 2 queries bringing back to
>>>> JROOT\plugins\system\languagefilter\languagefilter.php:608
>>>> and then to
>>>> JROOT\administrator\components\com_menus\helpers\menus.php:283
>>>>
>>>> I think I can fix the first series quite easly: hold on for
>>>> a PR
>>>> Unsure about the second... have to look...
>>>>
>>>> What are the sources of your duplicated queries?
>>>>
>>>> Thanks!
>>>>
>>>> Sergio
>>>>
>>>>
>>>> On 2015-06-07 05:57, Robert G Mears wrote:
>>>>> @Sergio Manzi
>>>>>
>>>>> Debug is on for this site -- albeit temporarily -- J!
>>>>> TUTOR <http://www.tutor.studiofive.biz/>.
>>>> 1st: like above, but 9 queries
>>>> 2nd: 2 queries bringing back to
>>>> JROOT\plugins\system\languagefilter\languagefilter.php:608
>>>> and then to
>>>> JROOT\administrator\components\com_menus\helpers\menus.php:283
>>>>
>>>> I think I can fix the first series quite easly: hold on for
>>>> a PR
>>>> Unsure about the second... have to look...
>>>>
>>>> What are the sources of your duplicated queries?
>>>>
>>>> Thanks!
>>>>
>>>> Sergio
>>>>
>>>>
>>>> On 2015-06-07 05:57, Robert G Mears wrote:
>>>>> @Sergio Manzi
>>>>>
>>>>> Debug is on for this site -- albeit temporarily -- J!
>>>>>
>>>>> It says (under Database Queries):
>>>>>
>>>>>
>>>>> 8 duplicate found!
>>>>>
>>>>> 2 duplicates: #2
>>>>> <http://www.tutor.studiofive.biz/#dbg-query-2> #23
>>>>> It says (under Database Queries):
>>>>>
>>>>>
>>>>> 8 duplicate found!
>>>>>
>>>>> 2 duplicates: #2
>>>>> <http://www.tutor.studiofive.biz/#dbg-query-23>
>>>>> 3 duplicates: #13
>>>>> <http://www.tutor.studiofive.biz/#dbg-query-13> #16
>>>>> <http://www.tutor.studiofive.biz/#dbg-query-16> #25
>>>>> <http://www.tutor.studiofive.biz/#dbg-query-25>
>>>>> 3 duplicates: #14
>>>>> <http://www.tutor.studiofive.biz/#dbg-query-14> #17
>>>>> <http://www.tutor.studiofive.biz/#dbg-query-17> #26
>>>>> <http://www.tutor.studiofive.biz/#dbg-query-26>
>>>>> --
>>>>> You received this message because you are subscribed to
>>>>> the Google Groups "Joomla! CMS Development" group.
>>>>> To unsubscribe from this group and stop receiving emails
>>>>> from it, send an email to joomla-dev-cm...@googlegroups.com.
>>>>> To post to this group, send email to
>>>>> joomla-...@googlegroups.com.
>>>>> Visit this group at
>>>>> http://groups.google.com/group/joomla-dev-cms
>>>>> <http://groups.google.com/group/joomla-dev-cms>.
>>>>> You received this message because you are subscribed to
>>>>> the Google Groups "Joomla! CMS Development" group.
>>>>> To unsubscribe from this group and stop receiving emails
>>>>> from it, send an email to joomla-dev-cm...@googlegroups.com.
>>>>> To post to this group, send email to
>>>>> joomla-...@googlegroups.com.
>>>>> Visit this group at
>>>>> http://groups.google.com/group/joomla-dev-cms
>>>>> For more options, visit https://groups.google.com/d/optout
>>>>> <https://groups.google.com/d/optout>.
>>>>
>>>
>>
>> --
>> You received this message because you are subscribed to the Google
>> Groups "Joomla! CMS Development" group.
>> To unsubscribe from this group and stop receiving emails from it,
>> send an email to joomla-dev-cm...@googlegroups.com
>> <mailto:joomla-dev-cm...@googlegroups.com>.
>>>
>>
>> --
>> You received this message because you are subscribed to the Google
>> Groups "Joomla! CMS Development" group.
>> To unsubscribe from this group and stop receiving emails from it,
>> send an email to joomla-dev-cm...@googlegroups.com
>> To post to this group, send email to joomla-...@googlegroups.com
>> <mailto:joomla-...@googlegroups.com>.
>> Visit this group at http://groups.google.com/group/joomla-dev-cms.
>> For more options, visit https://groups.google.com/d/optout.
>
> --
> You received this message because you are subscribed to the Google
> Groups "Joomla! CMS Development" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to joomla-dev-cm...@googlegroups.com
> <mailto:joomla-dev-cm...@googlegroups.com>.
>> For more options, visit https://groups.google.com/d/optout.
>
> --
> You received this message because you are subscribed to the Google
> Groups "Joomla! CMS Development" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to joomla-dev-cm...@googlegroups.com
> To post to this group, send email to joomla-...@googlegroups.com
> <mailto:joomla-...@googlegroups.com>.
Sergio Manzi
Jun 7, 2015, 1:44:20 PM6/7/15
to joomla-...@googlegroups.com
Very true.
After applying my PRs #7136 and #7137, which deals with very simple queries, on my (slow) PC I have a gain of about 15ms over 1500ms on my home page. This is a "free" pageload every 100, so, as I said in the PR, "nothing to write home about"...
But as this comes essentially for free... why not?
After applying my PRs #7136 and #7137, which deals with very simple queries, on my (slow) PC I have a gain of about 15ms over 1500ms on my home page. This is a "free" pageload every 100, so, as I said in the PR, "nothing to write home about"...
But as this comes essentially for free... why not?
Reply all
Reply to author
Forward
0 new messages