tracker issue : CF-4085682

select a category, or use search below
(searches all categories and all time range)
Title:

Enhance cfquery or anything that returns queries to return array of structs

| View in Tracker

Status/Resolution/Reason: Needs Review//NeedsApproval

Reporter/Name(from Bugbase): Luis Majano / Luis Majano (Luis Majano)

Created: 11/09/2015

Components: Language

Versions: 11.0

Failure Type: Enhancement Request

Found In Build/Fixed In Build: CF11_Final /

Priority/Frequency: Trivial / Unknown

Locale/System: English / Mac 10 All

Vote Count: 15

Hibernate has a feature that returns array of maps instead of array of objects.  Returning array of maps is consistently faster than even query objects.  I would like to propose the ability for cfquery or any other tag/function that returns queries to return array of structs instead.

I have seen firsthand the performance increase of this data representation. PLease consider it.

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

Watson Bug ID:	4085682

External Customer Info:
External Company:  
External Customer Name: Luis Majano
External Customer Email:  
External Test Config: My Hardware and Environment details:

Attachments:

Comments:

+1 Vote must be between 25 and 4000 characters
Vote by External U.
5377 | November 09, 2015 01:06:21 PM GMT
+1 Vote must be between 25 and 4000 characters
Vote by External U.
5378 | November 09, 2015 01:06:57 PM GMT
+1 (Vote must be between 25 and 4000 characters)
Vote by External U.
5379 | November 09, 2015 01:07:38 PM GMT
+1 (Vote must be between 25 and 4000 characters)
Vote by External U.
5380 | November 09, 2015 01:11:26 PM GMT
+1 Vote must be between 25 and 4000 characters
Vote by External U.
5381 | November 09, 2015 01:13:49 PM GMT
+1 ........................................
Vote by External U.
5382 | November 09, 2015 01:14:57 PM GMT
+1 for cfquery to return array of structs
Vote by External U.
5383 | November 09, 2015 01:47:57 PM GMT
Majority of my queries are converted to structs, then to JSON any way.May as well make it easier.
Vote by External U.
5384 | November 09, 2015 01:52:31 PM GMT
it has always bothered me to get a resultset from the DB to the client typically involves the dataset to be replicated several times in memory. for large resultsets, it becomes extremely slow converting it to JSON, as there are 4 times the resultset is replicated in memory: jdbc resultset -> cfml query object -> array representation -> json representation. the last 3 typically all reside in memory at the same time.
Vote by External U.
5385 | November 09, 2015 02:09:38 PM GMT
+1 Vote must be between 25 and 4000 characters
Vote by External U.
5386 | November 09, 2015 02:26:51 PM GMT
thinking about it more, perhaps would it make more sense to have cfquery/query functions just return just a jdbc resultset wrapper instead that has the ability to convert to the desired end representation? either way you still have a minimum of 2 representations in memory at the same time (if you are needing to convert). most cases there would only be two representations, some cases just the one. the key benefit is that the conversion is done at a different point in execution, and you would have the ability to get multiple representations from the same resultset. Example: rs = queryExecute("select * from table",...); // you can interact with rs as a native JDBC resultset, or use one of the following to get a final representation: records = rs.toRecordArray(); columns = rs.toColumnArray(); json = rs.toJSON("record"); // or "column", default being "record" qry = rs.toCFQuery(); // don't really see this ever being used, but it is a possible option...
Comment by External U.
5371 | November 09, 2015 02:50:31 PM GMT
At World Singles we've switched away from query results completely to arrays of structs and it's not only faster, it's much more convenient since you can then leverage all the normal map / filter / reduce stuff along with regular array and struct functions. And it provides better interop with other JVM languages on the back end and JavaScript on the front end.
Vote by External U.
5387 | November 09, 2015 02:51:49 PM GMT
I would love to see an array of structs as an option for queries.
Vote by External U.
5388 | November 09, 2015 04:02:25 PM GMT
+1111111111111111111111111
Vote by External U.
5389 | November 09, 2015 05:46:11 PM GMT
This would be helpful. There are many cases where I convert queries to this format.
Vote by External U.
5390 | November 10, 2015 10:23:22 AM GMT
Hi Luis, Which are you proposing? 1) For tags/functions to never return a query and always return an array of structs instead -or- 2) For tags/functions to optionally return either a query or an array of structs Thanks!, -Aaron
Comment by External U.
5372 | November 14, 2015 04:01:55 AM GMT
@Aaron, an optional return type. @adobe FYI, this has been implemented already in Lucee 5/4.5: https://luceeserver.atlassian.net/browse/LDEV-612
Comment by External U.
5373 | October 14, 2016 11:28:58 AM GMT
Hi Luis and Adobe, QueryGetRow(query, rowNumber)//returns struct QueryGetRows(query[, startRowNumber, endRowNumber])//returns array of structs QueryGetRow() was added in CF11. If QueryGetRows() is added, then query "myQuery" can be converted to array via myQuery.getRows(). Thanks!, -Aaron
Comment by External U.
5374 | October 14, 2016 04:28:59 PM GMT
+1 for adding QueryGetRows()/q.getRows() to return an array of what QueryGetRow()/q.getRow() currently returns.
Vote by External U.
5391 | October 14, 2016 04:33:09 PM GMT
Even though that capability is there. It defeats the purpose of optimization and performance. I don't want to incur the penalty of transforming the result set into an array of structs. I want to natively get an array of structs or struct of structs. Same goes for methods like entityload, ormexecutequery, queryexecute, etc.
Comment by External U.
5375 | October 14, 2016 05:08:17 PM GMT
Ok, are you finishing reviewing this? Lucee already support this
Comment by Luis M.
5376 | February 21, 2017 12:07:33 AM GMT