Performance: Non case-sensitive where clause for IQueryFilter

Question

Hi all,

I have a variety of different Shapefiles/SDE Featureclasses/File GDB Featureclasses which I need to perform attribute queries on. I need to develop a way which will ignore the varierty of case types present in these data sources. Currently I have used the following which do work.   pQF.WhereClause = "UPPER(" & pField & ") = '" & pSearchText.ToUpper & "'"

This works but performance is decreased as this ignores field indexing on the data.   pQF.WhereClause = pField & " = '" & pSearchText.ToUpper & "' OR " & pField & " = '" & pSearchText.ToLower & "' OR " & pField & " = '" & StrConv(pSearchText, VbStrConv.ProperCase) & "'"

This also works but again it does not perform well, I guess because it has to search the a number of times for the different cases. My question is: Is there a faster way to implement case insensitivity (I am not able to 'clean' the data)?

Thanks muchly in advance. 
Gino

GinoMellino · Answer

Hi agray1,

The app is designed to allow users to select a layer and a field within that layer to search on. Because it is dynamic like this I cannot hard code the query on a case by case basis as I do not know what the users choice of layers will contain. The best I could do is some sort of eval on the layer prior to this to find out what case the field is in and then set a QF accordingly, however i suspect this will end up being as slow as using the OR statements in the original QF. If the optimisation has to be done on the database side then perhaps the way I am currently doing it will be the quickest.

Thanks for the info.

AlexanderGray · Answer

Optimizing the query to handle case insensitive strings would be a database exercise (Oracle, SqlServer, etc.) ArcObjects and SDE are not really going to help.

If you can't optimize the sql for case insensitivity, why not sanitize the strings the user inputs. If you know one FC's attribute are all caps and the other capitalized the first letter, why not build two queryfilters with slightly different where clauses.  The first where the user's input is changed to all upper case and the second where the same input is changed to proper case.

GinoMellino · Answer

Hello,

Thanks for the reply. I think maybe a litlle misunderstanding :-), ill try and explain better.

Just say, for example, I have 2 different featureclasses each with a string field (TOWN) i wish to query. The first FC has its field data all in uppercase. If the user types in the search text all in upper case (e.g. "TOWN" = 'ABBADOAH') then there is no problem, the query will run fine. If the search text contains lowercase characters (eg. "TOWN" = 'Abbadoah') however the query will not return any records.

Now, I know that I can just convert the search text to uppercase using .ToUpper. This will also work fine on FC 1 and return a record for any case combination as they all evaluate to uppercase. FC 2 (which has to be queryable by the same code) has its field values in propercase. In this situation entering 'Abbadoah' will return false as this will evaluate to "TOWN" = 'ABBADOAH'. Then there are FC's which contain a variety of case combinations also.

What I want is the user to be able to enter the search text as either Abbadoah, ABBADOAH, AbbADoah, aBaaDOAH  or whatever case combination and still return the record. The 2 code snippets I entered previously accomplish this well but are just slow (on my machine result in a change from a near instant return to a wait time of 13-14 seconds on one of the larger datasets for example). I am wondering if there is a faster way to accomplish this? It is probably very simple but I cannot find it 🙂

Thanks again!!