Does anyone have an opinion on the fastest/most elegant way to turn a query into a CSV file while maintaining column order?

love that library but need to return directly from an api at scale

well you could use queryEach/queryMap using async=true and just comma separate the values based on the columnList

that is kind of where I was headed. Something like this

var snort = qb
	.setReturnFormat( "query" )
	.from( "accounts a" )
	.join( "accounts_holdings h", "a.id", "h.accountID" )
	.when( rc.keyExists( "accountID" ), ( q ) => {
		q.andWhere( "a.id", rc.accountID );
	} )
	.when( rc.keyExists( "bTaxable" ), ( q ) => {
		q.andWhere( "a.bTaxable", rc.bTaxable );
	} )
	.selectRaw( "a.portfolioCode,ticker,format(purchaseDate,'yyyy-MM-dd'),costbasis,lotID,'TRUE','TRUE',0" )
	.get()
var cols = snort.columnArray()
snort    = snort.reduce( ( o, row ) => {
	return o & cols
		.map( ( col, i ) => {
			return row[ col ]
		} )
		.toList() & chr( 10 );
}, "" )

sort of, thought the more performant would be map the rows into strings in parallel. and then use ArrayToList to join them all together at the end

that's smart

but need to return directly from an api at scale

What does that mean?

that isn't a udf - it's a spreadsheet manipulator. Not fast enough. I have dozens of containers pulling a ton of financial data and need to keep the data access part as fast as possible.

the beauty of the arrayToList in this example is that the string building is much more performant

var columns = ["id","title"];
    csvData = sortedNews.map((row) =>{
        var rowData = [];
        columns.each((colName) => {
            rowData.append(row[colName]);
        })
        return rowData.toList(",")
    },true,10);
    dump(arrayToList(csvData,"#chr(10)##chr(13)#"))

yeah interesting. why not a nested map instead of the for loop?

no reason actually, that would be fine

Almost instant.

var cols = [
      "portfolioCode",
      "ticker",
      "purchaseDate",
      "costbasis",
      "lotID",
      "isHeldShort",
      "isForceHold",
      "penalty"
    ]
    snort = snort
      .map( ( row ) => {
        return cols
          .map( ( col, i ) => {
            return row[ col ]
          } )
          .toList()
      }, true )
      .toList( chr( 10 ) );

but the rows it will definitely help, and the

10

is a placeholder for the number of parallel processes so set as necessary for your use case

yep

Have you considered that data can have commas, quotes, newlines etc? I'd perhaps look to use the Apache Commons CSV lib rather than trying to roll yer own. No point reinventing the wheel. https://www.baeldung.com/apache-commons-csv#creating-a-csv-file

In this case, it won't but I hear you. also love a challenge 😉 25ms to convert 830 rows including 3ms for the query

Might we worth ditching the braces and returns? All of those callbacks look like they're single expressions? Not sure if it would make it more or less readable though.

you're right... habit

I find it's 50/50 if it makes it more readable 😉 You seem to have a stray

i

yer not using in that innermost

map

, too.

also a good point hehe

I wonder... given you are converting a query to a string... whether

reduce

is more idiomatic. Although if performance is a consideration, all the accumulated string building might be slow.

it is. 19 seconds for reduce, 6 seconds for threaded map

The `toList`calls are fine & clear though. And probably deal with the delimiting better anyhow.

snort.map( ( row ) => cols.map( ( col, i ) => row[ col ] ).toList(), true ).toList( "<br>" );

vs

snort.reduce( ( o, row ) => o & cols.map( ( col ) => row[ col ], true ).toList() & "<br>", "" )

that is odd

It might be worth a try, but I'm not so good with java 😕

Heh "you like a challenge" I believe someone said

you might surprise yourself...

lol tru dat

I reckon the Lucee parallelisation would probably be helping here though.

yeah, but you're Adam Cameron 😉

No need to call me a twat mate

hehe

It's piqued my interest now anyhow. Something to look at another day perhaps.

well, if you end up trying it, let me know how it works out

But for now... 23:48 says "zzzzzzzzz" to me. Good luck.

does the 6s include the query time?

yep

can you cache it and see what the actual loop time is?

Don't forget to load test it properly if performance matters

or time it separately to see what actual performance is

phew - 3919ms

Single request testing won't really be telling you much about real-world performance here.

agreed

@Adam Cameron is talking in his sleep

FINE. Ciao.

lol

now that would be interesting

perfect

Just a late footnote: https://github.com/cfsimplicity/spreadsheet-cfml/wiki/queryToCsv uses the Apache Commons CSV java library under the hood.

You're right, Scott, I'm having to convert the cfml query into an array and sanitize it before I can pass it to the commons-csv lib. As an experiment I've just tried changing the for-loops to parallel and it does speed up quite a bit (about 4x on a 1K row query). But I think that's Lucee only for now and I have to support ACF 2016.

nice! i was just about to mention that would be the only suggestion for a speed improvement.