are there any negative implications to a cfquery where maxrows=1 is stipulated as an attribute but the query itself is a

SELECT TOP 1 ...

(sql server)? clearly maxrows is superfluous, but i wonder if there are additional implications i should include in calling it out in a code review beyond 'this is redundant'

Risky cos someone might think "don't need the

TOP 1

cos we're row-limiting in the `<cfquery>`", and get rid of the

TOP 1

which pull push more work than necessary into the DB. Other than that... not that I can think of.

was looking more from a technical slant. my understanding is maxrows allows the query to execute normally and THEN returns only the specified number of rows. given that, i would assume it would have NO appreciable effect on how the query is evaluated or executed by the db, so it literally would have no effect if the query itself has a SELECT TOP or say was doing a WHERE filter on a unique identifier

That is my understanding as well.

MS SQL works with SELECT TOP X, where MySQL its LIMIT X, and Oracle is fetch first X, , Coldfusion does the heavy lifting if you leave your statements as generic as possible.

@Evil Ware Are you suggesting relying on

maxrows

in a

cfquery

tag to magically change the SQL being executed? It doesn't work that way -- and you do not want to run a query that returns thousands of rows to CF, just to have CF throw away all but

maxrows

of that result. That's very inefficient.

@seancorfield Why would I not use MaxRows? Let's forget that there are views that are ultra-efficient at data retrieval, the simple fact that today's servers running even on CHEAP hosting are hundreds of times better than what We had at the start of the Coldfusion. Never mind that we have databases that can query millions of records in fractions of a second or that Java, no matter how far it has advanced runs only at roughly seventy percent of the processor's capacity. So yes, why would I want to spend hours upon hours rewriting a generic query for every language out there when that completely detracts from the sole purpose of Coldfusion. Optimize the Database, not the query.

🍿

@Evil Ware Because you don't want your ultra-efficient database to return one million rows across the network and into CF's memory, just to have CF itself ignore 999,999 rows and throw that data away again.

You are assuming that the db query is against a trillion records and not a view.

Whether it's a view or not isn't relevant. The SQL query is going to return as many rows as the query matches. All of that data is going to come back over the wire and into CF's memory. And then it's going to throw away the rows it didn't need. Now, with some databases and some drivers (and depending on the internal configuration CF sets on the JDBC driver), it may "stream" very large results in chunks, and if you close out the JDBC

ResultSet

before you've pulled all the rows out of it, that can prevent the streaming of additional chunks. But those can still be pretty large.

thats what QoQ is for 😄

QoQ means you already have all those rows locally in CF's memory 🙂

Its just memory, Dell and HP are happy to sell complete data centers full of servers with petabytes of memory per box.

can't tell if you're being serious or not

still can't tell if he's being serious or not