Need help tweaking this ASP/DB Code please!

Results 1 to 3 of 3

Thread: Need help tweaking this ASP/DB Code please!

  1. #1
    Tom Levesque Guest

    Default Need help tweaking this ASP/DB Code please!

    OK Here goes. I&#039m making a page which displays various categories and sub categories of links. It gets these cats and subcats from a database. The only problem is the page loads kind of slowly (not really slowly, maybe two or three seconds, but I have a feeling it could be faster, there&#039s a noticable pause.)<BR><BR>The actual working page is at this location:<BR><BR>There you&#039ll be able to witness the slowdown for yourself.<BR><BR>Meanwhile here&#039s the code I&#039m running:<BR><BR> Dim conn, sql, categories, subcategories, cmd, count, count2<BR> <BR> Set conn = Server.CreateObject("ADODB.Connection")<BR> <BR> cmd = "DRIVER={Microsoft Access Driver (*.mdb)};"<BR> cmd = cmd & "DBQ=" & Server.MapPath("../fpdb/fpnwind.mdb")<BR> <BR> conn.Open(cmd)<BR><BR> sql = "SELECT * FROM CATEGORIES ORDER BY TITLE;"<BR> Set categories = conn.Execute(sql)<BR> <BR> count2 = 1<BR> <BR> While Not categories.EOF<BR><BR> count2 = count2 + 1<BR> <BR> categories.MoveNext<BR> <BR> WEnd<BR> <BR> categories.MoveFirst<BR> <BR> For cats = 1 to count2 - 1<BR> <BR> If cats = count2 2 Then <BR> <BR> Response.Write ("&#060;/td&#062;")<BR> Response.Write ("&#060;td width=&#039 50%&#039 valign=&#039top&#039&#062;")<BR> <BR> End If<BR> <BR> Response.Write ("&#060;p class=&#039xsmallfont&#039&#062;&#060;b&#062;&#060 ;a href=&#039viewcategory.asp?category=" & categories("ID") & "&#039&#062;" & categories("TITLE") & "&#060;/a&#062;&#060;/b&#062;<BR>")<BR> <BR> sql = "SELECT * FROM SUB_CATEGORIES WHERE CATEGORY = " & categories("ID") & " ORDER BY TITLE;"<BR> Set subcategories = conn.Execute(sql)<BR> <BR> count = 1<BR> <BR> While Not subcategories.EOF<BR> <BR> count = count + 1<BR> <BR> subcategories.MoveNext<BR> <BR> WEnd<BR> <BR> subcategories.MoveFirst<BR> <BR> For subcats = 1 to count - 2<BR> <BR> Response.Write("&#060;a class=&#039xsmallfont&#039 href=&#039viewsubcategory.asp?subcat=" & subcategories("ID") & "&#039&#062;" & subcategories("TITLE") & "&#060;/a&#062;, ")<BR> <BR> subcategories.MoveNext<BR> <BR> Next<BR> <BR> Response.Write("&#060;a class=&#039xsmallfont&#039 href=&#039viewsubcategory.asp?subcat=" & subcategories("ID") & "&#039&#062;" & subcategories("TITLE") & "&#060;/a&#062;.")<BR> <BR> categories.MoveNext<BR> <BR> Next <BR> <BR> conn.Close<BR> <BR> Set conn = Nothing<BR><BR><BR>Please make suggestions!

  2. #2
    Joseph Price Guest

    Default RE: Need help tweaking this ASP/DB Code please!

    Hi There-<BR><BR>Well looking at your code it could be improved a little bit. First thing is get rid of access :) But you probably cannot do anything about that.<BR><BR>Second thing is do a join between cat and subcat, that way you only make of db call, which will limit your loops as well. You also do not need to go through the entire recordset once to get a count. I would set a variable, increment it by 1, and then do a mod to determine if i need to start a new td or tr.<BR><BR>Well...that is about it!<BR><BR>- Joe Price<BR>

  3. #3
    ADT Guest

    Default RE: Need help tweaking this ASP/DB Code please!

    Personally I feel there is a whole lot of redundancy in code which you can definitely avoid. <BR>1. Since you need to open read only recorsets, open them as Static records using adopenStatic option.<BR>eg objRec.Open sql, connection, adOpenStatic<BR>2. then you get the record count categories.recordcount, since it is a static recorset it will give you the count. This way you can avoid 2 unnecessary looping thru the recordsets categories and subcategories. Obviously you can still keep a count variable to check if your TD separation<BR>3. Another very important thing is Using Outer joins in SQL you can manage to get the entire Recordsets with categories and subcategories into One recordset. So you done need to run 2 quires at all ie 1 to get the cats and one to get the subcats from the cats. Once you get all the records in one recordset your record fileds would be something like <BR>catid catname subbcatud subcatname <BR>1 Computer 12 Internet<BR>1 computer 13 tutoorial on ASP<BR>2 Buisness 16 journal<BR>2 business NULL NULL<BR>Now you can process this recordset to display it the way you want. I would use a logic to display catname only once based on the subcatid.<BR><BR>This way you will get atleast a 50% performance enhancement.<BR><BR>-ADT<BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts