AutoComplete / AutoSuggest load time

[cumap]

Hi all,
I'm working on a autocomplete/autosuggest box to search for a member in my mssql database ( >9000 records ) and succeed doing so.
My only problem is load time get up to almost 10 sec every time the page loads.
Basically,
- I opened db and saved the table to GetRows().
- Run the array to save Names and IDs into 2 different strings with comma deliminator.
- Assigned the 2 strings to JScript variables.
- JScript will then autocomplete searching for match typing name.
- When name was clicked, the Onclick will take me to the next page with the right ID# that has the same in the JScript For Loop.

I'm hoping that there is/are better way to do this and here is my code, briefly:

<%
mySQL = "SELECT memberName, id " _
	  & "FROM tblMembers " _
	  & "ORDER BY memberName" 
set rs = objConn.Execute(mySQL)
If NOT rs.eof then
	arrContacts = rs.GetRows()
End If
call closeRS(rs)

If isArray(arrContacts) then
	For iCust = 0 to uBound(arrContacts,2)
		if len(trim(arrContacts(0,iCust)))>0 then
			mNames = mNames & ", """ & REPLACE(arrContacts(0,iCust),"""", "'") & " (" & Trim(arrContacts(1,iCust)) & ")" & """"
			custIDs = custIDs & ", """ & arrContacts(2,iCust) & """"
		end if
	Next
	custNames = Mid(custNames,3)
	custIDs = MID(custIDs,3)
Else
	custNames = ""
	custIDs = ""
End If
%>
<script type="text/javascript">
// This Javascript is granted to the public domain.

// This is the javascript array holding the function list
// The PrintJavascriptArray ASP function can be used to print this array.
//Contact Company Name List
var functionlist = Array(<%=custNames%>);
//Contact ID list
var functionlistid = Array(<%=custIDs%>);

// This is the function that refreshes the list after a keypress.
// The maximum number to show can be limited to improve performance with
// huge lists (1000s of entries).
// The function clears the list, and then does a linear search through the
// globally defined array and adds the matches back to the list.
function handleKeyUp(maxNumToShow)
{
    var selectObj, textObj, functionListLength;
    var i, searchPattern, numShown;

    // Set references to the form elements
    selectObj = document.forms[0].functionselect;
    textObj = document.forms[0].functioninput;

    // Remember the function list length for loop speedup
    functionListLength = functionlist.length;

    // Set the search pattern depending
//    if(document.forms[0].functionradio[0].checked == true)
 //   {
         searchPattern = "^"+textObj.value;
//    }
 //   else
 //   {
 //      searchPattern = textObj.value;
 //   }

    // Create a regulare expression
    re = new RegExp(searchPattern,"gi");
    // Clear the options list
    selectObj.length = 0;

    // Loop through the array and re-add matching options
    numShown = 0;
    for(i = 0; i < functionListLength; i++)
    {
        if(functionlist[i].search(re) != -1)
        {
			document.forms[0].functionselect.style.display = "none";
			selectObj[numShown] = new Option(functionlist[i],functionlistid[i]);
			//selectObj[numShown] = new Option(functionlist[i],i+100);
            numShown++;
        } else {
			document.forms[0].functionselect.style.display = "";
		}
        // Stop when the number to show is reached
        if(numShown == maxNumToShow)
        {
            break;
        }
    }
	
    // When options list whittled to one, select that entry
    if(selectObj.length == 1)
    {
        selectObj.options[0].selected = true;
    }
}

// this function gets the selected value and loads the appropriate
// php reference page in the display frame
// it can be modified to perform whatever action is needed, or nothing
function handleSelectClick()
{
    selectObj = document.forms[0].functionselect;
    textObj = document.forms[0].functioninput;

    selectedValue = selectObj.options[selectObj.selectedIndex].value;
//    selectedValue = selectedValue.replace(/_/g, '-') ;
    parent.location.href = 
	"/edit.asp?id="+selectedValue;

}

</script>
<table width="300" style="margin:auto;">
<tbody><tr><td valign="top">
<div class="headertitle2" style="margin-top:-4px; color:#ff7f00">Search For Name</div>
<form onSubmit="handleSelectClick();return false;" action="#">
<input onKeyUp="handleKeyUp(100);" name="functioninput" style="font-size: 10pt; width: 40ex;" type="text" /><br />
<select onclick="handleSelectClick();" name="functionselect" size="20" style="display:none; font-size:10pt; width:41ex; height:auto">
</select>

</form>
</td>
</tr></tbody></table>

Most of tutorials out there only get me to set the Name to input text field without the ID#, so keeping this structure is preferable.

Thank you.

 

[Qik3Coder]

Have you run the select against the db itself, or on a test page to ensure that your select is not the problem?

You've got questions and source code. We want both!

 

[cumap]

The SELECT is against the db itself, no problem there. I understand there are many different ways to select from db and utilizing an array. From what I have had, can you tell me the faster or optimum way to run 9000+ records.

I understand this is ASP forum so let just focus into the SELECT part. (however, if anyone with jscript knowledge is welcome as well)

 

[Qik3Coder]

90k records isn't a lot to spin through with a select, but it will bring a drop down to it's knees.

You may want to wait for the first character and then do an async call to get the result set. Limiting to 10-20 to ease processing.




You've got questions and source code. We want both!

 

[BigRed1212]

Like QikCoder implies, I would look at something like:

http://jqueryui.com/demos/autocomplete/#remote

for this.

If you are hitting the database you should be able to pull back both a name and an ID for your link.

I've never used it and don't know anything about implementation.