Autocomplete jQuery VERY slow in Classic ASP page


I am trying to get jQuery auto-complete to work in a classic ASP web page.
It is working but is very slow.  I am hitting a DB of about 400k names.
Here is the code for the two files. One is source.asp, the second is insert_personnel.asp
Is there a way to make the query only trigger after 2 or 3 amount of characters have been typed?  I guess this would be in the source.asp?
Thanks for the help.
dim xcount : xcount = 0
		if not rs4.eof then
		do while not rs4.eof
		xcount = xcount + 1
		output = "[" 
		output = output & "{""user_id"": """ & rs4("user_id") & """, ""value"": """ & rs4("last_name") & """},"

		end if
		set cmd = nothing
		set rs4 = nothing
		output = output & "]"
		'response.write output & "<BR>"
		'response.write xcount & "<BR>"

Open in new window

dim output, outstr, mystring%>
if not rs3.eof then
	do while not rs3.eof
end if		

'''''cleanup and reformat string
theChoices = Replace(theChoices,""","""",""","")
theChoices = """"&theChoices &""""
'response.write theChoices

Open in new window

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Is there a way to make the query only trigger after 2 or 3 amount of characters have been typed?  
Here the link :

I guess this would be in the source.asp?

$( ".selector" ).autocomplete({ minLength: 3 });

Open in new window

Big MontyWeb Ninja at largeCommented:
you can set the minimum number of characters needed before it fires by using the minlength property:

$( ".autocompleteBoxName" ).autocomplete({ minLength: 2 });

other things you could try is to just run the query directly in sql and see how long it takes to run. if it's slow there, then you should fine tune the query
capturetheflagAuthor Commented:
I forgot the java script code.  Here it is.

<script type="text/javascript" language="javascript">

   $(function() {	   
	   var availableTags = [<%=theChoices%>]; 
	   $( "#username" ).autocomplete({
		   source: availableTags

    $(function() {
        $( "#username2" ).autocomplete({
            source: "source.asp",
            minLength: 2

Open in new window

thanks for the help
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

Big MontyWeb Ninja at largeCommented:
looks like you already have the minlength option in there so that isn't an issue.

how much data are you pulling back? does the sql run slow directly in query analyzer?
Scott FellDeveloper & EE ModeratorCommented:
In your sql, you want to limit to 20 or 30 results.  

sql="Select top 30 from MyTable where name like '"&theName&"%'"

With 400K people in the db, you are going to have a lot of match's even with the first 3 characters.
Big MontyWeb Ninja at largeCommented:
you may also want to consider, if this is the case, to convert your sql over to a view or stored procedure. This way, you make the database do the majority of the processing of the data.
Scott FellDeveloper & EE ModeratorCommented:
Is your db on a shared server like godaddy or similar?  Shared SQL servers can choke except for something like

However, you can change your output to getrows.

'this code should be at the top of your page
if not rs3.eof then
    theChoices=rs3.getrows() ' make sure your query for rs3 only outputs the first name
end if
' close your db here		

'place this where you want your output
for each name in theChoices
   response.write name  ' add your formatting too

Open in new window

GetRows will speed things up

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott FellDeveloper & EE ModeratorCommented:
I have used auto complete like this and checked for first and last name and phone number.  I used a space as a delimiter.  If the input was "mike smith 555-555-5555" for instance.  Then I break out each like.

In simplified form
search=request("name") ' mike smith 555-555-5555
arrSearch=split(search, " ")

Open in new window

What I actually ended up doing was checking the length of the array using UBound so if there was only, "mike", the code did not try and use array(1) or array(2).    Then I checked to see if the first digit was numeric to test for phone.  To keep things fast, if the first 2 rows in the array are non numeric, it is assumed array(0) and array(1) are first and last names respectively.  

Based on the UBound count, I adjusted my sql and was able to use AND.  
if UBound(arSearch)=2 then
  ' use first and last name
sql= "...WHERE first_name like '"&firstname&"%' AND last_name like '"&LastName&%"'"
end if

Open in new window

Now you can search multiple fields with one input box.
capturetheflagAuthor Commented:
Thanks for the help and sorry for the late reply.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.