Shopping Cart Site Map - Sooo Sloooow!

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!

McKirahan
Sun Apr 03 11:00:37 CDT 2005

"Simon Harris" <too-much-spam@makes-you-fat.com> wrote in message
news:eMP1w4DOFHA.1096@tk2msftngp13.phx.gbl...
> 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
Sun Apr 03 11:19:49 CDT 2005

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

McKirahan
Sun Apr 03 13:02:29 CDT 2005

"Simon Harris" <too-much-spam@makes-you-fat.com> wrote in message
news:u#h02jGOFHA.3988@tk2msftngp13.phx.gbl...
> > 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
Sun Apr 03 13:09:33 CDT 2005

"McKirahan" <News@McKirahan.com> wrote in message
news:2_Kdndz2BKooss3fRVn-gA@comcast.com...
> "Simon Harris" <too-much-spam@makes-you-fat.com> wrote in message
> news:u#h02jGOFHA.3988@tk2msftngp13.phx.gbl...
> > > 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

Simon
Sun Apr 03 13:36:24 CDT 2005

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

Bob
Sun Apr 03 16:42:22 CDT 2005

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"

Aaron
Sun Apr 03 18:53:21 CDT 2005

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