Forums / Developer / Slow kernel SQL queries optimization
Piotrek Karaś
Friday 20 February 2009 6:52:13 am
Has anyone ever succeeded in optimizing those:
SELECT ezcontentobject_attribute.*, ezcontentclass_attribute.identifier as identifier FROM ezcontentobject_attribute, ezcontentclass_attribute WHERE ezcontentclass_attribute.version = '0' AND ezcontentclass_attribute.id = ezcontentobject_attribute.contentclassattribute_id AND ( ( ezcontentobject_attribute.version = '3' AND ezcontentobject_attribute.contentobject_id = '15539' AND ezcontentobject_attribute.language_code = 'pol-PL' ) OR ( ezcontentobject_attribute.version = '1' AND ezcontentobject_attribute.contentobject_id = '13743' AND ezcontentobject_attribute.language_code = 'pol-PL' ) OR ( ezcontentobject_attribute.version = '3' AND ezcontentobject_attribute.contentobject_id = '15540' AND ezcontentobject_attribute.language_code = 'pol-PL' ) OR ( ezcontentobject_attribute.version = '1' AND ezcontentobject_attribute.contentobject_id = '9960' AND ezcontentobject_attribute.language_code = 'pol-PL' ) OR ( ezcontentobject_attribute.version = '1' AND ezcontentobject_attribute.contentobject_id = '10982' AND ezcontentobject_attribute.language_code = 'pol-PL' ) OR ( ezcontentobject_attribute.version = '1' AND ezcontentobject_attribute.contentobject_id = '6858' AND ezcontentobject_attribute.language_code = 'pol-PL' ) OR ( ezcontentobject_attribute.version = '2' AND ezcontentobject_attribute.contentobject_id = '11000' AND ezcontentobject_attribute.language_code = 'pol-PL' ) OR ( ezcontentobject_attribute.version = '3' AND ezcontentobject_attribute.contentobject_id = '1271' AND ezcontentobject_attribute.language_code = 'pol-PL' ) OR ( ezcontentobject_attribute.version = '1' AND ezcontentobject_attribute.contentobject_id = '11340' AND ezcontentobject_attribute.language_code = 'pol-PL' ) OR ( ezcontentobject_attribute.version = '1' AND ezcontentobject_attribute.contentobject_id = '11341' AND ezcontentobject_attribute.language_code = 'pol-PL' ) OR ( ezcontentobject_attribute.version = '5' AND ezcontentobject_attribute.contentobject_id = '10983' AND ezcontentobject_attribute.language_code = 'pol-PL' ) OR ( ezcontentobject_attribute.version = '1' AND ezcontentobject_attribute.contentobject_id = '15477' AND ezcontentobject_attribute.language_code = 'pol-PL' ) OR ( ezcontentobject_attribute.version = '3' AND ezcontentobject_attribute.contentobject_id = '12730' AND ezcontentobject_attribute.language_code = 'pol-PL' ) OR ( ezcontentobject_attribute.version = '2' AND ezcontentobject_attribute.contentobject_id = '15479' AND ezcontentobject_attribute.language_code = 'pol-PL' ) OR ( ezcontentobject_attribute.version = '1' AND ezcontentobject_attribute.contentobject_id = '10980' AND ezcontentobject_attribute.language_code = 'pol-PL' ) OR ( ezcontentobject_attribute.version = '2' AND ezcontentobject_attribute.contentobject_id = '11545' AND ezcontentobject_attribute.language_code = 'pol-PL' ) OR ( ezcontentobject_attribute.version = '2' AND ezcontentobject_attribute.contentobject_id = '1925' AND ezcontentobject_attribute.language_code = 'pol-PL' ) OR ( ezcontentobject_attribute.version = '1' AND ezcontentobject_attribute.contentobject_id = '7650' AND ezcontentobject_attribute.language_code = 'pol-PL' ) OR ( ezcontentobject_attribute.version = '2' AND ezcontentobject_attribute.contentobject_id = '2566' AND
?
And where/what are those queries used for?
-- Company: mediaSELF Sp. z o.o., http://www.mediaself.pl eZ references: http://ez.no/partners/worldwide_partners/mediaself eZ certified developer: http://ez.no/certification/verify/272585 eZ blog: http://ez.ryba.eu
André R.
Friday 20 February 2009 7:57:53 am
Pre loading of content object attributes when you fetch nodes using content 'list' or 'tree' fetch:http://issues.ez.no/IssueView.php?Id=14471&activeItem=10&rv[]=1055&rm=1&column=8&sortOrder=4
eZ Online Editor 5: http://projects.ez.no/ezoe || eZJSCore (Ajax): http://projects.ez.no/ezjscore || eZ Publish EE http://ez.no/eZPublish/eZ-Publish-Enterprise-Subscription @: http://twitter.com/andrerom
Friday 20 February 2009 8:40:30 am
Hi Andre,
Thanks for quick reply!
As far as I understand it correctly, when is switch the default setting off, a number of lightweight direct fetch queries will be used instead of this gigantic "OR compound", right? This should not affect the functionality in any way?
Thanks,Piotrek
Tuesday 24 February 2009 3:49:50 am
No affect, only change is by default use more smaller faster sql calls instead of one gigantic one when you fetch for instance above 15 nodes.
Sunday 01 March 2009 1:12:46 pm
OK, seems to be working fine.
Just to make sure: before you implement a INI-based setting (or whatever) in Exponential 4.1.x, if I disable the load_data_map by default in 4.0.x, all my lists will be generated based on small queries, no matter how many items fetched, right? There is no internal limit of 15, right?
Nicklas Lundgren
Thursday 26 March 2009 6:31:12 am
Hi, I have severe troubles with sloooow queries like the one described in this thread. I see that this has been solved in 4.1, but what about eZ 3.8.6?I am running a huge site with about 100 000 content objects. Fetches like these can take up to several minutes to perform.
Does anyone have a clue on how to fix this in eZ 3.8.6?
Best regards,/Nicklas
Nicklas Lundgren, Managing Director Novitell AB, Sweden
Thursday 26 March 2009 7:58:49 am
See comment in issue.
( http://issues.ez.no/14471 )
Thursday 26 March 2009 9:28:17 pm
Just an update - works fine up till now. I think we will set it as default for all our implementations...
By the way - in 4.1 will there be a fetch list count under which Exponential will force the old complex approach?
Cheers,Piotrek
Friday 27 March 2009 12:36:21 am
Hi,Thanks for your replies!
It sounds really promising with this solution. I will try it out today and post my experiences from our tests here.
Best regards!/Nicklas