http://coldfusion.com logo
#cfml-general
Title
# cfml-general
w

websolete

05/23/2022, 7:12 PM
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'
a

Adam Cameron

05/23/2022, 7:15 PM
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.
w

websolete

05/23/2022, 7:18 PM
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
a

Adam Cameron

05/23/2022, 7:20 PM
That is my understanding as well.
✔️ 2
The difference in behaviour only becomes relevant when there is more than one row.
e

Evil Ware

05/23/2022, 11:46 PM
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.
s

seancorfield

05/23/2022, 11:55 PM
@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.
✔️ 2
e

Evil Ware

05/25/2022, 10:27 PM
@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.
a

Adam Cameron

05/25/2022, 10:29 PM
🍿
s

seancorfield

05/25/2022, 10:31 PM
@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.
e

Evil Ware

05/25/2022, 10:41 PM
You are assuming that the db query is against a trillion records and not a view.
s

seancorfield

05/25/2022, 10:48 PM
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.
https://www.bennadel.com/blog/464-coldfusion-cfquery-maxrows-not-a-bad-compromise-when-top-not-available.htm -- "When I "select *" from a database that has 50,000+ records, using the TOP directive executed in about 30-40 ms. MaxRows returned the same record set in around 125-140 ms. This is about 3-4 times slower..."
50,000 rows in a table is tiny. We have quite a few tables in our system that have 200,000,000 rows (yes, 200 million). Not using the database-appropriate limiting SQL sequence there could be pretty bad...
e

Evil Ware

05/25/2022, 10:52 PM
thats what QoQ is for 😄
s

seancorfield

05/25/2022, 10:53 PM
QoQ means you already have all those rows locally in CF's memory 🙂
e

Evil Ware

05/25/2022, 10:55 PM
Its just memory, Dell and HP are happy to sell complete data centers full of servers with petabytes of memory per box.
Its Gov work. When it doubt throw more money at it. So hell ya, max rows =1 on a table with a trillion records. who cares if it takes a minute to compute 😉
w

websolete

05/25/2022, 11:19 PM
can't tell if you're being serious or not
a

Adam Cameron

05/25/2022, 11:38 PM
w

websolete

05/26/2022, 12:03 AM
still can't tell if he's being serious or not
11 Views