limit for query where clause

Question

I have found that there is a limit to the count of items in the WHERE param of a query operation against a mapping and/or feature service.  This is not the returned limit param but the WHERE param of the query itself before it will generate a response.  The limit i have found is 1,000 items.  It does not matter if they are string or integer items or how long the items are; ie not a character limit but a "word" limit. I have tested increasing the Maximum Number Of Records to Return by Server param to a couple of thousand but the query operation returns a code 400 - "unable to complete query".

All postings and documentation I can find say nothing about a WHERE item limit.

I am using ArcGIS Server v10.04.

Please advise,

Nat Carter
Montana DEQ

NatC42 · Answer

Thanks, that was it.  Another solution is to switch out the data sources for the service to an FGDB and there are not any query request errors or limitations other that the MaxRecordReturn of the service itself.

Thanks for the quick help.

ReneRubalcava · Answer

No kidding. Haha, that helps me out a lot. I've been going back and forth with the Server admin tweaking stuff to figure out why all our queries seem to cap out at 1000. For crying out loud.

We were able to work around this by tweaking our queries to look like
ID IN (1, 2, ..., 999) OR ID IN (1000, 1001, ..., n)

It worked in my case.

derekswingley1 · Answer

Is your DB Oracle? From what I can find, this is an Oracle limitation:A comma-delimited list of expressions can contain no more than 1000 expressions. A comma-delimited list of sets of expressions can contain any number of sets, but each set can contain no more than 1000 expressions.

Source:  http://docs.oracle.com/cd/B19306_01/server.102/b14200/expressions014.htm#i1033664

If you must query 1000+ features, I'd do it over multiple queries and use a deferred list to manage execution.