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?

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>

Open in new window

Thanks,
hefterr

ASKER CERTIFIED SOLUTION

_agx_

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

hefterr

ASKER

Hi agx_,
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

hefterr

ASKER

Thanks again!!

_agx_

> 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 ;-)

_agx_

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.

hefterr

ASKER

Is that approach specific to the ColdFusion implementation or is that a standard JS/Jquery appraoch? Just curious.

_agx_

Not sure. But it seems like it would be standard to js/jquery. Otherwise, autosuggest would be terribly inefficient.