The CFQUERY connects to the database, returns the whole of the resulting query result into the specified variable, then closes the connection; the variable is held in memory for the lifetime of the request.
So, if you run a query that, as in this case, returns more than 300,000 records, there is going to be a significant overhead as the data is read out of the database and into ColdFusion. Especially if one of those fields is a memo field. I don't think this is unexpected behaviour - you just need to examine what you actually require from the query, and tailor your SQL to that. It is highly unlikely that you actually need to work with 300,000 records at one time... Seb Duggan Web & ColdFusion Developer e: [email protected] t: 07786 333184 w: http://sebduggan.com On 8 Mar 2009, at 22:42, Claude Schneegans wrote: > > Hi, > > I've always thought that all what CFQUERY was doing was to create some > connection to the database, and then the actual content of all records > would be read as needed during some loop on the result set. > > I have this statement on some Access database : > <CFQUERY NAME="getArmes" DATASOURCE="Armoriaux"> > SELECT armeId, armTexte > FROM armesArmoriaux > </CFQUERY> > <CFOUTPUT>getArmes.recordCount = #getArmes.recordCount#<BR> > cfquery.ExecutionTime = #cfquery.ExecutionTime#<BR></ > CFOUTPUT><CFABORT> > > The query semms to take for ever. Actually, the output says: > getArmes.recordCount = 303203 > cfquery.ExecutionTime = 614469 > > More than 10 min. just to settle a result set? That does not make > sense. > Ok, armTexte is a memo field, but is CFQUERY supposed to read all of > them? > > If I run the same query directly from Access, it runs so fast, I > cannot > even measure the time it takes. > > So, what the heck CF is doing within CFQUERY? Read all content? That > does not make sense. > Is the problem with CF, or the ODBC driver? > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:320241 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
