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)


    --
    I am using the free version of SPAMfighter for private users.
    It has removed 2036 spam emails to date.
    Paying users do not have this message in their emails.
    Try www.SPAMfighter.com for free now!
     
    Simon Harris, Apr 3, 2005
    #1
    1. Advertising

  2. Simon Harris

    McKirahan Guest

    "Simon Harris" <> wrote in message
    news:...
    > 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.


    [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. Advertising

  3. Simon Harris

    Simon Harris Guest

    > If the data is relatively static why not just generate
    > a static "include" periodically, perhaps nightly?


    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.


    --
    I am using the free version of SPAMfighter for private users.
    It has removed 2036 spam emails to date.
    Paying users do not have this message in their emails.
    Try www.SPAMfighter.com for free now!
     
    Simon Harris, Apr 3, 2005
    #3
  4. Simon Harris

    McKirahan Guest

    "Simon Harris" <> wrote in message
    news:u#...
    > > If the data is relatively static why not just generate
    > > a static "include" periodically, perhaps nightly?

    >
    > 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.


    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

    "McKirahan" <> wrote in message
    news:...
    > "Simon Harris" <> wrote in message
    > news:u#...
    > > > If the data is relatively static why not just generate
    > > > a static "include" periodically, perhaps nightly?

    > >
    > > 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.

    >
    > You could just add a subroutine to regenerate the static include
    > either on-demand or every time the tables are changed.


    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 include
    >> either on-demand or every time the tables are changed.

    I 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.

    > 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


    Cool, thanks.
     
    Simon Harris, Apr 3, 2005
    #6
  7. Simon Harris wrote:
    > 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! :)
    >

    Recordset loops are slow. See this article for faster alternatives:
    http://www.aspfaq.com/show.asp?id=2467

    Bob Barrows
    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
     
    Bob Barrows [MVP], Apr 3, 2005
    #7
  8. > I 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.


    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. Advertising

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. John Dalberg
    Replies:
    0
    Views:
    308
    John Dalberg
    Apr 30, 2004
  2. Replies:
    2
    Views:
    284
    nicolas_riesch
    Aug 23, 2005
  3. _george
    Replies:
    1
    Views:
    130
    _george
    Oct 10, 2007
  4. Michael Neumann

    "val.dup rescue val" sloooow

    Michael Neumann, Oct 27, 2004, in forum: Ruby
    Replies:
    2
    Views:
    134
    Michael Neumann
    Oct 27, 2004
  5. George  Moschovitis

    Why is webrick sooo slow?

    George Moschovitis, Jan 24, 2005, in forum: Ruby
    Replies:
    8
    Views:
    236
    Alexander Kellett
    Jan 31, 2005
Loading...

Share This Page