ColdFusion 9 - Top n random query results

Question

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.

You're not clear on the process you're currently using - is it a requirement to do this with QoQ? — Peter Boughton, May 18 '13 at 20:14
It's meant as a subset of a query, but if the result is faster, I'll take it — Daniel, May 18 '13 at 22:52

Peter Boughton · Accepted Answer · 2013-05-18T22:59:49.483

3

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:

select only the primary keys
shuffle the array (i.e. createObject("java","java.util.Collections").shuffle(Array))
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.

edited May 18 '13 at 22:59

answered May 18 '13 at 20:21

Peter Boughton

110,170
32
120
176

Hey, thanks for that, I totally forgot to mention my db type. I'm using MS SQL, thus the `top` – Daniel May 18 '13 at 22:46
Ok, you might also want `ORDER BY newid()` for MS SQL, and possibly [this answer](http://stackoverflow.com/questions/94906/how-do-i-return-random-numbers-as-a-column-in-sql-server-2005#493703) is relevant. – Peter Boughton May 18 '13 at 23:13

score 2 · Answer 2 · answered May 19 '13 at 22:55

2

For Microsoft SQL Server (v2005+) this query syntax will get 5 random records:

SELECT TOP 5 *
FROM table
ORDER BY NEWID()

answered May 19 '13 at 22:55

Scott Jibben

2,229
1
14
22

score 1 · Answer 3 · answered Apr 07 '15 at 10:30

1

I'm on Railo (ColdFusion 9) and neither TOP nor NEWID() works in a Query of Query (QoQ). If you happen to fall into this use case, and you must act upon a QoQ, then here's a solution:

<cfquery name="randomizedQueryObject" dbtype="query" maxrows="10">
SELECT *, RAND() as rand
FROM someQueryObject
ORDER BY rand
</cfquery>

This returns 10 random items from a larger result set and works in a QoQ. Short and simple.

answered Apr 07 '15 at 10:30

thdoan

18,421
1
62
57

It doesn't work in a QoQ. It says problem encountered with RAND() – volume one May 10 '15 at 10:02
Hmm... it is working fine for me. Maybe RAND() is working because I'm on Railo? Can't be sure. Even if RAND() isn't working for you, try to generate a random number some other way and put it in the custom 'rand' column. – thdoan May 10 '15 at 10:56
Yes Railo allows it, but ACF doesn't in a QoQ :( – volume one May 10 '15 at 20:37

ColdFusion 9 - Top n random query results

3 Answers3