tracker issue : CF-3114283

Problem:

CODE A:

<cfquery datasource="#dsnName#" name="INSTITUTION_LIST">
SELECT INSTITUTION_ID, INSTITUTION_NAME, INSTITUTION_CITY, COUNTRY_ID, INSTITUTION_DEGREES,
INSTITUTION_START_DATE, INSTITUTION_END_DATE, INSTITUTION_DATE_MODIFIED
FROM INSTITUTION
WHERE CODER_ID = <cfqueryparam cfsqltype="CF_SQL_NUMERIC" value="#myMemberID#">
</cfquery>

<cfquery dbtype="query" name="INSTITUTION_LIST">
SELECT *
FROM INSTITUTION_LIST
ORDER BY INSTITUTION_DATE_MODIFIED DESC
</cfquery>

CODE B:

<cfquery datasource="#dsnName#" name="INSTITUTION_LIST">
SELECT INSTITUTION_ID, INSTITUTION_NAME, INSTITUTION_CITY, COUNTRY_ID, INSTITUTION_DEGREES,
INSTITUTION_START_DATE, INSTITUTION_END_DATE, INSTITUTION_DATE_MODIFIED
FROM INSTITUTION
WHERE CODER_ID = <cfqueryparam cfsqltype="CF_SQL_NUMERIC" value="#myMemberID#">
ORDER BY INSTITUTION_DATE_MODIFIED DESC
</cfquery>

CODE A runs much faster than CODE B. With CODE B, It appears coldfusion tries to order all rows with the order by clause before it filters the results. However, CODE A forces coldfusion to order the results after the filtration has been done. The difference in speed between these two pieces of code can be 15 seconds with millions of records in a table.

Thus, I suggest coldfusion filters the query results before it tries to order them.
Method:


Result:

----------------------------- Additional Watson Details -----------------------------

Watson Bug ID:	3114283

External Customer Info:
External Company:  
External Customer Name: Travis Walters
External Customer Email: 163148AE447DEBE799201549
External Test Config: 01/30/2012

Attachments:

Comments: