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
insert_personnel.asp
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>"
%>
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
%>
ASPJavaScriptjQuery
Last Comment
capturetheflag
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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?
how much data are you pulling back? does the sql run slow directly in query analyzer?
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.
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.
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
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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
Based on the UBound count, I adjusted my sql and was able to use AND.
Now you can search multiple fields with one input box.
In simplified form
search=request("name") ' mike smith 555-555-5555
arrSearch=split(search, " ")
firstName=arrSearch(0)
LastName=arrSearch(1)
Phone=arrSearch(2)
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
Now you can search multiple fields with one input box.
ASKER
Thanks for the help and sorry for the late reply.
$( ".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