WHERE "IN" clause limits?

1183
3
Jump to solution
02-14-2019 07:06 AM
JamesCrandall
MVP Frequent Contributor

Hi all,

Is there a set number of values that can be contained in a "IN" clause when performing REST queries of a map service?

ArcGIS Server v10.4

RDBMS: Sql Server

0 Kudos
1 Solution

Accepted Solutions
RobertScheitlin__GISP
MVP Emeritus

James,

  In some of my research I have found that people have been able to do 30k and even 50k in an IN. But just because you can does not mean you should. You should always create data that can be query by some other attribute to get the results you are wanting without using a IN query with 10s of thousands of values.

Yes as far as I know it will only be a database limit, assuming you have a proxy to get you past the network query string limit.

View solution in original post

3 Replies
JamesCrandall
MVP Frequent Contributor

Thanks, Robert! I ran into that during research... 

Am I correct to focus on "65,536 * Network packet size" as the limit?  I'm unsure how to quantify this with a given set length of each value that may participate in the IN statement. 

Edit: Looks like [65,536 * Network packet size] = (about 256 MB) ref HERE

Can I assume the limit is RDBMS-specific and nothing applied to the map service itself?

0 Kudos
RobertScheitlin__GISP
MVP Emeritus

James,

  In some of my research I have found that people have been able to do 30k and even 50k in an IN. But just because you can does not mean you should. You should always create data that can be query by some other attribute to get the results you are wanting without using a IN query with 10s of thousands of values.

Yes as far as I know it will only be a database limit, assuming you have a proxy to get you past the network query string limit.