Shopping Cart Site Map - Sooo Sloooow!

Discussion in 'ASP General' started by Simon Harris, Apr 3, 2005.

  1. Simon Harris

    Simon Harris Guest

    Hi All,

    I have written a page that generates a site map of all categories and
    products within a shopping cart system. It works a treat, apart from being
    toooo slooow.

    The code is below, can anyone think of a way of generating the same
    results,
    but more efficiently? There are 1187 categories, 471 products (This may
    seem
    like an odd ratio, It's structured that way due to the nature of the
    business). Categories can hold any number of other categories, and
    products.

    For info, just running showCatsWithinCat with the call to
    showProdsWithinCat
    commented out, takes approx 10 seconds. With the products displayed takes
    approx 40 seconds!

    I was thinking perhaps some TSQL to generate this within SQL server - Not
    sure this was speed things up though? Also, I wouldnt know where to start
    with TSQL.

    Hope some one has a cool idea for this! :)

    Cheers,
    Simon.

    ---------------

    '**************************************
    'Display Products within given category
    '**************************************
    sub showProdsWithinCat(CatID)

    'Declare Variables
    dim mySQL, rsTemp

    'Get and loop data
    mySQL = "SELECT a.idProduct,a.description,a.descriptionLong,a.details
    FROM
    products a " &_
    "WHERE a.active = -1 AND EXISTS " &_
    "(SELECT b.idCategory FROM categories_products b WHERE b.idProduct =
    a.idProduct AND b.idCategory = " & CatID & ")"
    Response.Write("<ul>")
    set rsTemp = openRSexecute(mySQL)
    do while not rsTemp.eof
    response.write "<li><a href=""" & urlNonSSL & "prodView.asp?idproduct="
    &
    rsTemp("idProduct") &""">" & rsTemp("description") & "</a><br>" &
    rsTemp("description") & "<br>" & rsTemp("descriptionLong") & "<br>" &
    rsTemp("details") & "</li>"
    rsTemp.movenext:loop
    Response.Write("</ul>")

    'Close up
    call closeRS(rsTemp)

    end sub


    '**************************************
    'Display category tree in bulleted list
    '**************************************
    sub showCatsWithinCat(CatID)

    'Declare variables
    dim mySQL, rsTemp

    'Get and loop data
    mySQL ="SELECT idCategory, categoryDesc, categoryHTMLLong FROM Categories
    WHERE (idParentCategory = " & CatID & ");"
    Response.Write("<ul>")
    set rsTemp = openRSexecute(mySQL)
    do while not rsTemp.eof
    response.write "<li><a href=""" & urlNonSSL & "prodList.asp?idCategory="
    &
    rsTemp("idCategory") &""">" & rsTemp("categoryDesc") & "</a><br></li>"
    Call showProdsWithinCat(rsTemp("idCategory"))
    Call showCatsWithinCat(rsTemp("idCategory"))
    rsTemp.movenext:loop
    Response.Write("</ul>")

    'Close up
    call closeRS(rsTemp)
    end sub


    '**************************************
    'Display Site Map
    '**************************************
    Call showCatsWithinCat(0)
     
    Simon Harris, Apr 3, 2005
    #1
    1. Advertisements

  2. Simon Harris

    McKirahan Guest

    [snip]

    Just a thought...

    Presuming that the following is toooo slooow:

    '**************************************
    'Display category tree in bulleted list
    '**************************************
    sub showCatsWithinCat(CatID)

    If the data is relatively static why not just generate
    a static "include" periodically, perhaps nightly?
     
    McKirahan, Apr 3, 2005
    #2
    1. Advertisements

  3. Simon Harris

    Simon Harris Guest

    If the data is relatively static why not just generate
    Nice idea, which I will bear in mind if I dont come up with anything
    else...I dont have access to the server to add overnight tasks, but we do
    have direct access to the SQL Server, so it may have to be a VB Script,
    running on a PC some where.

    Thanks for the post.

    Any other ideas on speeding this up anyone?

    Thanks,
    Simon.
     
    Simon Harris, Apr 3, 2005
    #3
  4. Simon Harris

    McKirahan Guest

    You could just add a subroutine to regenerate the static include
    either on-demand or every time the tables are changed.
     
    McKirahan, Apr 3, 2005
    #4
  5. Simon Harris

    McKirahan Guest

    Also, you can invoke an overnight (or other period) task via the
    Windows Task scheduler. Something like this on the "Run" line:

    C:\PROGRA~1\INTERN~1\IEXPLORE.EXE
    http://www.your-domain.com/your-page.asp?your_parm
     
    McKirahan, Apr 3, 2005
    #5
  6. Simon Harris

    Simon Harris Guest

    You could just add a subroutine to regenerate the static includeI did consider that, but it would be almost as slow as writing out to the
    page, so the script would probably time out on when I put it into
    production.
    Cool, thanks.
     
    Simon Harris, Apr 3, 2005
    #6
  7. Recordset loops are slow. See this article for faster alternatives:
    http://www.aspfaq.com/show.asp?id=2467

    Bob Barrows
     
    Bob Barrows [MVP], Apr 3, 2005
    #7
  8. I did consider that, but it would be almost as slow as writing out to the
    But it wouldn't be something an end user would invoke, it would be something
    an admin would invoke.

    You could also consider having a separate text file for each category
    (listing all its subcategories) and only updating THAT text file when a
    product in its category tree is modified. A single #include file would be
    used to pull them all together.
     
    Aaron [SQL Server MVP], Apr 4, 2005
    #8
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.