Very complex question.
We have data published to ArcGIS Server as a map service (5 mil polygons in one filegeodatabase).
We need to filter it by fields.
The current structure contains fields that have pipe separated values, example:
AreaID AreaName ClientsNames
5 GreenPoly John Smith|Steve White|David Black
Performance is slow because we use "like"for this field "ClientsNames" in a layer definition to filter the layer.
layerDefinition = "ClientsNames like '%John Smith%' OR ClientsNames like '%Steve White%'";
What is possible improvement of performance:
1) restructure the data
2) use relationship class (if possible for file geodatabase on ArcGIS Server)