Welcome to the DNN Community Forums, your preferred source of online community support for all things related to DNN.
In order to participate you must be a registered DNNizen

HomeHomeDevelopment and...Development and...SQL and SQL Ser...SQL and SQL Ser...SQL order by calculated columnSQL order by calculated column
Previous
 
Next
New Post
1/12/2016 1:52 PM
 

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

 
New Post
1/12/2016 2:41 PM
 
With that number of rules, it is difficult to evaluate all for each product. It might help to insert calculated columns and indexed views, but as you already found out, the best would be a pre-calculated table. To avoid it being out of date, you may mark all (possibly) affected entries as "outdated", whenever a rule is modified and have an option to regenerate the precalculation for a single user, if some of the records are marked.

Cheers from Germany,
Sebastian Leupold (Microsoft MVP)

dnnWerk - The DotNetNuke Experts   German Spoken DotNetNuke User Group

Speed up your DNN Websites with TurboDNN
 
New Post
1/12/2016 3:00 PM
 
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 (Microsoft MVP)

dnnWerk - The DotNetNuke Experts   German Spoken DotNetNuke User Group

Speed up your DNN Websites with TurboDNN
 
New Post
1/13/2016 2:21 PM
 

Firstly thanks for your answers Sebastian.

The problem is that this would only make sense (eg. wouldn't take too long) if I had a small amount of products but I would like this to work even if they didn't filter the products. This means that I want to be able to sort around 50k products by their "user price".

I feel like this is not achievable with a temp table or did I get something wrong here?

 
New Post
1/13/2016 4:52 PM
 
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 (Microsoft MVP)

dnnWerk - The DotNetNuke Experts   German Spoken DotNetNuke User Group

Speed up your DNN Websites with TurboDNN
 
Previous
 
Next
HomeHomeDevelopment and...Development and...SQL and SQL Ser...SQL and SQL Ser...SQL order by calculated columnSQL order by calculated column


These Forums are dedicated to discussion of DNN Platform and Evoq Solutions.

For the benefit of the community and to protect the integrity of the ecosystem, please observe the following posting guidelines:

  1. No Advertising. This includes promotion of commercial and non-commercial products or services which are not directly related to DNN.
  2. No vendor trolling / poaching. If someone posts about a vendor issue, allow the vendor or other customers to respond. Any post that looks like trolling / poaching will be removed.
  3. Discussion or promotion of DNN Platform product releases under a different brand name are strictly prohibited.
  4. No Flaming or Trolling.
  5. No Profanity, Racism, or Prejudice.
  6. Site Moderators have the final word on approving / removing a thread or post or comment.
  7. English language posting only, please.

Content Layout

Subscribe to DNN Digest

DNN Digest is our monthly email newsletter. It highlights news and content from around the DNN ecosystem, such as new modules and themes, messages from leadership, blog posts and notable tweets. Keep your finger on the pulse of the ecosystem by subscribing.  


Copyright 2017 by DNN Corp Terms of Use Privacy
What is Liquid Content?
Find Out
What is Liquid Content?
Find Out
What is Liquid Content?
Find Out