[thelist] Coldfusion/SQL:queries:maxrows

Raymond Camden jedimaster at macromedia.com
Mon Sep 17 07:52:48 CDT 2001


FYI, speaking of CF queries, are people here using BLOCKFACTOR any? It's
a _very_ easy way to speed up query time, and, unfortunately, is not
documented well at all.

Basically (and anyone feel free to correct me on this), when CF performs
a query, it only fetches one row at a time. So, a query that returns 200
rows will result in 200 fetches. CF can be told to get more at a time by
using BLOCKFACTOR. BLOCKFACTOR is a number between 1 and 100. The
default is 1.

Ok, so here is where things get interesting. Why don't you just set
blockfactor to 100? Because CF, by default, can only get 32k of data at
one time. So, if 100 rows > 32k, CF has to 'back down' a bit in order to
fetch the right amount of data. What someone told me, was to simply take
the average row size and determine how many would fit into 32k.

On a few, VERY unscientific tests, I've seen _dramatic_ speed increases,
although it's not something you can always apply.

=======================================================================
Raymond Camden, Principal Spectra Compliance Engineer for Macromedia

Email    : jedimaster at macromedia.com
Yahoo IM : morpheus

"My ally is the Force, and a powerful ally it is." - Yoda 

> -----Original Message-----
> From: thelist-admin at lists.evolt.org 
> [mailto:thelist-admin at lists.evolt.org] On Behalf Of Bimal Shah
> Sent: Monday, September 17, 2001 7:37 AM
> To: thelist at lists.evolt.org
> Subject: [thelist] Coldfusion/SQL:queries:maxrows
> 
> 
> Hi,
> 
> Is there any advantage to using SQL to return
> a set number of results compared to using the
> maxrows parameter of a cf query?
> 
> I am trying to query a table, only wishing to
> return the first match e.g
> 
> <cfquery name="getLastDate" maxrows="1"...
> SELECT
> 	thedate
> FROM
> 	testTable
> WHERE
> 	thedate<#today#
> ORDER BY
> 	thedate DESC
> 





More information about the thelist mailing list