Solved

Autocomplete jQuery VERY slow in Classic ASP page

Posted on 2013-12-23
9
1,180 Views
Last Modified: 2014-02-04
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
Comment
Question by:capturetheflag
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 82

Assisted Solution

by:leakim971
leakim971 earned 250 total points
ID: 39736050
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
 
LVL 32

Expert Comment

by:Big Monty
ID: 39736055
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
 
LVL 1

Assisted Solution

by:capturetheflag
capturetheflag earned 0 total points
ID: 39736074
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
 
LVL 32

Expert Comment

by:Big Monty
ID: 39736085
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 52

Expert Comment

by:Scott Fell, EE MVE
ID: 39736142
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
 
LVL 32

Expert Comment

by:Big Monty
ID: 39736184
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
 
LVL 52

Accepted Solution

by:
Scott Fell,  EE MVE earned 250 total points
ID: 39736203
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
 
LVL 52

Expert Comment

by:Scott Fell, EE MVE
ID: 39736230
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
 
LVL 1

Author Closing Comment

by:capturetheflag
ID: 39831971
Thanks for the help and sorry for the late reply.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction JSON is an acronym for JavaScript Object Notation.  It is a text-string data transport mechanism, capable of representing simple or complex data structures in a consistent and easy-to-read manner.  Similar in concept to XML, but more e…
JavaScript can be used in a browser to change parts of a webpage dynamically. It begins with the following pattern: If condition W is true, do thing X to target Y after event Z. Below are some tips and tricks to help you get started with JavaScript …
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn the basics of jQuery, including how to invoke it on a web page. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery.: (CODE)

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now