[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Autocomplete jQuery VERY slow in Classic ASP page

Posted on 2013-12-23
9
Medium Priority
?
1,332 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 82

Assisted Solution

by:leakim971
leakim971 earned 1000 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 33

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

 
LVL 33

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
 
LVL 54

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 33

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 54

Accepted Solution

by:
Scott Fell,  EE MVE earned 1000 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 54

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

Tech or Treat!

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article you'll learn how to use Ajax calls within your CodeIgniter application. To explain this, I'll illustrate how to implement a simple contact form to allow visitors to send you an email through your web site.
In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
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)
The viewer will learn the basics of jQuery including how to code hide show and toggles. 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…

650 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