Developer forum

Forum » Development » Sql statement runs too many times

Sql statement runs too many times

E-mail notifications
Jose Caudevilla
Reply
Posted on 22/06/2021 10:52:55

Hi:

I have a webpage that goes slow, debuging the pages i found that a sql statement runs 190 times in every page , even if does not contains any product.

This is the SQL statement:

DECLARE @p0 INT = 10, @p1 NVARCHAR(8) = N'GROUP501', @p2 NVARCHAR(1) = N'1' SELECT TOP (@p0) EcomProducts.*, EcomGroupProductRelation.GroupProductRelationSorting AS GroupProductRelationSorting FROM EcomProducts INNER JOIN EcomGroupProductRelation ON EcomGroupProductRelation.GroupProductRelationProductID = EcomProducts.ProductID WHERE EcomProducts.ProductLanguageID = 'LANG1' AND EcomGroupProductRelation.GroupProductRelationGroupID = @p1 AND (EcomProducts.ProductVariantID IS NULL OR EcomProducts.ProductVariantID = '') AND (ISNULL(EcomProducts.ProductHidden, 0) <> @p2) ORDER BY EcomGroupProductRelation.GroupProductRelationSorting

All this calls are made inside the Page.parsed.cshtml page. 

 

Following the Stacktrace of these calls i found that the call is made in the following funcion

 

Im using dw 9.9.6 and rapido and Rapido 3.1

 

Any clue or reason why this statement is executed so may times on any page?

 

Regards,

Jose


Replies

 
Anders Ebdrup
Anders Ebdrup
Posted on 22/06/2021 10:59:36
Reply

We have seen the exact same issue!

 
Nicolai Pedersen
Posted on 22/06/2021 13:00:55
Reply

It seems like a paragraph that gets included. Do you have global elements in use?

Can we see the paragraphs on a page that has this issue?

 
Anders Ebdrup
Anders Ebdrup
Posted on 23/06/2021 08:28:20
Reply

Dear Nicolai,

 

You can try to take a look at this page: https://anværktøj.dk/produkter/anvaerktoej/arbejdsmiljoe/luftrensere/luftrensere, where the sorting query is executed 700 times.

 

Best regards, Anders

 
Nicolai Pedersen
Posted on 23/06/2021 11:27:57
Reply
This post has been marked as an answer

Yikes, that is nasty.

Looking at the call stack of that, it seems like an include with an include with an include that circles around...

And the calls comes from the navigation where a setting has been activated to include all products in navigation:

If you disable that checkbox, those db calls will go away.

Votes for this answer: 1
 
Jose Caudevilla
Posted on 23/06/2021 11:58:33
Reply

Hi Nicolai:

 

Thanks for your reply. 

Disabling that checkbox does not make these sql statements.

Has this checkbox any implications with the products that are show on the web? I mean breadcrumbs, product lists, sitemap, etc...

 

Regards,

Jose

 
Nicolai Pedersen
Posted on 23/06/2021 14:50:20
Reply

Hi Jose

It does - that is a guarentee. Give me the URL and I will have a look. You probably have this checkbox set on multiple pages and websites.

It will probably not have any implications - I can see from the SQL that it will only render the first 10 products in each group anyways:

DECLARE @p0 INT = 10

BR Nicolai

 
Jose Caudevilla
Posted on 23/06/2021 16:18:49
Reply

Hi Nicolai:

 

I send you two urls, both of them do these 190 sql statements.

For example this url does not use any product paragraph , it contains only a banner and the contact form

https://qubbos.es/contacto?debug=true

 

This one is a product list page:

https://qubbos.es/frigorificos-congeladores-listado ?debug=true

It contains a product catalog, but it show products in batches of 12 products...im not sure where those products come from

 

Thanks again.

Regards,

Jose

 

 
Nicolai Pedersen
Posted on 24/06/2021 09:57:38
Reply
This post has been marked as an answer

That checkbox is enabled - so disable it:

Votes for this answer: 1

 

You must be logged in to post in the forum