Slow kernel SQL queries optimization

« 
Previous topic
|
Developer
|
Next topic
 »
Author Message

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

Piotrek Karaś

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

--
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.

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.

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

Piotrek Karaś

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 eZ Publish 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?

Thanks,
Piotrek

--
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

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

André R.

Thursday 26 March 2009 7:58:49 am

See comment in issue.

( http://issues.ez.no/14471 )

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

Piotrek Karaś

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 eZ Publish will force the old complex approach?

Cheers,
Piotrek

--
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

Nicklas Lundgren

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

Nicklas Lundgren, Managing Director
Novitell AB, Sweden
Powered by eZ Publish™ CMS Open Source Web Content Management. Copyright © 1999-2014 eZ Systems AS (except where otherwise noted). All rights reserved.