Recursively Build Navigation in ASP

  • s15199d
  • Expert
  • Expert
  • User avatar
  • Posts: 524
  • Loc: NC, USA

Post 3+ Months Ago

I've got a script that recursively builds my website navigation based on a SQL table.

It works perfectly, but it's slow, and my page-load speeds are suffering. Going from under 150 milliseconds to over 2600 milliseconds.

So, I'm trying to re-engineer my script to accomplish the same goal, but do so more quickly.

My SQL table has, ID, ParentID, Name where ID is unique.

Anyone got a handy script for this? It seems to me this would be a common task...I've Googled it and nothing good came of it.

Thanks in advance!
  • s15199d
  • Expert
  • Expert
  • User avatar
  • Posts: 524
  • Loc: NC, USA

Post 3+ Months Ago

Code: [ Select ]
    'LEVEL 0
    sql = "select id, name from chief_product where parentid=0 and showitem='yes' and type='menu' order by name"
    
    Set rs0 = nothing
    Set rs0 = querySQL(sql)    
    if not rs0.EOF then
        
        'FOR EACH ITEM IN rs0 RESPONSE.WRITE rs0.name AND GET CHILDREN
        'LEVEL 1
        response.Write("<ul>")
        while not rs0.EOF            
            if lcase(rs0.fields("name").value) <> "survey" then                
                sql = "select id, name from chief_product where parentid="& rs0.fields("id").value &" and showitem='yes' and type='menu' order by name"
                Set rs1 = nothing
                Set rs1 = querySQL(sql)        
                if not rs1.EOF then
                    response.Write("<li><a href='"&basepath&replace(rs0.fields("name").value, "/", ",")&"'>" & rs0.fields("name").value & "&nbsp;&raquo;</a>")
                    'FOR EACH ITEM IN rs1 RESPONSE.WRITE rs1.name AND GET CHILDREN
                    'LEVEL 2
                    response.Write("<ul>")
                    while not rs1.EOF                        
                        sql = "select id, name from chief_product where parentid="& rs1.fields("id").value &" and showitem='yes' and type='menu' order by name"
                        Set rs2 = nothing
                        Set rs2 = querySQL(sql)        
                        if not rs2.EOF then
                            response.Write("<li><a href='"&basepath&replace(rs0.fields("name").value, "/", ",")&"/"&replace(rs1.fields("name").value, "/", ",")&"'>" & rs1.fields("name").value & "&nbsp;&raquo;</a>")
                            'FOR EACH ITEM IN rs2 RESPONSE.WRITE rs2.name AND GET CHILDREN
                            'LEVEL 3    
                            response.Write("<ul>")                
                            while not rs2.EOF                                
                                sql = "select id, name from chief_product where parentid="& rs2.fields("id").value &" and showitem='yes' and type='menu' order by name"
                                Set rs3 = nothing
                                Set rs3 = querySQL(sql)        
                                if not rs3.EOF then
                                    response.Write("<li><a href='"&basepath&replace(rs0.fields("name").value, "/", ",")&"/"&replace(rs1.fields("name").value, "/", ",")&"/"&replace(rs2.fields("name").value, "/", ",")&"'>" & rs2.fields("name").value & "&nbsp;&raquo;</a>")

                                else
                                    response.Write("<li><a href='"&basepath&replace(rs0.fields("name").value, "/", ",")&"/"&replace(rs1.fields("name").value, "/", ",")&"/"&replace(rs2.fields("name").value, "/", ",")&"'>" & rs2.fields("name").value & "</a>")
                                end if
                                response.Write("</li>")
                                rs2.movenext
                            wend
                            response.Write("</ul>")        
                        else
                            response.Write("<li><a href='"&basepath&replace(rs0.fields("name").value, "/", ",")&"/"&replace(rs1.fields("name").value, "/", ",")&"'>" & rs1.fields("name").value & "</a>")
                        end if
                        response.Write("</li>")
                        rs1.movenext
                    wend
                    response.Write("</ul>")            
            
                else
                    response.Write("<li><a href='"&basepath&replace(rs0.fields("name").value, "/", ",")&"'>" & rs0.fields("name").value & "</a>")                                
                end if                        
                response.Write("</li>")
            
            end if 'excludes SURVEY
            rs0.movenext
        wend        
        response.Write("<li style='color:black;'><a href='"&basepath&"Survey'>Survey</a></li>")
            
    end if
  1.     'LEVEL 0
  2.     sql = "select id, name from chief_product where parentid=0 and showitem='yes' and type='menu' order by name"
  3.     
  4.     Set rs0 = nothing
  5.     Set rs0 = querySQL(sql)    
  6.     if not rs0.EOF then
  7.         
  8.         'FOR EACH ITEM IN rs0 RESPONSE.WRITE rs0.name AND GET CHILDREN
  9.         'LEVEL 1
  10.         response.Write("<ul>")
  11.         while not rs0.EOF            
  12.             if lcase(rs0.fields("name").value) <> "survey" then                
  13.                 sql = "select id, name from chief_product where parentid="& rs0.fields("id").value &" and showitem='yes' and type='menu' order by name"
  14.                 Set rs1 = nothing
  15.                 Set rs1 = querySQL(sql)        
  16.                 if not rs1.EOF then
  17.                     response.Write("<li><a href='"&basepath&replace(rs0.fields("name").value, "/", ",")&"'>" & rs0.fields("name").value & "&nbsp;&raquo;</a>")
  18.                     'FOR EACH ITEM IN rs1 RESPONSE.WRITE rs1.name AND GET CHILDREN
  19.                     'LEVEL 2
  20.                     response.Write("<ul>")
  21.                     while not rs1.EOF                        
  22.                         sql = "select id, name from chief_product where parentid="& rs1.fields("id").value &" and showitem='yes' and type='menu' order by name"
  23.                         Set rs2 = nothing
  24.                         Set rs2 = querySQL(sql)        
  25.                         if not rs2.EOF then
  26.                             response.Write("<li><a href='"&basepath&replace(rs0.fields("name").value, "/", ",")&"/"&replace(rs1.fields("name").value, "/", ",")&"'>" & rs1.fields("name").value & "&nbsp;&raquo;</a>")
  27.                             'FOR EACH ITEM IN rs2 RESPONSE.WRITE rs2.name AND GET CHILDREN
  28.                             'LEVEL 3    
  29.                             response.Write("<ul>")                
  30.                             while not rs2.EOF                                
  31.                                 sql = "select id, name from chief_product where parentid="& rs2.fields("id").value &" and showitem='yes' and type='menu' order by name"
  32.                                 Set rs3 = nothing
  33.                                 Set rs3 = querySQL(sql)        
  34.                                 if not rs3.EOF then
  35.                                     response.Write("<li><a href='"&basepath&replace(rs0.fields("name").value, "/", ",")&"/"&replace(rs1.fields("name").value, "/", ",")&"/"&replace(rs2.fields("name").value, "/", ",")&"'>" & rs2.fields("name").value & "&nbsp;&raquo;</a>")
  36.                                 else
  37.                                     response.Write("<li><a href='"&basepath&replace(rs0.fields("name").value, "/", ",")&"/"&replace(rs1.fields("name").value, "/", ",")&"/"&replace(rs2.fields("name").value, "/", ",")&"'>" & rs2.fields("name").value & "</a>")
  38.                                 end if
  39.                                 response.Write("</li>")
  40.                                 rs2.movenext
  41.                             wend
  42.                             response.Write("</ul>")        
  43.                         else
  44.                             response.Write("<li><a href='"&basepath&replace(rs0.fields("name").value, "/", ",")&"/"&replace(rs1.fields("name").value, "/", ",")&"'>" & rs1.fields("name").value & "</a>")
  45.                         end if
  46.                         response.Write("</li>")
  47.                         rs1.movenext
  48.                     wend
  49.                     response.Write("</ul>")            
  50.             
  51.                 else
  52.                     response.Write("<li><a href='"&basepath&replace(rs0.fields("name").value, "/", ",")&"'>" & rs0.fields("name").value & "</a>")                                
  53.                 end if                        
  54.                 response.Write("</li>")
  55.             
  56.             end if 'excludes SURVEY
  57.             rs0.movenext
  58.         wend        
  59.         response.Write("<li style='color:black;'><a href='"&basepath&"Survey'>Survey</a></li>")
  60.             
  61.     end if
  • s15199d
  • Expert
  • Expert
  • User avatar
  • Posts: 524
  • Loc: NC, USA

Post 3+ Months Ago

Any thoughts on how to speed this up?
  • s15199d
  • Expert
  • Expert
  • User avatar
  • Posts: 524
  • Loc: NC, USA

Post 3+ Months Ago

My navigation is loaded into each page as an include file...

Is it possible to cache an include file?

I've added
Code: [ Select ]
<%response.Expires=30%>


to the nav file that is being included...but thats cacheing the page that's loading the nav file...not the nav file itself.

Example: default.asp loads nav.inc

nav.inc has the previously mentioned code.

The result is default.asp is getting cached.

I'd appreciate any input you may have...

Post Information

  • Total Posts in this topic: 4 posts
  • Users browsing this forum: No registered users and 147 guests
  • You cannot post new topics in this forum
  • You cannot reply to topics in this forum
  • You cannot edit your posts in this forum
  • You cannot delete your posts in this forum
  • You cannot post attachments in this forum
 
 

© 1998-2014. Ozzu® is a registered trademark of Unmelted, LLC.