[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1349
  • Last Modified:

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

0
capturetheflag
Asked:
capturetheflag
  • 3
  • 3
  • 2
  • +1
3 Solutions
 
leakim971PluritechnicianCommented:
Is there a way to make the query only trigger after 2 or 3 amount of characters have been typed?  
Here the link : http://api.jqueryui.com/autocomplete/#option-minLength

I guess this would be in the source.asp?
No:

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

Open in new window

0
 
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
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
0
 
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
    });
    });
</script>

Open in new window


thanks for the help
capturetheflag
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
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?
0
 
Scott Fell, EE MVEDeveloperCommented:
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.
0
 
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
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.
0
 
Scott Fell, EE MVEDeveloperCommented:
Is your db on a shared server like godaddy or similar?  Shared SQL servers can choke except for something like windowsazure.com.

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
next

Open in new window

GetRows will speed things up
http://www.w3schools.com/ado/met_rs_getrows.asp
http://www.4guysfromrolla.com/webtech/070500-1.shtml
0
 
Scott Fell, EE MVEDeveloperCommented:
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.
0
 
capturetheflagAuthor Commented:
Thanks for the help and sorry for the late reply.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 3
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now