Speed up Random CFQuery

[Guest]

Hello all,

I have created a Random Product Generator for my Web site which pulls a random product out using cfquery and displays it with an cfoutput query. All works well, except it is extremely slow as it searches through 37,000 records. Does anyone have a faster/better way of pulling a random record for display (on page refresh a new random record is displayed)

Here is my query:

<cfquery name = "items" dataSource = "mydata">
   SELECT NAME,DESCRIPTION
   FROM   product_catalog
   ORDER BY NAME
</cfquery>

Here is my cfoutput query

<cfoutput query="items" startrow="#displayRow#" maxrows="1">

Here is the output from the cfoutput query

#items.NAME#

Thanks!

[Guest]

Almost forgot. I am using an Access DB. Would love to be on SQL, but can't at this time...

... and I am using cfset to pull the random as follows:

<cfset displayRow = randRange(1,items.recordcount)>

[Adam Cameron.]

Almost forgot. I am using an Access DB. Would love to be on SQL, but can't at this time...

You are definitely overloading an Access DB if you've got 37k records in a table that you're querying on a regular basis.  I'd be trying to priotise a port to something else ASAP.

What's causing you to not be in the position to port the app to a proper DB system?

I dunno Access at all, but what you need to do is not drag ALL your records down to CF (or waste Access' meagre processing power fetching them!).  You should be doing your randomisation on the DB, and then just fetch the one record and return it to CF.

Do this sort of thing (all on the DB side of things):

* find out how many records there are in the table

* generate a random number to pick a single record within that range

* query that record and return to CF

As for the syntax... read the docs or Google or ask on an Access forum (or someone else here might know).  Access as a small amount of programmability, as I understand it, but I'm pretty sure it doesn't do stored procs or anything like that.  Still, you might be able to chain a couple of queries together?

--

Adam

[Dan_Bracuk]

google "msaccess order by random".  Plus, msaccess supports "select top n" syntax.

As Adam said, you don't have to bring your entire table into cold fusion.

[BKBK · Accepted solution]

1) Create a new column, product_id, to be used as primary key. This is vital to your design, in any case, now and later.

Make it an Autonumber field. Then you can conveniently use the fact that it is an integer field, as shown below.

2) This should run fast
<cfquery name = "items" dataSource = "mydata">
   SELECT count(*) as numberOfProducts
   FROM   product_catalog
</cfquery>

You can even do better. This query, like your query, assumes that the product catalog changes constantly as the application proceeds. This is usually not the case for most applications.

If your product catalog stays the same during the application, then you should define the following query, preferably in your Application.cfm or Application.cfc:

<cfquery name = "application.items" dataSource = "mydata">
   SELECT count(*) as numberOfProducts
   FROM   product_catalog
</cfquery>

This is much more efficient, as Coldfusion reads it just once throughout the life of the application.

3) Now comes the number-generating line:
<cfset random_id = randRange(1,application.items.numberOfProducts)>

4) Finally, the main query. Quite efficient, as it fetches just one row.

<cfquery name = "getItem" dataSource = "mydata">
   SELECT *
   FROM   product_catalog
   WHERE  product_id = #random_id#
</cfquery>

[Guest]

Thanks BKBK,

That worked perfectly. I knew last night that it was slow because I was essentially pulling down the whole DB instead of handling the randomization in the initial query... but I did not know how, since it is in Access (for now, can't wait to move it to a SQL DB) and commands like RAND did not work.

K.