Performance issue -- Faster to loop through record

Results 1 to 3 of 3

Thread: Performance issue -- Faster to loop through record

  1. #1
    J. Wilson Guest

    Default Performance issue -- Faster to loop through record

    I have a list of 50 checkboxes (one for each state).<BR>I want to pull in default values for a user based on whether or not they checked these boxes earlier.<BR>Would it be faster to pull in a recordset of all their selected values (0-50 rows possible), and for each state, loop through the recordset to see if it is in there...<BR>or for each state, do a seperate, specific query, opening a recordset to see if that particular state is in there. (open recordset 50 times.)<BR>Thanks.

  2. #2
    Join Date
    Dec 1969

    Default RE: Performance issue -- Faster to loop through re

    It would certainly be less taxing on your network to do a single query. If you then converted the recordset into an array with getrows and looped through the array, it would probably be many times faster as well.

  3. #3
    Join Date
    Dec 1969

    Default Faster still to rethink problem....

    EVERY query is a TERRIFIC hit on your system&#039s performance. One query is many, many times faster.<BR><BR>BUT...<BR><BR>Why a nested loop?<BR><BR>If you do an ORDER BY on the query, then you can do a "merge" on your list of states vs. the DB results.<BR><BR>Something like this:<BR><BR>&#060;%<BR>states = Array("AK", "AL", "AR", .... )<BR><BR>SQL = "SELECT state, ... FROM ... ORDER BY state"<BR>Set RS = yourConnection.Execute(SQL)<BR><BR>For i = 0 To UBound( states )<BR>&nbsp;&nbsp;&nbsp; info = "" &#039 no info from RS<BR>&nbsp;&nbsp;&nbsp; Response.Write "&#060;INPUT Type=Checkbox Name=State Value=&#039" & states(i) & "&#039"<BR>&nbsp;&nbsp;&nbsp; If NOT RS.EOF Then<BR>&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; If RS("state") = states(i) Then <BR>&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; Response.Write " CHECKED"<BR>&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; info = RS("someotherfield") &#039 ??? what you called &#039default values&#039???<BR>&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; RS.MoveNext<BR>&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; End If<BR>&nbsp;&nbsp;&nbsp; End If<BR>&nbsp;&nbsp;&nbsp; Response.Write "&#062;" & info<BR>Next<BR>%&#062;<BR><BR>See it? Since the states are in order in the array *AND* in the RecordSet, you simply go through them like you were trying to match cards in an ordered deck vs. cards in an ordered hand. You always flip a card in the deck, but you only flip one in the hand after you find a match in the deck. You won&#039t miss any, guaranteed!<BR><BR><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