Shopping Cart Site Map - Sooo Sloooow!

[Simon Harris]

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)

 

[McKirahan]

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?

 

[Simon Harris]

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.

 

[McKirahan]

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]

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

 

[Simon Harris]

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.

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.

 

[Bob Barrows [MVP]]

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

 

[Aaron [SQL Server MVP]]

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.