Hi All!

This is not specific to DNN but rather a request for general advice on a SQL related problem that I'm experiencing with SQL. In detail - due to the design of our software - I have a function which will evaluate a number of pricing rules based on the customer, product, product type etc. and then return the price. As the tables from which I am reading the pricing rules tend to be quite big this function is not too fast. While this is OK if I just want to return a number of products including their prices, I can't really use this for ordering the products by price. So I am trying to find a way to do the sorting without it taking too long.

At the moment the only idea that I had for this would be to have another table like UserProductPrices and populate this in the background with a scheduler to then read from this table rather than calculating the values on the fly. The disadvantage of this is that I obviously do not know whether the price is still correct as pricing rules / customer data could have changed in the meantime.

The table sizes are (roughly):

• about 50,000 products
• maybe 4 tables with pricing rules each with up to 4,000,000 records (these will be JOINed together to extract a number of rules that only apply to the current login)

Any idea, suggestions are most appreciated.

Cheers
Nils

---

Acumen Online - Integrated Websites and more ...

btw, if you evaluate the prices for a single user and selection of products inside SQL Server, you may populate a temp table and sort it by price.

---

Cheers from Germany,
Sebastian Leupold

   

Speed up your DNN Websites with TurboDNN

Nils,
it depends on the structure of the rules, whether you are able to apply on demand for a single user. building a temp table with 50 k records and sort by any criteria should not be the biggest problem. However, if applying the rules takes very long and each needs to be evaluated one by one, it might be an idea to create parts of the results, whenever a rule or product price is updated, and generate the individual price from this table, once the user requests the list of products.

---

Cheers from Germany,
Sebastian Leupold

   

Speed up your DNN Websites with TurboDNN