Post reply

What is an efficient way to retrieve product counts at multiple levels of a hierarchy?

  • September 2, 2011 at 7:30 am

    #247372

    I'm working on an e-business website to sell books and also want to display the number of available books in the category list.

    The category list contains the genre and the format (pocket, ebook, audiobook etc). In each element in genre and format I would like to display the number of available products from the stock list. Of course, when you go deeper into the hierarchy, you retrieve more specific information of available books at that level.

    For instance there are 2000 available books in the "medicine" genre and if I select the "pocket" format then there are 200 available books left. In other words, you have combined "pocket" and "medicine" and you know now that there are 200 available pocket books in total in the "medicine" genre.

    My question is:

    How do you create that functionality without making lots of effort to ask the database about the amount of available products in each element? Do you need to buy a special application or plugin? Please remember that there are millions of books in the database.

    I really understand that you use cache the result or something similiar but the main is HOW to do it. I'm using Visual studio, CMS and SQL server 2008 R2 in this project.

  • September 2, 2011 at 8:01 am

    #1376447

    First, welcome aboard! 🙂

    There are 3 basic types of hierarchies... how are your hierarchies being stored? Adjacency List, Hierarchical Path, Nested Sets?

    Since you're brand-spanking new to this forum, please take a minute to read the article at the first link in my signature line below. It'll help you get tested, coded answers much more quickly than providing a narrative of the problem alone.

    Given your good narrative, this is actually a pretty easy problem but it depends an awful lot on what your data looks like. If you can provide the test data in a fashion outlined in the article I directed you to, it would answer a whole lot of questions that I and others will have.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply