Search code examples
coldfusioncoldfusion-9cfmlqoq

ColdFusion 9 - Top n random query results

I've got a series of queries that I do to get 5 results at random, the problem is that it is taking a while to get through them, mostly because it involves a loop to assign a rand value that I can order by (which Railo can do in-query)

I was wondering if anyone has dealt with this and knows of a way of speeding it up.

I'm below 200ms, which isn't bad but I'm sure it can be sped up.

Solution

  • You probably don't need to use QoQ at all.

    One option might be to write your original query as:

    SELECT TOP 5 whatever,you,need
FROM table
ORDER BY rand()

    Update the syntax depending on which database server you're using.

    Another option, which could be done for both regular queries and QoQ, would be:

    1. select only the primary keys
    2. shuffle the array (i.e. createObject("java","java.util.Collections").shuffle(Array))
    3. use the first five items in the array to select the fields you need.

    No looping or updating, just two simple selects.

    Of course if your primary key is just an auto-incrementing integer, you might get away with SELECT MAX(Id) then use RandRange to pick your five items.