The mechanics of CFQUERY

[jdeline]

I inherited a query (shown below) that is going against a DB2 table with over
600,000 rows. Note there is no WHERE clause. I'm puzzled as to what the
author intended, but my curent question is this: Does ColdFusion tell DB2 to
return only 1 row? If so, which one? If not, does the query retrieve A, B
and C from all 600,000+ rows?



<cfquery dbtype="query" name="MFC2" maxrows="1">
SELECT A, B, C FROM orderTable
</cfquery>

[mxstu]

From what I understand, using maxrows sends the statement to the database as
is. So the database selects all 600K records from the table. ColdFusion then
reads the results until it reaches the specified number of rows (in this case 1
record). You're right in thinking that using the database to limit the
records would be more efficient. Like ....

SELECT TOP 1 A, B, C FROM orderTable

... or ...

SELECT A, B, C FROM orderTable LIMIT 1

The database would then only select and return (1) record.

[jdeline]

I looked at that query again, and duh!, it is not using a datasource, it has
dbtype="query". The 600,000+ record had previously been SELECTed by a query
named MFC2 that did have a WHERE clause. So the query in question is not
retrieving the world after all.

There remains the question: What was the author trying to do by running a
query on an existing recordset?

[mxstu]

Yes, I missed the "dbtype" as well. I guess they are using maxrows because
QofQ has no "TOP" or "LIMIT" operator, afaik.

What are they using the query for? Perhaps that will give you an idea why the
only want (1) row?

[jdeline]

> What are they using the query for? Perhaps that will give you an idea why they only want (1) row?

I'm looking into that today. Thanks for your help.

[mxstu]

No problem. Good Luck!

[jmich]

if you wish DB2 to return N rows, where N is an integer
use this type of SQL and don't use the maxrow option of the cfquery tag

select * from YOUR_TABLE
where YOUR_CONDITION
order by YOUR_CHOICE
Fetch first N rows only

(note that if you only 1 row you use row or rows in the fetch line)