asked on

Autocomplete jQuery VERY slow in Classic ASP page

Hello,

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.
source.asp

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") & """},"

		rs4.movenext
		loop
		end if
		
		
		
		set cmd = nothing
				
		rs4.close
		set rs4 = nothing
		
		output=Left(output,Len(output)-1)
		output = output & "]"
		
		'response.write output & "<BR>"
		'response.write xcount & "<BR>"
	
		
%>

Open in new window

insert_personnel.asp

<%
dim output, outstr, mystring%>
<%
if not rs3.eof then
	do while not rs3.eof
			theChoices=theChoices&""&rs3("first_name")&""","""
		rs3.movenext
	loop
end if		

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

Open in new window

SOLUTION

leakim971

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Big Monty

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

SOLUTION

capturetheflag

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Big Monty

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 Fell

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 Monty

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.

ASKER CERTIFIED SOLUTION

Scott Fell

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Scott Fell

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, " ")
firstName=arrSearch(0)
LastName=arrSearch(1)
Phone=arrSearch(2)

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.

capturetheflag

ASKER

Thanks for the help and sorry for the late reply.