Developer forum

Forum » CMS - Standard features » SQL Query partial index OR some other way

SQL Query partial index OR some other way

E-mail notifications
Nuno Aguiar Dynamicweb Employee
Nuno Aguiar
Reply
Posted on 13/01/2021 21:07:24

Hi,

 

I have a challenge. We're working with a Query Publisher using the SqlBuilder to render a list of ordelrines associated with Digital products and it rebuilds on a 5min schedule. This basically builds a list of "My Downloads" that a customer purchased. So far so good.

 

Some customers experience an up to 5min delay until they can see their downloadable products, while others see them immediately (depending when they place the order relative to the schedule task being run). The problem is that this is not enough for our customer's industry standard so they are feeling penalized by it.

 

My solutions are:

  • Rebuild the index on order complete
    But I am concerned about volume and/or multiple orders being placed within a small time frame
     
  • Refactor to Data Lists and use SQL indexes
    But that's not as flexible and we have to implemente searching and faceting the "by hand" - no fun
    Performance wise is not expected to be a big issue because it's not a page users hang around a lot

 

Can anyone share a thought?

 

Best Regards,

Nuno Aguiar


Replies

 
Nuno Aguiar Dynamicweb Employee
Nuno Aguiar
Posted on 05/02/2021 13:10:35
Reply

bump

 
Adrian Ursu Dynamicweb Employee
Adrian Ursu
Posted on 06/03/2023 16:30:21
Reply

Hi Nuno,

Have you found a solution to this issue?

Thank you,

Adrian

 
Nuno Aguiar Dynamicweb Employee
Nuno Aguiar
Posted on 06/03/2023 17:39:02
Reply

Hi Adrian,

 

We ended up ising the DataList approach, considering also (that for our scenario) it's not a page that would be highly visited, so having SQL queries (not even cached, because DataLists doesn't do that) would not be an issue.

 

We actually evolved into using Data Lists for "Order History", when the customers want/need more search capabilities than the Customer Center app is capable of. We also removed the ability to search within orderlines when we need to boost performance even more.

 

Hope this helps, but let me know if you need anything else.

 

Best Regards,

Nuno Aguiar

 
Adrian Ursu Dynamicweb Employee
Adrian Ursu
Posted on 06/03/2023 17:41:39
Reply

Hi Nuno,

Thank you. I was afraid of this answer :)

In our case, we need some filtering that would be hard to handle with DataLists.

We'll have to review our options/

Thank you,
Adrian

 
Nuno Aguiar Dynamicweb Employee
Nuno Aguiar
Posted on 06/03/2023 17:44:58
Reply

Well... Some SQL indexes and/or stored procedures should help you.

 

The problem we had with the index was really the inability to do partial indexes, and/or that they took SOOOO long to run that rendered them useless for our cases.

 

In your case, you may also have the option to have rebuilds every 10min or 60min, and assuming a repository index would take a minute or so, could be a good business case for you.

 

Nuno

 

You must be logged in to post in the forum