SQL queries

[Guest]

Hello all.

As everyone says on their first post.... Unbelievable application that has been created here! Can not wait to help out with some mods of my own.

 

Onto the problem I am having. When I go to view the category listing... The page is taking way too long to load. Also, keep in mind I have a huge inventory we are reaching nearly 17,000 products.

 

For a category that has 27 products the download time is about 25 second. Another category with 83 products was surprisingly 83 seconds. Yet another category with 262 products took 148 seconds. Now one of the big ones with 1,728 products. At 1,038 seconds all of the sql commands had finished. At around 1,340 seconds I stopped the web browser, as nothing else was coming in and I was sitting in limbo.

 

In posts from searching on the forum one had said to make sure to turn off "counting" in application_top.php, and also to use the cache option from the admin section. Both of these have been done.

 

I was thinking I might have a looping problem, but after looking over the code for the product_listing.php page everything seems to be fine.

 

Anyone have any idea that may be able to speed things up (other than to drop a couple thousand items for sell)?

 

One thing I have been thinking about is taking out one of the SQL commands that creates the product listed. This is the second SQL command in the string of three. This command appears like this during a SHOW PROCESSLIST in MySQL:

 

SELECT p.products_image, pd.products_name, p.products_model, p.products_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, 

IF (

s.status, s.specials_new_products_price, NULL 

) AS specials_new_products_price, 

IF (

s.status, s.specials_new_products_price, p.products_price

) AS final_price

FROM products_description pd, products p

LEFT JOIN manufacturers m ON p.manufacturers_id = m.manufacturers_id, products_to_categories p2c

LEFT JOIN specials s ON p.products_id = s.products_id

WHERE p.products_status = '1' AND p.products_id = p2c.products_id AND pd.products_id = p2c.products_id AND pd.language_id = '1' AND p2c.categories_id = '49'

ORDER BY pd.products_name

 

The reason I am thinking about removing this(if I can locate where this is in the application :D ) is the very next SQL is this:

 

SELECT p.products_image, pd.products_name, p.products_model, p.products_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, 

IF (

s.status, s.specials_new_products_price, NULL 

) AS specials_new_products_price, 

IF (

s.status, s.specials_new_products_price, p.products_price

) AS final_price

FROM products_description pd, products p

LEFT JOIN manufacturers m ON p.manufacturers_id = m.manufacturers_id, products_to_categories p2c

LEFT JOIN specials s ON p.products_id = s.products_id

WHERE p.products_status = '1' AND p.products_id = p2c.products_id AND pd.products_id = p2c.products_id AND pd.language_id = '1' AND p2c.categories_id = '49'

ORDER BY pd.products_name

LIMIT 0, 20

 

It seems to me that these are the same call, yet separates the order by the limit for the "Previous/Next" links in the third. Couldn't we just remove the extra call before this?? Or am I missing something here. :?:

 

Environment info (don't think this is needed but..just in case):

 

MySQL: 3.23.39

PHP: 4.2.3

Apache: 1.3.26

osCommerce: snapshot - 20030111

[Guest]

Let me see if I can help you out ;-)

Can you be a little more specific about what pages take very long to load? Pasting a URL would help.

 

You had a look at the categorybox thread, the reason this can be sped up is because the database repeatedly gets hit inside a control loop, this sends performance down the drain.

I'm pretty there are other places in the code where this happens, maybe you are bitten by one of them.

I was planning on having a look for these anyway maybe I can try and tackle yours first.

 

An other thing that might cause bad performance, is when the dataset MySQL is working gets swapped in and out constantly, check if your server doesn't start swapping when you hit it with requests.

 

HTH,

Rob

[Guest]

Rob,

 

I fixed this up a while back, the problem was the way the sale price was calculated. I have a very odd way that the sale price is calculated, and created my own functions to handle all my differant possibilites, as the default OsCommerce functions did not handle it at all.

 

One thing I am starting to run into however is attributes starting to slow down the system, I am working on creating custom scripts to handle this as well.

 

Can not supply a link as of yet since the site I am testing resides on my test server and have not uploaded anything to the live server yet. Waiting to have everything 100% before I do this.

 

Thanks very much,

Nicky