oops.. 3000 queries !

[adiwillow]

Hi all, i'm trying to debug my slow site.

 

It has a lot of products, which are slowly being whittled down but is still near the 10,000 mark. It also has a few contribs such as easy populate (products are produced from an excel file) and various odds and sods.

 

Anyway, the pages are coming back at 3000+ queries !!!

 

Cany anyone put me on the right track to begin to reduce this? there is obviously something wrong somewhere.. perhaps a loop? I've been trudging through chemos speed optimiser thread until i realised there was something majorly wrong.

 

Can anyone help? I can post things if you tell me what they are...

 

Thanks

Adi

[adiwillow]

forgot to give the url...

 

http://www.thetoolshop.co.uk

[Guest]

forgot to give the url...

 

http://www.thetoolshop.co.uk

 

Hate to tell you but with that many products, you can not successfully use the Simplet Template System. You will need to get rid of STS and your queries will go down to the normal range.

 

I just recently had to do this.

 

Autumn

[adiwillow]

Hate to tell you but with that many products, you can not successfully use the Simplet Template System. You will need to get rid of STS and your queries will go down to the normal range.

 

I just recently had to do this.

 

Autumn

 

you're kidding ... seriously? why should the amount of queries go up with STS?

 

Can you be 100% sure that this is what it is causing it?

 

Will i have the same problem with BTS?

[user99999999]

I think its because the dhtml menu is built and included in every page request. It really needs to be an admin function that builds categories.js when you edit categories and then include categories.js after coolmenu.js in your pages. Your categories is 85k worth of code so having it in a seperate .js will allow the browser to cache it increasing page load time and decreasing sql queries. There is more things you can do but you might want to check that by commenting the menu in column_left.php and checking the queries.

[minghoo]

I think its because the dhtml menu is built and included in every page request. It really needs to be an admin function that builds categories.js when you edit categories and then include categories.js after coolmenu.js in your pages. Your categories is 85k worth of code so having it in a seperate .js will allow the browser to cache it increasing page load time and decreasing sql queries. There is more things you can do but you might want to check that by commenting the menu in column_left.php and checking the queries.

 

 

Here is good tools to direct your to optimize your store.

 

http://www.websiteoptimization.com/services/analyze/

 

 

sean

[adiwillow]

I think its because the dhtml menu is built and included in every page request. It really needs to be an admin function that builds categories.js when you edit categories and then include categories.js after coolmenu.js in your pages. Your categories is 85k worth of code so having it in a seperate .js will allow the browser to cache it increasing page load time and decreasing sql queries. There is more things you can do but you might want to check that by commenting the menu in column_left.php and checking the queries.

 

i've tried disabling coolmenu from displaying in the template page, but i've not actually stopped it from being called. I might try that next..cheers.

[adiwillow]

the javascript is in a seperate file though.. called from the head of the sts template file..

 

 

<script LANGUAGE="JavaScript1.2" type="text/javascript" src="includes/menu_animation.js"></script>

 

 

so it not strictly being called by each page is it?

 

Anyway, i removed it, and it reduced the queries by about a thousand... but there were still over 2000 of the things... i reckon there is another problem elsewhere....

 

any other ideas? I'm beginning to think along the lines of what someo ne said earlier and remove the STS.

[adiwillow]

okay, i've got hundreds and hundreds of these...

 

QUERY select c.categories_id, cd.categories_name, c.parent_id from categories c, categories_description cd where c.categories_id = cd.categories_id and cd.language_id = '1' and c.parent_id = '394' order by c.sort_order, cd.categories_name
RESULT Resource id #4112 
QUERY select c.categories_id, cd.categories_name, c.parent_id from categories c, categories_description cd where c.categories_id = cd.categories_id and cd.language_id = '1' and c.parent_id = '422' order by c.sort_order, cd.categories_name
RESULT Resource id #4115 
QUERY select c.categories_id, cd.categories_name, c.parent_id from categories c, categories_description cd where c.categories_id = cd.categories_id and cd.language_id = '1' and c.parent_id = '415' order by c.sort_order, cd.categories_name
RESULT Resource id #4118 
QUERY select c.categories_id, cd.categories_name, c.parent_id from categories c, categories_description cd where c.categories_id = cd.categories_id and cd.language_id = '1' and c.parent_id = '851' order by c.sort_order, cd.categories_name
RESULT Resource id #4121 
QUERY select c.categories_id, cd.categories_name, c.parent_id from categories c, categories_description cd where c.categories_id = cd.categories_id and cd.language_id = '1' and c.parent_id = '819' order by c.sort_order, cd.categories_name
RESULT Resource id #4124 
QUERY select c.categories_id, cd.categories_name, c.parent_id from categories c, categories_description cd where c.categories_id = cd.categories_id and cd.language_id = '1' and c.parent_id = '857' order by c.sort_order, cd.categories_name
RESULT Resource id #4127 
QUERY select c.categories_id, cd.categories_name, c.parent_id from categories c, categories_description cd where c.categories_id = cd.categories_id and cd.language_id = '1' and c.parent_id = '886' order by c.sort_order, cd.categories_name
RESULT Resource id #4130 
QUERY select c.categories_id, cd.categories_name, c.parent_id from categories c, categories_description cd where c.categories_id = cd.categories_id and cd.language_id = '1' and c.parent_id = '1056' order by c.sort_order, cd.categories_name
RESULT Resource id #4133 
QUERY select c.categories_id, cd.categories_name, c.parent_id from categories c, categories_description cd where c.categories_id = cd.categories_id and cd.language_id = '1' and c.parent_id = '1058' order by c.sort_order, cd.categories_name

 

anyone know why i've got so many?

[adiwillow]

okay..slowly chipping away at this.. the above code is what is generated from the coolmenu.. i need a menu of some sort so i cant get rid of that. Underneath it i also had a top level category list in case the browser was stopping the coolmenu. Thats now gone as well...

 

But, something is calling this query, which as far as i can see isnt needed...

 

QUERY select parent_id from categories where categories_id = '884'
RESULT Resource id #3646 
QUERY select parent_id from categories where categories_id = '548'
RESULT Resource id #3649 
QUERY select parent_id from categories where categories_id = '884'
RESULT Resource id #3652 
QUERY select parent_id from categories where categories_id = '549'
RESULT Resource id #3655 
QUERY select parent_id from categories where categories_id = '884'
RESULT Resource id #3658 
QUERY select parent_id from categories where categories_id = '550'
RESULT Resource id #3661 
QUERY select parent_id from categories where categories_id = '884'

 

is this the standard OS category query? can i turn it off? obviously with coolmenu active i dont need it.. do i?

[user99999999]

This is the menu source code.

 

<script LANGUAGE="JavaScript1.2" type="text/javascript" src="includes/menu_animation.js"></script>

 

The menu content comes from the include below and is embeded in the html page, view source to see it.

Not sure what file for your template but should be after the body tag before the header include.

<body marginwidth="0" marginheight="0" topmargin="0" >

<!-- coolMenu //-->
<?php require(DIR_WS_INCLUDES . 'coolmenu.php'); ?>
<!-- coolMenu_eof //-->

<!-- header //-->
<?php require(DIR_WS_INCLUDES . 'header.php'); ?>
<!-- header_eof //-->

 

Then the coolmenu category box is included in column_left.php.

 

You should be able to remove the above two things and change coolmenu.php to categories.php in column_left.php

[user99999999]

Also you might disable the also purchased section it uses a lot.

 

Try the config cache it saves 200+

http://www.oscommerce.com/community/contributions,1862

 

Add some indexs to your tables to speed it up, the times should less then 1.

 

I expect if you installed a clean osc and sample data you would see times like 0.175 on your server.

[adiwillow]

well, the coolmenu is called directly after the opening body tag in my template.

 

But, i dont really want to ditch coolmenu. I need a tiered menu and coolmenu works okay. What i really need to get rid of are the secondary queries that are appearing from somewhere...

 

QUERY select parent_id from categories where categories_id = '884'

RESULT Resource id #3646

QUERY select parent_id from categories where categories_id = '548'

RESULT Resource id #3649

QUERY select parent_id from categories where categories_id = '884'

RESULT Resource id #3652

QUERY select parent_id from categories where categories_id = '549'

RESULT Resource id #3655

QUERY select parent_id from categories where categories_id = '884'

RESULT Resource id #3658

QUERY select parent_id from categories where categories_id = '550'

 

What is calling these?

[user99999999]

Its the tep_get_path() function used to build the sub category links at the top

 

Sections within this category...

http://www.thetoolshop.co.uk/index.php?cPath=445_446

[adiwillow]

okay.. but these are also being called from coolmenu arent they? so they arent really needed? so can i not ditch those queries?

 

Thanks for all the help by the way....