hefterr
asked on
ColdFusion AutoSuggest Question
Hi,
I am doing an Ajax autosuggest (see code below). I only return 5 choices at a time.
My question is should the SQL (using SQL Server 2005) use
Select Distinct top(5) OrgName versus
Select Distinct OrgName
There is an order by so SQL Server has to sort them all but if I only need 5 returned, it should be faster? But I'm not sure how this works in ColdFusion?
hefterr
I am doing an Ajax autosuggest (see code below). I only return 5 choices at a time.
My question is should the SQL (using SQL Server 2005) use
Select Distinct top(5) OrgName versus
Select Distinct OrgName
There is an order by so SQL Server has to sort them all but if I only need 5 returned, it should be faster? But I'm not sure how this works in ColdFusion?
FORM FIELD:
<cfinput type="text" name="orgName" size=30 maxlength="100"
autosuggest="cfc:org-methods.orgName_autosuggest({cfautosuggestvalue})"
autosuggestMinLength="2"
maxResultsDisplayed = "5"/>
CFC:
<cffunction name="orgName_autosuggest" access="remote" returnType="array">
<cfargument name="search" type="any" required="false" default="">
<cfset var OrgNameSearch="">
<cfset var OrgResult=ArrayNew(1)>
<cfquery name="OrgNameSearch" datasource="#application.socialDSN#">
Select Distinct OrgName
from TestOrganization
WHERE Upper(OrgName) Like Upper('#Arguments.search#%')
ORDER BY OrgName ASC
</cfquery>
<cfloop query="OrgNameSearch">
<cfset arrayappend(OrgResult, OrgName)>
</cfloop>
<cfreturn OrgResult>
</cffunction>
Thanks,hefterr
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks again!!
> I thought it was on every keystroke.
I used to think that too :) Enable ajax debugging in the CF Admin (and add ?cfdebug to your page) and you can see what's it's doing.
When you start typing, the javascript control sends an initial request to .cfc for data matching what you typed. It then caches the data on the client side and filters it as you type. It only sends a new request to the server when it can't find a match in it's cache.
Example:
1) You type "Jo"
2) The autosuggest has no data. So it sends a request to the server for records starting with "Jo". It caches the returned data and displays the first X records that match (ie maxResultsDisplayed):
- Joanne
- Job
- Johan
- John
- ....
3) Then you type another character "h". The autosuggest filters the data it already has, and displays the next 5 items matching "Joh"
- Johan
- John
- ....
(... Lather-rinse-repeat ;-)
I used to think that too :) Enable ajax debugging in the CF Admin (and add ?cfdebug to your page) and you can see what's it's doing.
When you start typing, the javascript control sends an initial request to .cfc for data matching what you typed. It then caches the data on the client side and filters it as you type. It only sends a new request to the server when it can't find a match in it's cache.
Example:
1) You type "Jo"
2) The autosuggest has no data. So it sends a request to the server for records starting with "Jo". It caches the returned data and displays the first X records that match (ie maxResultsDisplayed):
- Joanne
- Job
- Johan
- John
- ....
3) Then you type another character "h". The autosuggest filters the data it already has, and displays the next 5 items matching "Joh"
- Johan
- John
- ....
(... Lather-rinse-repeat ;-)
Gah... I left off #4
4) You type another character "x". Autosuggest can't find a match in it's cache. So it sends off a new request to the server. Then displays the next 5 items matching "Johx" , if there are any.
4) You type another character "x". Autosuggest can't find a match in it's cache. So it sends off a new request to the server. Then displays the next 5 items matching "Johx" , if there are any.
ASKER
Is that approach specific to the ColdFusion implementation or is that a standard JS/Jquery appraoch? Just curious.
Not sure. But it seems like it would be standard to js/jquery. Otherwise, autosuggest would be terribly inefficient.
ASKER
OK. Thanks.. That was the kind of answer I was looking for as I did not know when the Autosuggest would fire up the remote CFC. I thought it was on every keystroke. In that case my question would have more relevance.
hefterr