Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

Site running 734 queries, site shutdown


protogabe

Recommended Posts

Hey all:

 

I'm hoping someone can clear this up. My host shutdown my osC site stating it was overload their system resources. After much back and forth they allowed me access to bugfix. I installed the Query Contrib and nightmarishly discovered my site was performing 2074 QUERIES!!!!

 

I disabled the category count and now Im at 734 . 700 of these queries are these category queries which look like this:

 

" [712] => 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 = '673' order by c.sort_order, cd.categories_name

[713] => 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 = '674' order by c.sort_order, cd.categories_name

[714] => 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 = '675' order by c.sort_order, cd.categories_name

[715] => 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 = '665' order by c.sort_order, cd.categories_name

[716] => 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 = '678' order by c.sort_order, cd.categories_name

[717] => 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 = '683' order by c.sort_order, cd.categories_name

[718] => 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 = '684' order by c.sort_order, cd.categories_name

[719] => 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 = '685' order by c.sort_order, cd.categories_name

[720] => 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 = '686' order by c.sort_order, cd.categories_name

[721] => 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 = '687' order by c.sort_order, cd.categories_name

[722] => 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 = '688' order by c.sort_order, cd.categories_name

[723] => 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 = '689' order by c.sort_order, cd.categories_name

[724] => 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 = '690' order by c.sort_order, cd.categories_name

[725] => 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 = '691' order by c.sort_order, cd.categories_name

[726] => 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 = '692' order by c.sort_order, cd.categories_name

[727] => 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 = '27' order by c.sort_order, cd.categories_name

[728] => 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 = '693' order by c.sort_order, cd.categories_name

[729] => 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 = '681' order by c.sort_order, cd.categories_name

[730] => 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 = '28' order by c.sort_order, cd.categories_name

[731] => 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 = '29' order by c.sort_order, cd.categories_name

[732] => 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 = '30' order by c.sort_order, cd.categories_name

[733] => 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 = '31' order by c.sort_order, cd.categories_name

)"

 

Of course, that's only the last few lines. I read every post on here about optimization and it seems to be something with the cateogory dropdown or search box, disabling it or something, but I don't even use that and nor do I know how to disable the code. My site is STS enabled by the way.

 

Any suggestions wise osC mentors? My host will not allow my osC site back up unless i get the queries under to 30 so Im patiently waiting for any response on this. thanks.

 

Gabe

Link to comment
Share on other sites

Ditching STS and adding these should help..

 

 

 

Collapsible CSS Category Menu

 

Page Cache

 

Optimize also_purchased_products module

 

 

In addition there are other contribs and tips and trick in the forum for how to get your db queries count down.

 

But anyway a host having 30 queries as a max limit frankly sucks , so i would suggest a change of host.

Link to comment
Share on other sites

Ditching STS and adding these should help..

Collapsible CSS Category Menu

 

Page Cache

 

Optimize also_purchased_products module

In addition there are other contribs and tips and trick in the forum for how to get your db queries count down.

 

But anyway a host having 30 queries as a max limit frankly sucks , so i would suggest a change of host.

 

Thanks toyicebear but I knew about those contributions and am not in a position to ditch STS as the whole site works around it. I would have even more work on my hands if I do that.

 

My original question still holds.

Link to comment
Share on other sites

Thanks toyicebear but I knew about those contributions and am not in a position to ditch STS as the whole site works around it. I would have even more work on my hands if I do that.

 

My original question still holds.

 

LOL,

 

"as the whole site works around it."

 

The site's not working around it if the host has shut it down.

 

 

But anyway a host having 30 queries as a max limit frankly sucks , so i would suggest a change of host.

 

Good point. I'm always surprised at how many people jump through hoops trying to get around crappy hosting when they could just move the site to someplace else.

 

There are excellent hosting options out there now for low prices. Get an account set up, a temporary domain name, load the store and try them out. If you're not happy try another.

 

Lots of them offer 90 day money back guarantees.

Local: Mac OS X 10.5.8 - Apache 2.2/php 5.3.0/MySQL 5.4.10 • Web Servers: Linux

Tools: BBEdit, Coda, Versions (Subversion), Sequel Pro (db management)

Link to comment
Share on other sites

Thanks toyicebear but I knew about those contributions and am not in a position to ditch STS as the whole site works around it. I would have even more work on my hands if I do that.
Not really, you may have some extra work, but going from STS to BTS or even a non template system, is some work, but then the site is easier to debug and in my opinion easier to add contributions in the future. I used STS and then tried to implement a contribution, and as far as I am concerned it is a pain in the butt to work with STS, and I will not use it for any sites if possible.

 

cheers,

Peter M.

Peter McGrath

-----------------------------

See my Profile (click here) for more information and to contact me for professional osCommerce support that includes SEO development, custom development and security implementation

Link to comment
Share on other sites

LOL,

 

"as the whole site works around it."

 

The site's not working around it if the host has shut it down.

Good point. I'm always surprised at how many people jump through hoops trying to get around crappy hosting when they could just move the site to someplace else.

 

There are excellent hosting options out there now for low prices. Get an account set up, a temporary domain name, load the store and try them out. If you're not happy try another.

 

Lots of them offer 90 day money back guarantees.

 

My host is very reputable and their concerns are valid. I'm hoping that I can open now that i've gotten queries down to 719. Migrating to another host is out of the question as SSL certificates have been paid for the specific IP the site runs on and I'm not about to hit the client up with another bill for something that is totally within the realm of repair.

 

Also, STS is not the reason behind the 700 extraneous queries. I installed all of Chemo's mods, none of them made a diff, actually his CSS Catalog one increased my queries to 3718. Funny, the page is hardcoded with links and no category pages even need to be queried, so its useless besides.

 

I troubleshooted and commented out every line application_top.php to see what was making the malicious calls but was unable to locate the loop. I did this for a number of files in the include folders, commenting out lines that looked like calls to the catalog dbase, with no luck.

 

On a postive note the index.php page loads in .07 seconds, so even though it does 718 supposed queries, the page loads super fast.

Link to comment
Share on other sites

I hope you figure it out. Worse comes to worse, you could just pay somebody like Chemo to fix up the code. It probably wouldn't take them very long if they knew what they were doing.

 

Make sure to post a link to your site once it's back on. That way, us forum-users can let you know how we find your site, i.e. if it is fast or sluggish etc.

 

--Peter

CE PHOENIX SUPPORTER

Support the Project, go PRO and get access to certified add ons

Full-time I am a C-suite executive of a large retail company in Australia. In my spare time, I enjoying learning about web-design.

Download the latest version of CE Phoenix from gitHub here

Link to comment
Share on other sites

I hope you figure it out. Worse comes to worse, you could just pay somebody like Chemo to fix up the code. It probably wouldn't take them very long if they knew what they were doing.

 

Make sure to post a link to your site once it's back on. That way, us forum-users can let you know how we find your site, i.e. if it is fast or sluggish etc.

 

--Peter

 

Pete, thanks for the motivation. I contacted Chemo's guy Chris and the charge was too high. Anyway, I thought about it and realized STS is the culprit so I went through all the files STS installs and began commenting out things that look like category loop calls.

 

Anyway, long story short.

 

 

Here's the solution. In sts_user_code.php, kill these lines:

 

 

$sts_block_name = 'catmenu';
require(STS_START_CAPTURE);
  echo "\n<!-- Start Category Menu -->\n";
  echo tep_draw_form('goto', FILENAME_DEFAULT, 'get', '');
echo tep_draw_pull_down_menu('cPath', tep_get_category_tree(), $current_category_id, 'onChange="this.form.submit();"');
echo "</form>\n";
echo "<!-- End Category Menu -->\n";
require(STS_STOP_CAPTURE);
$template[$sts_block_name] = $sts_block[$sts_block_name];

 

Went from 719 Queries to 43. Amazing.

 

Thanks for the help guys!

Link to comment
Share on other sites

Just another reason not to use STS. I have found that the use of this contribution while it is ok for some stuff, it raises issues and lots of additional code requirements/overhead that are not needed in a template system.

 

Glad you found your issue :)

 

cheers,

Peter M.

Peter McGrath

-----------------------------

See my Profile (click here) for more information and to contact me for professional osCommerce support that includes SEO development, custom development and security implementation

Link to comment
Share on other sites

I'm very happy you worked it out! That wouldn't have been very easy to do. Make sure to post the fix in the STS contrib section or the STS support thread. I'm sure there are others experiencing similar problems...

 

--Peter

CE PHOENIX SUPPORTER

Support the Project, go PRO and get access to certified add ons

Full-time I am a C-suite executive of a large retail company in Australia. In my spare time, I enjoying learning about web-design.

Download the latest version of CE Phoenix from gitHub here

Link to comment
Share on other sites

Pete, thanks for the motivation. I contacted Chemo's guy Chris and the charge was too high. Anyway, I thought about it and realized STS is the culprit so I went through all the files STS installs and began commenting out things that look like category loop calls.

 

Anyway, long story short.

Here's the solution. In sts_user_code.php, kill these lines:

$sts_block_name = 'catmenu';
require(STS_START_CAPTURE);
  echo "\n<!-- Start Category Menu -->\n";
  echo tep_draw_form('goto', FILENAME_DEFAULT, 'get', '');
echo tep_draw_pull_down_menu('cPath', tep_get_category_tree(), $current_category_id, 'onChange="this.form.submit();"');
echo "</form>\n";
echo "<!-- End Category Menu -->\n";
require(STS_STOP_CAPTURE);
$template[$sts_block_name] = $sts_block[$sts_block_name];

 

Went from 719 Queries to 43. Amazing.

 

Thanks for the help guys!

 

I too am having the same problem and am not 'smart' enough (lol) to uninstall STS and go without a template system. I tried killing those lines above and my querie for the homepage is still above 4,000 and for categories is above 10,000. Anyone else have any other suggestions?

 

Thanks in advance.

 

Autumn (www.better-beads.com)

Link to comment
Share on other sites

Awe man,,, this sounds like a real issue to be concerned about. I'm glad you all posted what you did, because I downloaded the contrib, but I haven't installed.

 

I'm glad for these posts.

 

 

Kevin

"What I didn't know yesterday, I know today & will remember tomorrow"

(By Kwalker)

 

What do you see when you open up the tep_database-pr2.2-CVS.pdf file that came with your osCommerce download?

Link to comment
Share on other sites

Archived

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

×
×
  • Create New...