topic Re: limit for query where clause in ArcGIS JavaScript Maps SDK Questions

limit for query where clause

NatCarter — Wed, 01 May 2013 21:37:04 GMT

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

Re: limit for query where clause

derekswingley1 — Wed, 01 May 2013 21:56:50 GMT

Do you have a public service we can use to reproduce this?

Re: limit for query where clause

NatCarter — Thu, 02 May 2013 14:46:20 GMT

Do you have a public service we can use to reproduce this?

Please see the attached document for service URLs, example SQL, & mapping service properties.

Re: limit for query where clause

derekswingley1 — Thu, 02 May 2013 16:30:27 GMT

Thanks! I'll investigate and post back here when I figure out what/why this is happening.

Re: limit for query where clause

derekswingley1 — Thu, 02 May 2013 20:07:00 GMT

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.

Re: limit for query where clause

ReneRubalcava — Thu, 02 May 2013 21:28:57 GMT

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.

Re: limit for query where clause

NatCarter — Mon, 06 May 2013 15:37:53 GMT

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.