[Reactor For CF] maxrows and limit

[Chris Blackwell]

I’ve just come across an issue with using gateways when working with large record sets.  I couldn’t figure out why this bit of code was executing so slowly.   customerGateway = reactor.createGateway("customer"); query = customerGateway.createQuery(); query.returnField("customer", "ext_name"); query.returnField("customer", "ext_id"); query.setMaxRows(10); qCustomers = customerGateway.getByQuery(query);   My customers table has about 13,000 rows, but I only want ten so this should be very fast.  I had an idea what was going on so I ran a little test   <cfquery name="q" datasource="mydsn"> SELECT * FROM customer LIMIT 10 </cfquery>   CF debugging reports that this query takes 0ms, and the page executes in under 50ms   <cfquery name="q" datasource="mydsn" maxrows="10"> SELECT * FROM customer </cfquery>   With this one, debugging still reports an execution time of 0ms for the query but the page now takes 10seconds+ to execute.  So it seems that CF is actually asking the DB for every row in the table then only returning the first 10 rows in the query.  This may be common knowledge, but as I’ve never used the maxrows attribute directly, always preferring a LIMIT clause in the SQL I’ve never run into it.   I’m running MySQL 5, with the CF’s built in JDBC driver, it may be that this doesn’t happen with other drivers, but I’m sure there are enough people out there using this setup that this issue should be addressed.    I only develop on MySQL and have very limited knowledge of other DB’s but is ‘LIMIT n’ not supported by every database?    Chris -- Reactor for ColdFusion Mailing List -- reactor@doughughes.net -- Archives at http://www.mail-archive.com/reactor%40doughughes.net/