Query caching to power jQuery autocomplete

Question

I'm working on an application that requires an autocomplete field on a search form. The application is CFML on Railo 3.3. I'm using jQuery UI autocomplete and have implemented the lookup on the server-side like this:

private struct function getStationDetails(required numeric uic)
{
    var qryCacheStations = new query();
    var qryStations = new query();
    var cacheData = "";
    var resultData = "";
    var stcResult = {};

    qryCacheStations.setDatasource(variables.instance['dataSource']);       
    qryCacheStations.setSQL("select distinct uic, name, crs from stations order by name");
    qryCacheStations.setCachedwithin(createTimeSpan(1,0,0,0));
    cacheData = qryCacheStations.execute().getResult();

    qryStations.setDBType("query");
    qryStations.setAttributes(srcTbl = cacheData);  
    qryStations.setSQL("select name, crs from srcTbl where uic = :uic");

    qryStations.addParam(name="uic",value=arguments.uic,CFSQLType="CF_SQL_INTEGER");

    resultData = qryStations.execute().getResult();

    stcResult = {
        name = resultData['name'][1],
        crs = resultData['crs'][1]
    }

    return stcResult;
}

Basically I'm loading the entire station list into the cache on the first lookup, with a 1 day expiry (the data rarely changes), then using a query of queries to return the relevant results back to the client.

My question is simply this; is this agressive caching and QoQs technique a good pattern? Performance seems good and the memory footprint is reasonable (the data set is quite small), so it 'feels' okay but I'm looking for any advice from those who have perhaps tried this before and discovered problems?

Any thoughts would be very much appreciated.

Addiction

I think you nailed it :). I use this all of the time. – Oxtail

does Railo have cachePut() and cacheGet()? might be more flexible then Cachedwithin when it's time to flush the cache – Nucleo

@Nucleo - it does indeed have cachePut() and cacheGet(). I'm using those caching functions with EHCache for other types of data but the query cache is great for straight DB caching. Also, Railo supports the <cfobjectcache/> tag which allows you to flush the query cache if required. – Addiction

Answer 1

you can use the Jquery auto complete

cacheLength: number

to set the caching enable at client side so number of calls to server can be reduced and performance can be improved

Pericarp

Answer 2

I used this exact same technique with great success.

You are definitely doing the right thing, assuming that your data doesn't grow unexpectedly.

Hildegard