Developer forum

Forum » Development » Slow product view in shop

Slow product view in shop

E-mail notifications
Henrik Madsen
Reply
Posted on 01/03/2014 12:07:37

Hi

I have a problem with extrem long loadtime on productdetails view and i know why (Se debug list below). When i click on a product the page generates a ton of requst to the database, but why?

I get thousand of the following DB select.

10:50:32.365 Start Output 16 172
10:50:32.365 Before Content 0 172
10:50:32.428 Executing dataset (Dynamic.mdb: SELECT Paragraph.* FROM Paragraph WHERE Paragraph.ParagraphPageID=3 AND (('2014-3-1 10:50:32' BETWEEN Paragraph.ParagraphValidFrom AND Paragraph.ParagraphValidTo) OR Paragraph.ParagraphValidTo IS NULL) AND Paragraph.ParagraphShowParagraph = 1 AND (ParagraphApprovalState > -1 OR ParagraphApprovalState IS NULL) ORDER BY ParagraphSort ) - took: 31,2518 ms. 63 234
10:50:32.428 Start module (PID = 9): eCom_Catalog 0 234
10:50:32.428 <-- eCom: start GetContent --> 0 234
10:50:32.444 Executing datareader (Ecom.mdb: SELECT * FROM EcomProducts WHERE ProductID = 'PROD2789' AND (ProductVariantID IS NULL OR ProductVariantID = '')AND EcomProducts.ProductLanguageID = 'LANG1' ) - took: 15,6259 ms. 16 250
10:50:32.522 Executing datareader (Ecom.mdb: SELECT EcomPrices.* FROM EcomPrices WHERE PriceProductID='PROD2789' ORDER BY PriceID) - took: 15,6259 ms. 78 328
10:50:32.537 Executing datareader (Ecom.mdb: SELECT * FROM EcomVatGroups WHERE VatGroupID = 'VATGRP1') - took: 15,6259 ms. 16 344
10:50:32.537 Executing datareader (Ecom.mdb: SELECT * FROM EcomProducts WHERE ProductID = 'PROD145' AND ProductLanguageID='LANG1' ) - took: 0 ms. 0 344
10:50:32.553 Executing datareader (Ecom.mdb: SELECT EcomPrices.* FROM EcomPrices WHERE PriceProductID IN('PROD145') ORDER BY EcomPrices.PriceProductID) - took: 15,6259 ms. 16 359
10:50:32.569 Executing datareader (Ecom.mdb: SELECT * FROM EcomProducts WHERE ProductID = 'PROD147' AND ProductLanguageID='LANG1' ) - took: 15,6259 ms. 16 375
10:50:32.569 Executing datareader (Ecom.mdb: SELECT EcomPrices.* FROM EcomPrices WHERE PriceProductID IN('PROD147') ORDER BY EcomPrices.PriceProductID) - took: 0 ms. 0 375
10:50:32.584 Executing datareader (Ecom.mdb: SELECT * FROM EcomProducts WHERE ProductID = 'PROD149' AND ProductLanguageID='LANG1' ) - took: 15,6259 ms. 16 391
10:50:32.600 Executing datareader (Ecom.mdb: SELECT EcomPrices.* FROM EcomPrices WHERE PriceProductID IN('PROD149') ORDER BY EcomPrices.PriceProductID) - took: 15,6259 ms. 16 406
10:50:32.615 Executing datareader (Ecom.mdb: SELECT * FROM EcomProducts WHERE ProductID = 'PROD150' AND ProductLanguageID='LANG1' ) - took: 15,6259 ms. 16 422
10:50:32.615 Executing datareader (Ecom.mdb: SELECT EcomPrices.* FROM EcomPrices WHERE PriceProductID IN('PROD150') ORDER BY EcomPrices.PriceProductID) - took: 0 ms. 0 422
10:50:32.631 Executing datareader (Ecom.mdb: SELECT * FROM EcomProducts WHERE ProductID = 'PROD151' AND ProductLanguageID='LANG1' ) - took: 15,6259 ms. 16 438
10:50:32.647 Executing datareader (Ecom.mdb: SELECT EcomPrices.* FROM EcomPrices WHERE PriceProductID IN('PROD151') ORDER BY EcomPrices.PriceProductID) - took: 15,6259 ms. 16 453
10:50:32.647 Executing datareader (Ecom.mdb: SELECT * FROM EcomProducts WHERE ProductID = 'PROD152' AND ProductLanguageID='LANG1' ) - took: 0 ms. 0 453
10:50:32.662 Executing datareader (Ecom.mdb: SELECT EcomPrices.* FROM EcomPrices WHERE PriceProductID IN('PROD152') ORDER BY EcomPrices.PriceProductID) - took: 0 ms. 16 469
10:50:32.694 Executing datareader (Ecom.mdb: SELECT * FROM EcomProducts WHERE ProductID = 'PROD153' AND ProductLanguageID='LANG1' ) - took: 31,2518 ms. 31 50

Replies

 
Jeppe Eriksson Agger Dynamicweb Employee
Jeppe Eriksson Agger
Posted on 03/03/2014 10:34:53
Reply

Hi Henrik,

 

It's very difficult to give a meaningful answer without more information. You don't mention which version of Dynamicweb you're using, if you're using filters or even if you use Optimized Product Retrieval.

 

If you can provide a url to the site in question (including which page is acting up), I'll take a look and see what I can find out.

 

- Jeppe

 
Henrik Madsen
Posted on 03/03/2014 11:29:00
Reply

Hi Jeppe,

I have testet this in version: 8.4.0.6 and 8.3.1.17
Isue is the same on this test site: http://cameleonss.net.dynamicweb-cms.com/en-US/ProductList/Bikes/Nirve-Paul-Frank-Aku-Single-Speed.aspx?debug=true 

We do not user filters. 

/Henrik

 
Jeppe Eriksson Agger Dynamicweb Employee
Jeppe Eriksson Agger
Posted on 04/03/2014 10:28:37
Reply

Hi Henrik,

I looked at the site, and the issue is related with the eCom:Related loops. Related products in these loops are lazy loaded rather than bulk loaded.

You should be able to solve this by removing the eCom:Related loops from the template. I tried this on your solution and was able to reduce the number of database connection by more than 100 and reduce the load time of the site from ~1.4s to ~0.3s.

- Jeppe

 
Henrik Madsen
Posted on 05/03/2014 08:59:28
Reply

Hi Jeppe

I’m sorry, but You still have all the DB request in your debug trace (see below). Am I wrong?
Our case is we have a customer with over 20.000 different prices and discounts... 20.000 * 1,4 s = 466 minutes or 20.000 * 0,3 s = 100 minutes to view a product... this is a no go.
In my first test, I had about 2.000 discount that’s 46 minutes to view a product. On top off that, when you add more data/discount to the DB it do not get any quicker.

8:54:51 PM.164 Executing datareader (Ecom.mdb: SELECT * FROM EcomProducts WHERE ProductID = 'PROD131' AND ProductVariantID = 'VO8.VO4.VO3' AND EcomProducts.ProductLanguageID = 'LANG1' ) - took: 0 ms. 0 1984
8:54:51 PM.164 Executing datareader (Ecom.mdb: SELECT * FROM EcomProducts WHERE ProductID = 'PROD131' AND ProductLanguageID = 'LANG1' ) - took: 0 ms. 0 1984
8:54:51 PM.164 Starting template.output ( (VariantCombinations)) 0 1984
8:54:51 PM.164 Ending template.output ( (VariantCombinations)) - took: 0 ms. 0 1984
8:54:51 PM.164 Executing datareader (Ecom.mdb: SELECT * FROM EcomProducts WHERE ProductID = 'PROD131' AND ProductVariantID = 'VO8.VO5.VO1' AND EcomProducts.ProductLanguageID = 'LANG1' ) - took: 0 ms. 0 1984
8:54:51 PM.164 Executing datareader (Ecom.mdb: SELECT * FROM EcomProducts WHERE ProductID = 'PROD131' AND ProductLanguageID = 'LANG1' ) - took: 0 ms. 0 1984
8:54:51 PM.164 Starting template.output ( (VariantCombinations)) 0 1984
8:54:51 PM.164 Ending template.output ( (VariantCombinations)) - took: 0 ms. 0 1984
8:54:51 PM.164 Executing datareader (Ecom.mdb: SELECT * FROM EcomProducts WHERE ProductID = 'PROD131' AND ProductVariantID = 'VO8.VO5.VO2' AND EcomProducts.ProductLanguageID = 'LANG1' ) - took: 0 ms. 0 1984
8:54:51 PM.164 Executing datareader (Ecom.mdb: SELECT * FROM EcomProducts WHERE ProductID = 'PROD131' AND ProductLanguageID = 'LANG1' ) - took: 0 ms. 0 1984
8:54:51 PM.164 Starting template.output ( (VariantCombinations)) 0 1984
8:54:51 PM.164 Ending template.output ( (VariantCombinations)) - took: 0 ms. 0 1984
8:54:51 PM.164 Executing datareader (Ecom.mdb: SELECT * FROM EcomProducts WHERE ProductID = 'PROD131' AND ProductVariantID = 'VO8.VO5.VO3' AND EcomProducts.ProductLanguageID = 'LANG1' ) - took: 0 ms. 0 1984
8:54:51 PM.164 Executing datareader (Ecom.mdb: SELECT * FROM EcomProducts WHERE ProductID = 'PROD131' AND ProductLanguageID = 'LANG1' ) - took: 0 ms. 0 1984
8:54:51 PM.164 Starting template.output ( (VariantCombinations)) 0 1984
8:54:51 PM.164 Ending template.output ( (VariantCombinations)) - took: 0 ms. 0 1984
8:54:51 PM.164 Executing datareader (Ecom.mdb: SELECT * FROM EcomProducts WHERE ProductID = 'PROD131' AND ProductVariantID = 'VO8.VO6.VO1' AND EcomProducts.ProductLanguageID = 'LANG1' ) - took: 0 ms. 0 1984
8:54:51 PM.179 Executing datareader (Ecom.mdb: SELECT * FROM EcomProducts WHERE ProductID = 'PROD131' AND ProductLanguageID = 'LANG1' ) - took: 15.6232 ms. 16 2000
8:54:51 PM.179 Starting template.output ( (VariantCombinations)) 0 2000
8:54:51 PM.179 Ending template.output ( (VariantCombinations)) - took: 0 ms. 0 2000
8:54:51 PM.179 Executing datareader (Ecom.mdb: SELECT * FROM EcomProducts WHERE ProductID = 'PROD131' AND ProductVariantID = 'VO8.VO6.VO2' AND EcomProducts.ProductLanguageID = 'LANG1' ) - took: 0 ms. 0 2000
8:54:51 PM.179 Executing datareader (Ecom.mdb: SELECT * FROM EcomProducts WHERE ProductID = 'PROD131' AND ProductLanguageID = 'LANG1' ) - took: 0 ms. 0 2000

 

 
Jeppe Eriksson Agger Dynamicweb Employee
Jeppe Eriksson Agger
Posted on 05/03/2014 09:14:37
Reply

Yes, they are still there. I didn't remove change your site permanently without having cleared it with you first, I only tested it. I added those loops back afterwards. I can remove the loops for you, but like I said, I wouldn't just do that without permission.

 
Henrik Madsen
Posted on 05/03/2014 09:44:15
Reply

Hi again

I just looked in our running solution and we don’t use eCom:Related loops. When I activate just to discount I get 4 extra DB request:

http://kens.appsin.net/da-DK/Webshop/Beholder-2.aspx?debug=true

09:27:28.698

Executing datareader (Ecom.mdb: SELECT * FROM EcomProducts WHERE ProductID = 'PROD334' AND ProductLanguageID='LANG1' ) - took: 0 ms.

0

172

09:27:28.698

Executing datareader (Ecom.mdb: SELECT EcomPrices.* FROM EcomPrices WHERE PriceProductID IN('PROD334') ORDER BY EcomPrices.PriceProductID) - took: 0 ms.

0

172

09:27:28.714

Executing datareader (Ecom.mdb: SELECT * FROM EcomProducts WHERE ProductID = 'PROD151' AND ProductLanguageID='LANG1' ) - took: 15,6247 ms.

16

187

09:27:28.714

Executing datareader (Ecom.mdb: SELECT EcomPrices.* FROM EcomPrices WHERE PriceProductID IN('PROD151') ORDER BY EcomPrices.PriceProductID) - took: 0 ms.

0

187

I have also tried to delete all code in all templates including main.html I masterpages, and I get same result 2 DB request for every active discount I create.
I’am sorry but I think this is deep in Dynamicweb ecommerce engine.

For others whit the same problem:
Support says that I should not create more than 10 to 25 discounts. L

Regards
Henrik

 
Henrik Madsen
Posted on 05/03/2014 09:46:29
Reply

Hi Jeppe

Please do so! And then tell me where you removed/changed them. :-)

 
Jeppe Eriksson Agger Dynamicweb Employee
Jeppe Eriksson Agger
Posted on 05/03/2014 09:51:22
Reply

I have now removed these loops from the Product template.

Load time is reduced to around ~0.3s.

 
Jeppe Eriksson Agger Dynamicweb Employee
Jeppe Eriksson Agger
Posted on 05/03/2014 10:09:13
Reply

You should be aware that I have only looked at the original site, you sent me. This is the site I investigated, so the performance issues I'm talking about here are related to that site only. Each site present they own challenges, so the new site you linked might have a new set of issues. Like I said, I haven't checked that yet.

You might be right that this is something deep in Ecommerce, but if I understand the specific use-case, then you need around 2000 discounts which sounds more like a price structure rather than a discount setup. This is -- as I understand it -- a discussion that is handled elsewhere.

 
Henrik Madsen
Posted on 05/03/2014 10:24:52
Reply

As I wrote earlier, I have tested this in version: 8.4.0.6 and 8.3.1.17
Are we still talking about this site?
http://cameleonss.net.dynamicweb-cms.com/en-US/ProductList/Bikes/Nirve-Paul-Frank-Aku-Single-Speed.aspx?debug=true

The DB activity is still the same you still have 2 calls for every discount?

10:22:15 AM.444

Executing datareader (Ecom.mdb: SELECT * FROM EcomProducts WHERE ProductID = 'PROD134' AND ProductLanguageID='LANG1' ) - took: 0 ms.

0

125

10:22:15 AM.444

Executing datareader (Ecom.mdb: SELECT EcomPrices.* FROM EcomPrices WHERE PriceProductID IN('PROD134') ORDER BY EcomPrices.PriceProductID) - took: 0 ms.

0

125

 

You must be logged in to post in the forum