[thelist] ColdFusion Tip

> Use the 'maxrows' attribute in your cfquery tag set to 1; it will speed
> the query up (database stops searching after it finds the first match)

hi seth

i'm not a cf internals guy, so i don't know what kind of logic is built
into the odbc or native drivers that lets cf limit the number of rows it
returns to your cfquery

but i'll bet that it just limits the results coming back from the database,
which, the way i understand it anyway, has already executed the sql and
constructed the entire result set in its (the database's) buffers

here's my understanding of the communication between the cf server (a great
big whack of code) and the database server (another great big whack of
code, often on another machine)

cf says "get me these records" and passes over your sql

now the database first has to execute your sql in order to determine if
there are *any* records at all that qualify, never mind just one

okay, so the database now has its buffers full of the *entire* result set
of your sql, so it starts shipping blocks of results back to cf

database says "here's some records" and then "here's some more" and then
"here's some more" and so on -- and it doesn't stop until it's sent the
last block

the part that wouldn't surprise me is if there's some option in the
communication between cf and the database that lets cf say "okay, you can
stop now" before the last block

if there is no way for cf to stop the database from sending all the result
blocks, then i suppose cf just ignores the ones it doesn't want -- less
efficient, but gets the job done, probably doesn't fill up any cf buffers
needlessly, etc.

note that in both cases the database doesn't "stop searching" -- it's
already done its full search the moment it was given your sql to execute!

nevertheless, i think you're right seth, using maxrows has to be more
efficient than not using it

;o)

any real cf internals folks out there can refute/verify any of this?  did
any of this make sense?


<tip>

maxrows notwithstanding, it's *gotta* be more efficient to tell the
*database* to limit the number of rows you want -- in microsoft access, you
have the TOP keyword that you can use for this purpose, described as
follows (excerpt from msaccess help file) --

  TOP returns a certain number of records that fall at the
  top or the bottom of a range specified by an ORDER BY
  clause. Suppose you want the names of the top 25
  students from the class of 1994:

       SELECT TOP 25 FirstName, LastName
         FROM Students
        WHERE GraduationYear = 1994
     ORDER BY GradePointAverage DESC;

  If you don't include the ORDER BY clause, the query
  will return an arbitrary set of 25 records from the
  Students table that satisfy the WHERE clause.

  The TOP predicate doesn't choose between equal values.
  In the preceding example, if the twenty-fifth and
  twenty-sixth highest grade point averages are the same,
  the query will return 26 records.

</tip>

_____________
rudy limeback
http://r937.com/
http://evolt.org/