Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

oops.. 3000 queries !


adiwillow

Recommended Posts

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...