FalseDawn Posted March 10, 2005 Posted March 10, 2005 When optimizing a store it is always best to first optimize the queries being executed to keep the presentation dynamic Exactly. Whilst I think the page cache is a very good contribution, it can't replace doing the grunt work and figuring out why you need it in the first place (the main reason being the standard OSC way of doing things being incredibly inefficient in terms of database queries). I think the shopping cart code has to be one of the worst offenders here. With a fair amount of effort, I have managed to replace every single query relating to the shopping cart with one (yes, one!) query. This also handles specials(using a left join), and option values and pricing using dynamically constructed SQL... but that's another story. It also handles stock checking at the option level. I'm down to 19-20 queries total now, regardless of how many items are in the cart. Once I've optomised the category queries, this should easily drop 5 or 6 more as well.
Guest Posted March 10, 2005 Posted March 10, 2005 Exactly...as you have stated the queries must be optimized or else the page cache will still be very server intesive while generating the cache file. If the engine doesn't run well the cache is just a bandaide... Bobby
Guest Posted March 10, 2005 Posted March 10, 2005 Thank you for your feedback FalseDawn, Bobby!! I do appreciate the way you answered me. Sure I won't forget the help provided on this forum!!! No more Oscid in my urls even if I turn off the "force cookies" thanks to bobby's "ultimate seo contribution" thread. I also gonna follow your advice and install page cache contribution and force cookies on my site. I've succeeded in decreasing queries but not that much... still working on it. Bobby, I'm amazed by your work... where do you find time to work on so many contributions?!! May I ask you to look at the function tep_get_tax_rate I posted above to have it work with the contribution separate price per customer. You can charge me for this works. Best regards and Thanks for all!!!
RocksIT Posted March 12, 2005 Posted March 12, 2005 Ok just shaved 11 seconds off of my page load time! Great Forum Guys! If you're like me and have a large store with a ton of products, (We have 70,322 online currently) you're going to run into problems with the products featured module.. Our site is http://www.KenStantonMusic.com after adding the debug query script i noticed it was taking on average 11 seconds just for that query to run.. I guess somehow it checked every product or something.. anyway to fix I just copied the html code ouputed by the products featured module, and copyed and pasted that code into the define mainpage... much faster now.. more work as I'll have to edit products manually, but to save 11 secons i'll do it.. = 0 ) page still isn't quite optimized enough, but getting better.. we've gone from Current Parse Time: 16.856 s with 57 queries to Current Parse Time: 6.410 s with 48 queries www.PleasurePlayToys.com
RocksIT Posted March 14, 2005 Posted March 14, 2005 Ok below is my output file from the debug contrib. How do I go about combining/joining or eliminationg uneeded queries from my site? Is there a tutorial on joins? many of these features i don't think i need.. i don't use banners or display the visitor # information etc. and I only use the english language, I use the google info box for translations... any help/advice would be greatly appreciated! Thanks Current Parse Time: 6.759 s with 48 queries QUERY DEBUG: Array ( [QUERIES] => Array ( [0] => select configuration_key as cfgKey, configuration_value as cfgValue from configuration [1] => select code, title, symbol_left, symbol_right, decimal_point, thousands_point, decimal_places, value from currencies [2] => delete from whos_online where time_last_click < '1110828526' [3] => select count(*) as count from whos_online where session_id = 'da6b058ef75b45bec8fb4288a4df45ea' [4] => update whos_online set customer_id = '0', full_name = 'Guest', ip_address = '68.153.131.4', time_last_click = '1110829426', last_page_url = '/Products/index.php' where session_id = 'da6b058ef75b45bec8fb4288a4df45ea' [5] => select customers_selected_template as template_selected from customers where customers_id = '' [6] => select * from template where template_name = 'Original' [7] => select banners_id, date_scheduled from banners where date_scheduled != '' [8] => select b.banners_id, b.expires_date, b.expires_impressions, sum(bh.banners_shown) as banners_shown from banners b, banners_history bh where b.status = '1' and b.banners_id = bh.banners_id group by b.banners_id [9] => select specials_id from specials where status = '1' and now() >= expires_date and expires_date > 0 [10] => select featured_id from featured where status = '1' and now() >= expires_date and expires_date > 0 [11] => select cd.categories_head_desc_tag ,cd.categories_head_title_tag, cd.categories_head_keywords_tag from categories c, categories_description cd where c.categories_id = '0' and cd.categories_id = '0' and cd.language_id = '1' [12] => select manufacturers_name from manufacturers where manufacturers_id = '0' [13] => select display_in_column as cfgcol, infobox_file_name as cfgtitle, infobox_display as cfgvalue, infobox_define as cfgkey, box_heading, box_template, box_heading_font_color from infobox_configuration where template_id = 1 and infobox_display = "yes" and display_in_column = "left" order by location [14] => select manufacturers_id, manufacturers_name from manufacturers order by manufacturers_name [15] => select c.categories_id, cd.categories_name, c.parent_id from categories c, categories_description cd where c.parent_id = '0' and c.categories_id = cd.categories_id and cd.language_id='1' order by sort_order, cd.categories_name [16] => select count(*) as count from categories where parent_id = '451' [17] => select count(*) as count from categories where parent_id = '2' [18] => select count(*) as count from categories where parent_id = '121' [19] => select count(*) as count from categories where parent_id = '7' [20] => select count(*) as count from categories where parent_id = '5' [21] => select count(*) as count from categories where parent_id = '21' [22] => select count(*) as count from categories where parent_id = '429' [23] => select count(*) as count from categories where parent_id = '33' [24] => select count(*) as count from categories where parent_id = '59' [25] => select count(*) as count from categories where parent_id = '1' [26] => select count(*) as count from categories where parent_id = '67' [27] => select count(*) as count from categories where parent_id = '162' [28] => select count(*) as count from categories where parent_id = '432' [29] => select count(*) as count from categories where parent_id = '72' [30] => select count(*) as count from categories where parent_id = '90' [31] => select count(*) as count from categories where parent_id = '430' [32] => select count(*) as count from categories where parent_id = '431' [33] => select count(*) as count from categories where parent_id = '103' [34] => select count(*) as count from categories where parent_id = '87' [35] => select count(*) as count from categories where parent_id = '141' [36] => select count(*) as count from categories where parent_id = '433' [37] => select count(*) as count from categories where parent_id = '144' [38] => select p.products_id, pd.products_name, p.products_price, p.products_map, p.products_tax_class_id, p.products_image, s.specials_new_products_price from products p, products_description pd, specials s where p.products_status = '1' and p.products_id = s.products_id and pd.products_id = s.products_id and pd.language_id = '1' and s.status = '1' order by s.specials_date_added desc limit 10 [39] => select display_in_column as cfgcol, infobox_file_name as cfgtitle, infobox_display as cfgvalue, infobox_define as cfgkey, box_heading, box_template, box_heading_font_color from infobox_configuration where template_id = 1 and infobox_display = "yes" and display_in_column = "right" order by location [40] => update auction_basket set listing_status = 3 where time_end < now() and listing_status = 2 [41] => select a.auction_basket_id, a.ext_id, a.ext_title, a.ext_desc, a.quantity, a.price_start, a.price_end, a.insurance, a.insurance_per_item, a.insurance_option, a.shipping_type, a.shipping, a.shipping_additional, a.sales_tax, a.products_id, a.time_start, a.time_end, a.listing_status, a.auction_house_id, a.orders_id, b.ext_email_address, c.auction_item_url, d.status_label from auction_basket a, auction_winners b, auction_houses c, auction_sale_status d where b.ext_email_address = '' and a.listing_status = 3 and a.auction_winner_id = b.auction_winner_id and a.auction_house_id = c.auction_house_id and a.listing_status = d.status_id order by a.time_end desc [42] => update auction_basket set listing_status = 3 where time_end < now() and listing_status = 2 [43] => select a.auction_basket_id, a.ext_id, a.ext_title, a.ext_desc, a.quantity, a.price_start, a.price_end, a.insurance, a.insurance_per_item, a.insurance_option, a.shipping_type, a.shipping, a.shipping_additional, a.sales_tax, a.products_id, a.time_start, a.time_end, a.listing_status, a.auction_house_id, a.orders_id, b.ext_email_address, c.auction_item_url, d.status_label from auction_basket a, auction_winners b, auction_houses c, auction_sale_status d where b.ext_email_address = '' and a.listing_status = 3 and a.auction_winner_id = b.auction_winner_id and a.auction_house_id = c.auction_house_id and a.listing_status = d.status_id order by a.time_end desc [44] => SELECT information_id, languages_id, info_title FROM information WHERE visible= '1' and languages_id ='1' ORDER BY v_order [45] => select startdate, counter from counter [46] => update counter set counter = '407010' [47] => select banners_id, banners_title, banners_image, banners_html_text from banners where status = '1' and banners_group = 'googlefoot' ) [TIME] => Array ( [0] => 0.013172 [1] => 0.000574 [2] => 0.000943 [3] => 0.000363 [4] => 0.000600 [5] => 0.000929 [6] => 0.000687 [7] => 0.000420 [8] => 0.000834 [9] => 0.000428 [10] => 0.000904 [11] => 0.000537 [12] => 0.001013 [13] => 0.000978 [14] => 0.004487 [15] => 0.001602 [16] => 0.000531 [17] => 0.000459 [18] => 0.000460 [19] => 0.000447 [20] => 0.000439 [21] => 0.000441 [22] => 0.000427 [23] => 0.000449 [24] => 0.000446 [25] => 0.000436 [26] => 0.000440 [27] => 0.000434 [28] => 0.000432 [29] => 0.000465 [30] => 0.000592 [31] => 0.000460 [32] => 0.000435 [33] => 0.000459 [34] => 0.000456 [35] => 0.000450 [36] => 0.000450 [37] => 0.000451 [38] => 0.001221 [39] => 0.000990 [40] => 0.000528 [41] => 0.000977 [42] => 0.000402 [43] => 0.000992 [44] => 0.001158 [45] => 0.000416 [46] => 0.000259 [47] => 0.000381 ) www.PleasurePlayToys.com
RocksIT Posted March 14, 2005 Posted March 14, 2005 Ok below is my output file from the debug contrib. How do I go about combining/joining or eliminationg uneeded queries from my site? Is there a tutorial on joins? many of these features i don't think i need.. i don't use banners or display the visitor # information etc. and I only use the english language, I use the google info box for translations... any help/advice would be greatly appreciated! Thanks Current Parse Time: 6.759 s with 48 queries QUERY DEBUG: Array ( [QUERIES] => Array ( [0] => select configuration_key as cfgKey, configuration_value as cfgValue from configuration [1] => select code, title, symbol_left, symbol_right, decimal_point, thousands_point, decimal_places, value from currencies [2] => delete from whos_online where time_last_click < '1110828526' [3] => select count(*) as count from whos_online where session_id = 'da6b058ef75b45bec8fb4288a4df45ea' [4] => update whos_online set customer_id = '0', full_name = 'Guest', ip_address = '68.153.131.4', time_last_click = '1110829426', last_page_url = '/Products/index.php' where session_id = 'da6b058ef75b45bec8fb4288a4df45ea' [5] => select customers_selected_template as template_selected from customers where customers_id = '' [6] => select * from template where template_name = 'Original' [7] => select banners_id, date_scheduled from banners where date_scheduled != '' [8] => select b.banners_id, b.expires_date, b.expires_impressions, sum(bh.banners_shown) as banners_shown from banners b, banners_history bh where b.status = '1' and b.banners_id = bh.banners_id group by b.banners_id [9] => select specials_id from specials where status = '1' and now() >= expires_date and expires_date > 0 [10] => select featured_id from featured where status = '1' and now() >= expires_date and expires_date > 0 [11] => select cd.categories_head_desc_tag ,cd.categories_head_title_tag, cd.categories_head_keywords_tag from categories c, categories_description cd where c.categories_id = '0' and cd.categories_id = '0' and cd.language_id = '1' [12] => select manufacturers_name from manufacturers where manufacturers_id = '0' [13] => select display_in_column as cfgcol, infobox_file_name as cfgtitle, infobox_display as cfgvalue, infobox_define as cfgkey, box_heading, box_template, box_heading_font_color from infobox_configuration where template_id = 1 and infobox_display = "yes" and display_in_column = "left" order by location [14] => select manufacturers_id, manufacturers_name from manufacturers order by manufacturers_name [15] => select c.categories_id, cd.categories_name, c.parent_id from categories c, categories_description cd where c.parent_id = '0' and c.categories_id = cd.categories_id and cd.language_id='1' order by sort_order, cd.categories_name [16] => select count(*) as count from categories where parent_id = '451' [17] => select count(*) as count from categories where parent_id = '2' [18] => select count(*) as count from categories where parent_id = '121' [19] => select count(*) as count from categories where parent_id = '7' [20] => select count(*) as count from categories where parent_id = '5' [21] => select count(*) as count from categories where parent_id = '21' [22] => select count(*) as count from categories where parent_id = '429' [23] => select count(*) as count from categories where parent_id = '33' [24] => select count(*) as count from categories where parent_id = '59' [25] => select count(*) as count from categories where parent_id = '1' [26] => select count(*) as count from categories where parent_id = '67' [27] => select count(*) as count from categories where parent_id = '162' [28] => select count(*) as count from categories where parent_id = '432' [29] => select count(*) as count from categories where parent_id = '72' [30] => select count(*) as count from categories where parent_id = '90' [31] => select count(*) as count from categories where parent_id = '430' [32] => select count(*) as count from categories where parent_id = '431' [33] => select count(*) as count from categories where parent_id = '103' [34] => select count(*) as count from categories where parent_id = '87' [35] => select count(*) as count from categories where parent_id = '141' [36] => select count(*) as count from categories where parent_id = '433' [37] => select count(*) as count from categories where parent_id = '144' [38] => select p.products_id, pd.products_name, p.products_price, p.products_map, p.products_tax_class_id, p.products_image, s.specials_new_products_price from products p, products_description pd, specials s where p.products_status = '1' and p.products_id = s.products_id and pd.products_id = s.products_id and pd.language_id = '1' and s.status = '1' order by s.specials_date_added desc limit 10 [39] => select display_in_column as cfgcol, infobox_file_name as cfgtitle, infobox_display as cfgvalue, infobox_define as cfgkey, box_heading, box_template, box_heading_font_color from infobox_configuration where template_id = 1 and infobox_display = "yes" and display_in_column = "right" order by location [40] => update auction_basket set listing_status = 3 where time_end < now() and listing_status = 2 [41] => select a.auction_basket_id, a.ext_id, a.ext_title, a.ext_desc, a.quantity, a.price_start, a.price_end, a.insurance, a.insurance_per_item, a.insurance_option, a.shipping_type, a.shipping, a.shipping_additional, a.sales_tax, a.products_id, a.time_start, a.time_end, a.listing_status, a.auction_house_id, a.orders_id, b.ext_email_address, c.auction_item_url, d.status_label from auction_basket a, auction_winners b, auction_houses c, auction_sale_status d where b.ext_email_address = '' and a.listing_status = 3 and a.auction_winner_id = b.auction_winner_id and a.auction_house_id = c.auction_house_id and a.listing_status = d.status_id order by a.time_end desc [42] => update auction_basket set listing_status = 3 where time_end < now() and listing_status = 2 [43] => select a.auction_basket_id, a.ext_id, a.ext_title, a.ext_desc, a.quantity, a.price_start, a.price_end, a.insurance, a.insurance_per_item, a.insurance_option, a.shipping_type, a.shipping, a.shipping_additional, a.sales_tax, a.products_id, a.time_start, a.time_end, a.listing_status, a.auction_house_id, a.orders_id, b.ext_email_address, c.auction_item_url, d.status_label from auction_basket a, auction_winners b, auction_houses c, auction_sale_status d where b.ext_email_address = '' and a.listing_status = 3 and a.auction_winner_id = b.auction_winner_id and a.auction_house_id = c.auction_house_id and a.listing_status = d.status_id order by a.time_end desc [44] => SELECT information_id, languages_id, info_title FROM information WHERE visible= '1' and languages_id ='1' ORDER BY v_order [45] => select startdate, counter from counter [46] => update counter set counter = '407010' [47] => select banners_id, banners_title, banners_image, banners_html_text from banners where status = '1' and banners_group = 'googlefoot' ) [TIME] => Array ( [0] => 0.013172 [1] => 0.000574 [2] => 0.000943 [3] => 0.000363 [4] => 0.000600 [5] => 0.000929 [6] => 0.000687 [7] => 0.000420 [8] => 0.000834 [9] => 0.000428 [10] => 0.000904 [11] => 0.000537 [12] => 0.001013 [13] => 0.000978 [14] => 0.004487 [15] => 0.001602 [16] => 0.000531 [17] => 0.000459 [18] => 0.000460 [19] => 0.000447 [20] => 0.000439 [21] => 0.000441 [22] => 0.000427 [23] => 0.000449 [24] => 0.000446 [25] => 0.000436 [26] => 0.000440 [27] => 0.000434 [28] => 0.000432 [29] => 0.000465 [30] => 0.000592 [31] => 0.000460 [32] => 0.000435 [33] => 0.000459 [34] => 0.000456 [35] => 0.000450 [36] => 0.000450 [37] => 0.000451 [38] => 0.001221 [39] => 0.000990 [40] => 0.000528 [41] => 0.000977 [42] => 0.000402 [43] => 0.000992 [44] => 0.001158 [45] => 0.000416 [46] => 0.000259 [47] => 0.000381 ) www.PleasurePlayToys.com
RocksIT Posted March 14, 2005 Posted March 14, 2005 sorry guys not sure how that last reply posted 3 times! please forgive www.PleasurePlayToys.com
lpmccracken Posted March 15, 2005 Posted March 15, 2005 Hey Chemo, Earlier in this thread, you mentioned that you'd go over optimizing Featured Products. Is that this contribution: http://www.oscommerce.com/community/contributions,651 I'll assume for the moment that it is. Did you optimize the SQL in the Featured Products contribution on your client site (http://love-scent.com/)? If so, would you be willing to explain your changes? I think the change to the sql is in /includes/modules/featured.php, but I'd appreciate any input. I'm still using Feature Products 1.5.2 because the later updates all seem to be extra language support. The time that that one statement takes to execute is greater that all the rest combined by a large factor, and I'm only displaying three featured products (compared to your six). I haven't installed your Page Cache contribution yet, but I'm working on it. I have implemented the MS3 Tax Class. Thanks, L.P. McCracken
RocksIT Posted March 16, 2005 Posted March 16, 2005 Allright Now I have taken off the Ultimate SEO contribution and my pages times have gone down to Current Parse Time: 0.143 s with 48 queries on index.php !! right on, but could the page re-write contrib be causing that much of a slow down due to our large number of products? because thats like a 6 second drop from un-installing. www.PleasurePlayToys.com
Dacotah Posted March 16, 2005 Posted March 16, 2005 A couple of easy n00b questions: Ultimate SEO Urls 2.0b installed and working very well. But about the cache... If I reset the cache, the first reload on index.php shows 277 queries. Would this be the normal number, or is it artificially high because it is reloading the cache? On a page reload it is down to 80 queries and stays there. If I turn on the stock OCS cache (admin -> config -> cache -> true) it drops to 47 on a reload. But that should be turned off, correct?
Guest Posted March 17, 2005 Posted March 17, 2005 CODE? ? ? ? ? [15] => select count(*) as count from categories where parent_id = '27' ? ? ? ? ? [16] => select count(*) as count from categories where parent_id = '27' ? ? ? ? ? [17] => select count(*) as count from categories where parent_id = '54' ? ? ? ? ? [18] => select count(*) as count from categories where parent_id = '54' ? ? ? ? ? [19] => select count(*) as count from categories where parent_id = '66' ? ? ? ? ? [20] => select count(*) as count from categories where parent_id = '66' ? ? ? ? ? [21] => select count(*) as count from categories where parent_id = '76' ? ? ? ? ? [22] => select count(*) as count from categories where parent_id = '76' :huh: sorry to post this question again, but will someone please....help? this is just a small small section.. i have about 100 of these.. As you can see there're duplicate of every category query... how do i fix this..?? thanks a bunch!!
aromacraft Posted March 17, 2005 Posted March 17, 2005 Hello All... Any help would be greatly apreciated. I have tried the page cache and until i read this thread explains why it wont work for me i am using STS so i have stoped trying to get it to work..... my site is running slow i currently i get Current Parse Time: 0.291 s with 237 queries And on one of my product catogories i get Current Parse Time: 0.750 s with 407 queries i have Installed the tax contib and this has hellped but from looking at every one elses post and mine is starting to look a bit high rather than clogging up the thread with results you can get it from here Results from queries Once again any help would be greatly appreciated Marc Turner :thumbsup: :thumbsup: :thumbsup:
Guest Posted March 22, 2005 Posted March 22, 2005 Just discovered this thread and was blown away! There are MANY osC features that I don't use and probably never will -- banners, reviews, specials, new products, taxes. My product listing pages must make hundreds of queries that are never used... I'm wondering, what's a good procedure to remove the stuff I don't need? Are there any complete instructions (ie, how to get rid of all references to "specials" on all pages) posted anywhere? If not... can I safely just GREP and delete stuff? :-"
uurbana Posted March 23, 2005 Posted March 23, 2005 Hello, As a lot of people, I am interest by this type of optimization. I tried on a standard base of oscommerce the various typical templates (BTS, STS, normal JTS and osc) . I have used the same database for all shop and a standard installation with no optimization. I have tested this in local server for more velocity. Here is the results: osc + BTS: Current Parse Time: 0.282 s with 93 queries osc + JTS: Current Parse Time: 0.284 s with 93 queries osc + STS: Current Parse Time: 0.203 s with 114 queries stand. osc: Current Parse Time: 0.271 s with 92 queries I very surprise that STS require lot of queries. 21 queries furthermore... And it is the worse in product_info.php osc + STS: Current Parse Time: 0.294 s with 250 queries osc + BTS: Current Parse Time: 0.257 s with 98 queries stand. osc: Current Parse Time: 0.186 s with 104 queries How does it make it? Is there means to remedy it? Thanks.
waynehartwig Posted April 1, 2005 Posted April 1, 2005 Since we are working on optimizing the store, I have a good one for you... I've looked through the thread, and I can't see anything that talks about optimizing the back end! I have around 100,000 products in my store (www.alljeep.com). I've been importing them through Easy Populate. I've been putting them into one category, so that later I can go through each item and adjust descriptions, etc. It's just easier to keep them seperate from the items that already have detailed descriptions, pictures, etc. Anyway, when I open the category in the admin area, it takes FOREVER! We're talking 2 minutes or so to do anything. Edit, move, etc. I'm not any good with PHP or coding.. But it seems like if when you open the category it puts the items into say 25 items per page, it would load a LOT faster. Currently it displays all items in the entire category. Any ideas how to fix this? Thank you! Wayne
homewetbar Posted April 3, 2005 Posted April 3, 2005 I see optimizing your categories box mentioned over and over throughout the thread but no one has said specifically how. Can someone post their optimized categories INFOBOX or code on how to optimize it???? Most Valuable OsCommerce Contributions: Also Purchased (AP) Preselection (cuts this resource hogging query down to nothing) -- Contribution 3294 FedEx Automated Labels -- Contribution 2244 RMA Returns system -- Contribution 1136 Sort Products By Dropdown -- Contribution 4312 Ultimate SEO URLs -- Contribution 2823 Credit Class & Gift Voucher -- Contribution 282 Cross-Sell -- Contribution 5347
Guest Posted April 3, 2005 Posted April 3, 2005 The easiest way to optimize that box is to turn OFF the category counts and USE THE CACHE. Bobby
bugauto Posted April 4, 2005 Posted April 4, 2005 Implement MS3 Tax Class for MS2 <{POST_SNAPBACK}> I wanted to make sure I got the code right in my includes/functions/general.php file. This is my new code below from how yous said to edit it. // TABLES: tax_rates, zones_to_geo_zones function tep_get_tax_rate($class_id, $country_id = -1, $zone_id = -1) { global $customer_zone_id, $customer_country_id, $osC_Tax; return $osC_Tax->getTaxRate($class_id, $country_id, $zone_id); } { global $customer_zone_id, $customer_country_id; if ( ($country_id == -1) && ($zone_id == -1) ) { if (!tep_session_is_registered('customer_id')) { $country_id = STORE_COUNTRY; $zone_id = STORE_ZONE; } else { $country_id = $customer_country_id; $zone_id = $customer_zone_id; } } // TABLES: tax_rates; function tep_get_tax_description($class_id, $country_id, $zone_id) { global $osC_Tax; return $osC_Tax->getTaxRateDescription($class_id, $country_id, $zone_id); } { $tax_query = tep_db_query("select tax_description from " . TABLE_TAX_RATES . " tr left join " . TABLE_ZONES_TO_GEO_ZONES . " za on (tr.tax_zone_id = za.geo_zone_id) left join " . TABLE_GEO_ZONES . " tz on (tz.geo_zone_id = tr.tax_zone_id) where (za.zone_country_id is null or za.zone_country_id = '0' or za.zone_country_id = '" . (int)$country_id . "') and (za.zone_id is null or za.zone_id = '0' or za.zone_id = '" . (int)$zone_id . "') and tr.tax_class_id = '" . (int)$class_id . "' order by tr.tax_priority"); if (tep_db_num_rows($tax_query)) { $tax_description = ''; while ($tax = tep_db_fetch_array($tax_query)) { $tax_description .= $tax['tax_description'] . ' + '; } $tax_description = substr($tax_description, 0, -3); return $tax_description; } else { return TEXT_UNKNOWN_TAX_RATE; } } It is a bit confusing when you say REPLACE tep_get_tax_rate() it with this code: function tep_get_tax_rate($class_id, $country_id = -1, $zone_id = -1) { global $customer_zone_id, $customer_country_id, $osC_Tax; return $osC_Tax->getTaxRate($class_id, $country_id, $zone_id); } Do you mean to replace only what is inside the () or do you mean replace the whole script? I note no less querries on the product pages themselves. The only noted change I could see was on the catalog index page which lessened the querries by 10. I don't see where this has made any significant reduction in querries on my site. That's why I ask "Did I get the code correct?" Mike
bugauto Posted April 4, 2005 Posted April 4, 2005 When I reset the cache in the database and then reload a product page that was only getting 97 querries before and then jumped to Current Parse Time: 3.218 s with 485 queries. Then I reload again and it goes back to the normal Current Parse Time: 0.937 s with 97 queries. I'm using the osc advance cache class. Mike
nico_ Posted April 4, 2005 Posted April 4, 2005 hi there, i don't wanna be heretic but everything over 10 queries (in the catalog) and 0.2 seconds parsetime is pure evil. just imagine 30+ people on your page and one or more bots... to really provide a satisfying shopping experience to your customers and to get them "into the flow" instant responses of your site are a must imho ... just my 2 cents, greetings, nico
Guest Posted April 4, 2005 Posted April 4, 2005 I wanted to make sure I got the code right in my includes/functions/general.php file. This is my new code below from how yous said to edit it.... Do you mean to replace only what is inside the () or do you mean replace the whole script? I note no less querries on the product pages themselves. The only noted change I could see was on the catalog index page which lessened the querries by 10. I don't see where this has made any significant reduction in querries on my site. That's why I ask "Did I get the code correct?" Mike <{POST_SNAPBACK}> It's not that the contribution replaces or reduces the query count by 10...it eliminates 10 HUGELY resource intensive queries. The fact is that the tax query used to form the price of each product price displayed (whether tax is turned on or NOT) is VERY slow and server intensive. As for your code: post what your functions look like right now. hi there, i don't wanna be heretic but everything over 10 queries (in the catalog) and 0.2 seconds parsetime is pure evil. just imagine 30+ people on your page and one or more bots... to really provide a satisfying shopping experience to your customers and to get them "into the flow" instant responses of your site are a must imho ... just my 2 cents, greetings, nico <{POST_SNAPBACK}> ...I think that number should be a bit higher. Those tax queries are some of the hardest queries on the osCommerce application. He probably saved himself at least 1-1.5 seconds on each page load. Bobby
bugauto Posted April 4, 2005 Posted April 4, 2005 Hello, I have been working on optimizing the MS2 codebae for several months and am going to do a test site from scratch so everyone can follow along. This is my development server and I'm going to keep the admin area open for everyone. Don't mess this up with malicious stuff or I'll lock it down. On the bottom of every page there is a debug output with parse time, query count, and the actual queries used to construct the page. I'll be using this info to optimize the queries. Can anyone take a look and see any redundant queries? It's a loaded question since I've already been down this block and know where there is opportunity to streamline. Let's see if anyone wants to follow along as we get this down to 10 queries per page and sub .1 second render times (read: bad ass cart performance). The only requirement to the optimizations are a net increase in performance and NO LOSS OF FUNCTIONALITY. Vanilla Install Stats: Parse Time: .328 seconds Queries - 92 Disable Category Counts: Parse Time: .259 seconds Queries: 52 So, save 40 queries right off the bat for disabling category counts. More optimizations tomorrow...whe I start to dissect the code and get this puppy screaming. <{POST_SNAPBACK}> Can you give the steps to disable category counts? I think that is why my querries are so high in number. I have many categories. Mike
nico_ Posted April 4, 2005 Posted April 4, 2005 ...I think that number should be a bit higher. Those tax queries are some of the hardest queries on the osCommerce application. He probably saved himself at least 1-1.5 seconds on each page load. well, i dropped all those because all my prices will have the tax already included and by now i only plan to sell national. did you ever think of using mysql guery cache? it would need some changes in the database scheme because after every insert (eg click count in products) the cache is flushed - i took those queries out and parse time is down to 1/3... nico
bugauto Posted April 4, 2005 Posted April 4, 2005 It's not that the contribution replaces or reduces the query count by 10...it eliminates 10 HUGELY resource intensive queries. The fact is that the tax query used to form the price of each product price displayed (whether tax is turned on or NOT) is VERY slow and server intensive. As for your code: post what your functions look like right now. ...I think that number should be a bit higher. Those tax queries are some of the hardest queries on the osCommerce application. He probably saved himself at least 1-1.5 seconds on each page load. Bobby <{POST_SNAPBACK}> Yes the code I have right now which is the edited code from the tax class contribution is as follows: //// // Returns the tax rate for a zone / class // TABLES: tax_rates, zones_to_geo_zones function tep_get_tax_rate($class_id, $country_id = -1, $zone_id = -1) { global $customer_zone_id, $customer_country_id, $osC_Tax; return $osC_Tax->getTaxRate($class_id, $country_id, $zone_id); } { global $customer_zone_id, $customer_country_id; if ( ($country_id == -1) && ($zone_id == -1) ) { if (!tep_session_is_registered('customer_id')) { $country_id = STORE_COUNTRY; $zone_id = STORE_ZONE; } else { $country_id = $customer_country_id; $zone_id = $customer_zone_id; } } $tax_query = tep_db_query("select sum(tax_rate) as tax_rate from " . TABLE_TAX_RATES . " tr left join " . TABLE_ZONES_TO_GEO_ZONES . " za on (tr.tax_zone_id = za.geo_zone_id) left join " . TABLE_GEO_ZONES . " tz on (tz.geo_zone_id = tr.tax_zone_id) where (za.zone_country_id is null or za.zone_country_id = '0' or za.zone_country_id = '" . (int)$country_id . "') and (za.zone_id is null or za.zone_id = '0' or za.zone_id = '" . (int)$zone_id . "') and tr.tax_class_id = '" . (int)$class_id . "' group by tr.tax_priority"); if (tep_db_num_rows($tax_query)) { $tax_multiplier = 1.0; while ($tax = tep_db_fetch_array($tax_query)) { $tax_multiplier *= 1.0 + ($tax['tax_rate'] / 100); } return ($tax_multiplier - 1.0) * 100; } else { return 0; } } //// // Return the tax description for a zone / class // TABLES: tax_rates; function tep_get_tax_description($class_id, $country_id, $zone_id) { global $osC_Tax; return $osC_Tax->getTaxRateDescription($class_id, $country_id, $zone_id); } { $tax_query = tep_db_query("select tax_description from " . TABLE_TAX_RATES . " tr left join " . TABLE_ZONES_TO_GEO_ZONES . " za on (tr.tax_zone_id = za.geo_zone_id) left join " . TABLE_GEO_ZONES . " tz on (tz.geo_zone_id = tr.tax_zone_id) where (za.zone_country_id is null or za.zone_country_id = '0' or za.zone_country_id = '" . (int)$country_id . "') and (za.zone_id is null or za.zone_id = '0' or za.zone_id = '" . (int)$zone_id . "') and tr.tax_class_id = '" . (int)$class_id . "' order by tr.tax_priority"); if (tep_db_num_rows($tax_query)) { $tax_description = ''; while ($tax = tep_db_fetch_array($tax_query)) { $tax_description .= $tax['tax_description'] . ' + '; } $tax_description = substr($tax_description, 0, -3); return $tax_description; } else { return TEXT_UNKNOWN_TAX_RATE; } } //// Can you please post your code here from exactly where I started to exactly where I ended it. I want to compare to see if it's the same. I'm not arguing about 10 queries, all I am saying is it reduced it by that much on the index page but it did not reduce any at all on the product pages. Awaiting the steps to reduct category queries. Mike
Guest Posted April 4, 2005 Posted April 4, 2005 Can you give the steps to disable category counts? I think that is why my querries are so high in number. I have many categories. Mike <{POST_SNAPBACK}> It's not code...it is an admin control panel setting. well, i dropped all those because all my prices will have the tax already included and by now i only plan to sell national. did you ever think of using mysql guery cache? it would need some changes in the database scheme because after every insert (eg click count in products) the cache is flushed - i took those queries out and parse time is down to 1/3... nico <{POST_SNAPBACK}> ...ssshhhh...we can't give away all of our secrets :) Bobby
New 
Recommended Posts
Archived
This topic is now archived and is closed to further replies.