Zufällige Datensätze aus der Datenbank auswählen

Mischa Sameli, Geschäftsführer & Leiter Entwicklung

Immer wieder einmal muss eine bestimmte Anzahl Elemente zufällig aus einem grossen Recordset ausgelesen werden. Mit ColdFusion kein Problem, mit reinem SQL aber bedeutend effizienter, wie ich bei einem Code-Refactoring festgestellt habe.

Die Aufgabenstellung ist simpel: Aus einer grossen Artikel-Datenbank sollen zufällig x beliebige Artikel mit einem speziellen Attribut ausgewählt und auf einer Überssichtsseite angezeigt werden. Dazu hat der Programmierer folgenden Ablauf vorgesehen:

  1. alle Datensätze mit dem entsprechenden Attribut auslesen
  2. Eine zufällige Liste mit maximal x Einträgen generieren aufgrund der gefundenens IDs aus der ersten Datenbankabfrage
  3. Aus dem ersten RecordSet Artikel mit den IDs auslesen aus der soeben zusammengestellten Liste.

Der dafür benötigte Code sieht dann in etwa so aus:

1<cffunction name="getRandomArticles" returntype="query" access="public" output="false">
2    <cfargument name="anzahl" type="numeric" required="false" default="4" />
3    <cfset var local = StructNew() />
4
5 <cfquery name="local.query" datasource="#datasource#">
6    SELECT * FROM artikel
7 WHERE highlight=<cfqueryparam cfsqltype="cf_sql_bit" value="true" />
8    </cfquery>
9
10    <cfset local.TopSeller.selected = getRandomIDs(valueList(local.query.id),arguments.anzahl) />
11
12    <cfquery name="local.query" dbtype="query">
13        SELECT * FROM [local].[query]
14 WHERE ID IN('#replace(local.TopSeller.selected,",","','","all")#')
15    </cfquery>
16    
17    <cfreturn local.query />
18</cffunction>
19
20<cffunction name="getRandomIDs" returntype="string" access="private" output="false">
21    <cfargument name="IDs"        type="string"     required="true" />
22    <cfargument name="anzahl"    type="numeric"    required="true" />
23    <cfset var local = structNew() />
24    <cfset local.selected = "" />
25    <cfloop condition="listLen(local.selected) LT (iif(listLen(arguments.IDs) LT arguments.anzahl,de(listLen(arguments.IDs)),de(arguments.anzahl)))">
26        <cfset local.value = listGetAt(arguments.IDs,randRange(1,listLen(arguments.IDs))) />
27        <cfif NOT listFindNocase(local.selected,local.value)>
28            <cfset local.selected = listAppend(local.selected,local.value) />
29        </cfif>
30    </cfloop>
31
32    <cfreturn local.selected />
33</cffunction>

Der Code funktioniert, keine Frage, verbraucht aber mit zunehmender Datenmenge auch immer mehr Performance. Was nicht sein müsste, denn was die DB kann besorgen, bereitet CF keine Sorgen. Und so überlässt die Funktion getRandomArticles() nun die Zufalls-Selektion nun direkt der Datenbank:

1<cffunction name="getRandomArticles" returntype="query" access="public" output="false">
2    <cfargument name="anzahl"    type="numeric" required="false" default="4" />
3    <cfset var qryGet = "" />
4
5 <cfquery name="qryGet" datasource="#datasource#">
6 SELECT TOP #arguments.anzahl# *
7 FROM artikel
8 WHERE highlight=<cfqueryparam cfsqltype="cf_sql_bit" value="true" />
9 ORDER BY newid()
10 </cfquery>
11    <cfreturn qryGet />
12</cffunction>

Die obige Syntax funktioniert im Zusammenhang mit einer MSSQL-Datenbank. Für MySQL und Postgre funktionieren diese beiden Varianten:

1<!--- MySQL --->
2<cfquery name="qryGet" datasource="#datasource#">
3 SELECT *
4 FROM artikel
5 WHERE highlight=<cfqueryparam cfsqltype="cf_sql_bit" value="true" />
6 ORDER BY RAND()
7 LIMIT #arguments.anzahl#
8</cfquery>
9<!--- PostgreSQL --->
10<cfquery name="qryGet" datasource="#datasource#">
11 SELECT *
12 FROM artikel
13 WHERE highlight=<cfqueryparam cfsqltype="cf_sql_bit" value="true" />
14 ORDER BY RANDOM()
15 LIMIT #arguments.anzahl#
16</cfquery>