Function Gets Timeout Error. Is There A More Efficient Way To Rewrite This Function?

Question

This CF Function is getting a timeout error. It's executed in a page that has more stuff going on. Is there a more efficient way to rewrite this function?

<cffunction name="GetTheSerialNumber" access="remote" returnformat="plain" returntype="string" output="no">
    <cfargument name="term" required="yes" />

    <cfsetting requesttimeout="20"/>
    <cfif NOT IsDefined("session.LoggedIn")>
        <cfabort />
    </cfif>

    <cfquery name="GetSerialNumber" datasource="#application.datasource#">
        SELECT [serialNum]
            ,MissionAsset.MissionAssetID
            ,MissionAssetDescription
            ,ESN.EfracusSerialNumberID
        FROM MissionAsset        
            LEFT JOIN EfracusSerialNumber ESN ON MissionAsset.MissionAssetID = ESN.MissionAssetID
        WHERE MissionAsset.isActive = <cfqueryparam value="1" cfsqltype="cf_sql_bit"> 
            AND serialNum LIKE <cfqueryparam value="%#arguments.term#%" cfsqltype="cf_sql_varchar"> 
            AND MissionAssetStatusID IN (2,3,4,5,6)
        ORDER BY serialNum
    </cfquery>

    <cfset stcReturn = '['>

    <cfloop query="GetSerialNumber">
        <cfset stcReturn &= '{"label":"#GetSerialNumber.serialNum#", "value":"#GetSerialNumber.serialNum#", "missionAssetID": "#GetSerialNumber.MissionAssetID#", "missionAssetDescription": "#GetSerialNumber.MissionAssetDescription#", "ID" : "#GetSerialNumber.EfracusSerialNumberID#"}'>
        <cfif GetSerialNumber.CurrentRow NEQ GetSerialNumber.RecordCount>
            <cfset stcReturn &= ",">
        <cfelse>
            <cfset stcReturn &= "]">
        </cfif>
    </cfloop>

    <cfreturn stcReturn>
</cffunction> 

Update: I took some of the suggestions below and optimized the function without having to change any of the front end. This is what we have now. Thanks!

<cffunction name="GetTheSerialNumber" access="remote" returnformat="plain" returntype="string" output="no">
    <cfargument name="term" required="yes">

    <cfsetting requesttimeout="20"/>
    <cfif NOT IsDefined("session.LoggedIn")>
        <cfabort />
    </cfif>
    
    <cfquery name="GetSerialNumber" datasource="#application.datasource#">
        SELECT [serialNum] as LabelValue, MissionAsset.MissionAssetID as MAID, MissionAssetDescription as MAD, ESN.EfracusSerialNumberID as ESNID
        FROM MissionAsset        
            LEFT JOIN EfracusSerialNumber ESN ON MissionAsset.MissionAssetID = ESN.MissionAssetID
        WHERE MissionAsset.isActive = <cfqueryparam cfsqltype="cf_sql_bit" value="1"> 
            AND serialNum LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="%#arguments.term#%"> 
            AND MissionAssetStatusID IN (2,3,4,5,6)
        ORDER BY LabelValue
    </cfquery>

    <cfset stcReturn = '['>
    <cfloop query="GetSerialNumber" >
        <cfset stcReturn &= '{"label":"#LabelValue#", "value":"#LabelValue#", "missionAssetID": "#MAID#", "missionAssetDescription": "#MAD#", "ID" : "#ESNID#"}'>
        <cfif GetSerialNumber.CurrentRow NEQ GetSerialNumber.RecordCount>
            <cfset stcReturn &= ",">
        </cfif>
    </cfloop>
    <cfset stcReturn &= ']'>

    <cfreturn stcReturn>
</cffunction>    

Answer 1

The query is most likely the culprit; you'll have to check how it performs on the database independent of the application. Check indexes and the execution plan to see where bottlenecks may lie.

As for your code, first I'd make sure to scope the query to the function using the local scope. Then you can just serialize the query data to JSON using a built-in function, but how depends on what version of ColdFusion you're using.

<cfscript>
news = queryNew("id,title", "integer,varchar");
queryAddRow(news);
querySetCell(news, "id", "1");
querySetCell(news, "title", "Dewey Defeats Truman");
queryAddRow(news);
querySetCell(news, "id", "2");
querySetCell(news, "title", "Men walk on Moon");

writeDump(serializeJSON(news, "struct"));
</cfscript>

This returns an JSON array of objects using name/value pairs.

[
    {
        "ID": 1,
        "TITLE": "Dewey Defeats Truman"
    },
    {
        "ID": 2,
        "TITLE": "Men walk on Moon"
    }
]

If you alias the database column names, you can easily create the keys indicated in your JSON output. You may have to adjust the code that reads the key names to match the case returned by the function.

<cffunction name="GeTheSerialNumber" access="remote" returnformat="plain" returntype="string" output="no">
    <cfargument name="term" required="yes" />

    <cfsetting requesttimeout="20"/>
    <cfif NOT IsDefined("session.LoggedIn")>
        <cfabort />
    </cfif>

    <cfquery name="local.GetSerialNumber" datasource="#application.datasource#">
        SELECT 
            [serialNum] as label
            , [serialNum] as [value]
            , MissionAsset.MissionAssetID as missionAssetID
            , MissionAssetDescription as missionAssetDescription
            , ESN.EfracusSerialNumberID as ID 
        FROM MissionAsset        
            LEFT JOIN EfracusSerialNumber ESN ON MissionAsset.MissionAssetID = ESN.MissionAssetID
        WHERE MissionAsset.isActive = <cfqueryparam value="1" cfsqltype="cf_sql_bit"> 
            AND serialNum LIKE <cfqueryparam value="%#arguments.term#%" cfsqltype="cf_sql_varchar"> 
            AND MissionAssetStatusID IN (2,3,4,5,6)
        ORDER BY serialNum
    </cfquery>

    <cfreturn serializeJSON(local.GetSerialNumber, "struct")>
</cffunction> 

If you want more fine control, you can try the ArrayCollection.cfc I put together for older versions of ACF.

Answer 2

The question has an accepted answer, which is better than this one. This one will show how to make the code more efficient while maintaining a return type of string in the function.

<cfset var stcReturn = '['>
<cfset var records = GetSerialNumber.recordcount>

<cfloop 
     query="GetSerialNumber"
     StartRow = 1
     EndRow = records - 1>

    <cfset stcReturn &= 
          '{"label":"#serialNum#"
          ,"value":"#serialNum#"
          , "missionAssetID": "#MissionAssetID#"
          , "missionAssetDescription":#MissionAssetDescription#"
          , "ID" : "#EfracusSerialNumberID#"}
          , '>  <!--- note the comma here --->
   
</cfloop>

    <cfset stcReturn &= 
          '{"label":"#GetSerialNumber.serialNum[records]#"
          ,"value":"#GetSerialNumber.serialNum[records]#"
          , "missionAssetID": "#GetSerialNumber.MissionAssetID[records]#"
          , "missionAssetDescription":#GetSerialNumber.MissionAssetDescription[records]#"
          , "ID" : "#GetSerialNumber.EfracusSerialNumberID[records]#"}
          ] '>  <!--- note the square bracket here --->