The FilterExpression property in MapObjects is meant to store a piece of ANSI SQL that will be used to set up a definition query on a layer. What this means is that the layer will consist of only those features which satisfy the FilterExpression.
As per the documentation, your expression should be that syntax which would normally follow the word “WHERE” in an SQL query, such as: “POP > 2000”
If no features satisfy the expression, none are returned and the layer is not drawn. To demonstrate this, set the FilterExpression to “1 = 0” and you’ll get nothing back.
The values you are storing in the FilterExpression property are not valid statements and, therefore, they evaluate to FALSE, causing no features to be returned.
First, a clarification, SDE does not store shapefiles, you can load a shapefile into SDE, but the data is now stored in SDE, and will benefit from spatial and attribute indexes created in by SDE and the RDBMS.
Second, think of filter expression as analogous to a definition query in ArcView. You use search expression to search for features within a layer (or the subset of a layer if a filter expression is already applied).
Third, setting the filter order to moAttributeFirst may or may not improve your performance, this is directly related to the data you are viewing, and the extent at which you are viewing it. In some cases this will speed up your data retrievals, in others it will drastically slow them down. If you are not sure about your data, leave the default setting. If you are sure that the current extent of your data is such that a much smaller subset will be returned by the atttribute query than is contained within the spatial extent then setting moAttribute first will speed your queries. This setting works against you when you are zoomed into a small area where only a few records would be returned by the spatial extent, but many are returned by the attribute query.
Lastly, if you are going to be using filter expressions and or attribute queries against your sde data be sure to have the appropriate column(s) indexed within your RDBMS. The indexes will (in general) greatly enhance your query response times.
Use filterexpression only to limit the features you want to see in your display or perform a searchexpression on.
To highlight features, use searchexpression and then use DrawShape in the AfterLayerDraw event to display it.
SearchExpression honors the FilterExpression so you cannot select a feature from your layer that doesn’t apply to the FilterExpression as well. (This makes your query shorter).