Developer forum

Forum » CMS - Standard features » Index GetProductsFromIndexQuery SQLException

Index GetProductsFromIndexQuery SQLException

António Ramos
António Ramos
Reply

Hi,

When we filter by three parameters and PageSize=99999 (show all products) we get an SQL Exception ("The server supports a maximum of 2100 parameters"). Anyone had experienced any similar?

Our SQL server is very affected when we set PageSize=99999 and try to retrieve around 400 products from index. 

DW Version: 8.9.1.13

BR,

António Ramos

BL_SQLError.PNG

Replies

 
António Ramos
António Ramos
Reply

Other question:

Is there any way to return all products from index (without pagination)?

 
Nicolai Pedersen
Reply

You can use the query publisher that return data directly from index - that will bypass stuff like price calculations etc.

That said - do not do this. It will only give problems. You do not want 400 products in one go - you should really consider designing this differently.

BR Nicolai

 
António Ramos
António Ramos
Reply

Hi Nicolai,

Just to make sure i understand correctly, you are saying that we shouldn't allow to show all products in product list, is this correct?

Other thing:

We have Live Integration to get prices, stock and other data from NAV. Displaying product list via query publisher will allow to keep doing this? 

 

BR,

António Ramos

 
Nicolai Pedersen
Reply

Using query publisher will only give you raw index data and not the ecommerce logic.

Looking closer at your stack, I can tell it has to do with stock calculations - and not from rendering 400 products.

 
António Ramos
António Ramos
Reply

Good morning Nicolai,

Thanks for your answer. Which options do we have to list all products?

BR,

António Ramos

 
Nicolai Pedersen
Reply
This post has been marked as an answer

Loading them in batch using ajax as the page scrolls.

Again - Why would you load 400 products?

Votes for this answer: 1
 
António Ramos
António Ramos
Reply

It's client requirement. Ajax looks a good option. Let's see what client thinks.

Thanks for your help,

António Ramos

 
Nicolai Pedersen
Reply

I had a client that wanted to jump off the roof. I told him not to because he would probably die. He then did not jump, is alive and happy, not dead...

 

You must be logged in to post in the forum