anyone ever run into the max limit of the number of list items (numeric) in a queryparam list="true" for sql server? docs suggest a hard limit of 64K items in an IN clause, but i'm more concerned about cf's handling of the bind parameters if i was to pass in a 10,000 item list to a single queryparam

Definitely, it's a thing.

Yes, I've run into this

Limit has changed over the years, used to be 32K 2100 items at one point.

If you need, you can manually build that portion of the raw SQL so long as you carefully escape the values

you're suggesting i forego the queryparam in favor of raw sql to avoid 'issues'? i can do that, this is just a one-off maintenance routine i'm running that won't be run again, but seems like there would be marginal performance gains, if any

It depends on which max you're hitting

i'm not hitting any max so far, my list size is capped at 1000, but i have millions of rows to process, so if i can get away with 10,000 so much the better

Are you doing some sort of bulk import here?

reading from a large table and then inserting a selected portion of those into another table

That's the sort of thing I'd do in a stored proc using temp tables unless you specifically need CFML involved 🙂

well, the advantage with having this relatively simple sql in a cf template is i can control the batch size (as i said, its 1000 currently), and then just meta refresh the page to itself, which will commit the transaction and prevent the log file from blowing up without a fair amount of tsql code wrapping it, were i to do it in a sp

When I used to work with Oracle and do data warehousing, many moons ago, to get around the "IN limit" we would use a subquery in place of the list - In (select whatever to make the list) - And to make the subquery perform better we would normalize the list data into another table - and use the cfqueryparam in the subquery

Yup, The very unorthodox method We currently employ for a stupidly large dataset is to chunk up the SQL query into max "chunks" IE, limit 5000, and then build the queries as files and bang the files off in rapid succession to a "TEMP" database for reports. It's faster to have CF build the script to dump the data and then hand off and fire off another script to inload the data, than trying to have ColdFusion query the data we need, and the plunk it into a temp database, then fire off a report. The end result is 100x faster reports, and for a record set that spans a large and vast quantity of data, speed is everything.

I am actually battling with the same thing at the mo, limit 2100 and that includes a single list param where each item in counted. as @bdw429s you can simply construct the IN clause you just need to guard against potential threats In my case it is bunch of integers and the performance is not critical so I am splitting into several INs an ORing together