|
Hi Samuel,
That’s certainly a possibility we’ve talked about, and for sure it would improve the performance vs. my solution. The tradeoffs to consider are that we have many columns across many tables where this is used, with 100’s of millions of records,
and so each new index is a huge chunk of storage space. I think in the end, though, we should probably do as you suggest, at least for a selected subset of those columns.
As a general case of just optimizing the qualifier though, having the 4 index lookups, with the merge, is in practice much faster than one full table scan, so it seems like a useful tool to have.
Regards,
Mark
On Feb 25, 2016, at 10:16 AM, Samuel Pelletier < email@hidden> wrote:
Hi Mark,
I suggest you look into adding a collation to your column or index. I do not know how Oracle does this but it surely exists.
With an index using a collation, your query is trivial and much more efficient because the index is already case insensitive, there is no OR.
A query like the one you build generate multiple index lookup, one for each part of the OR and the server need to merge all those results before iterating into the rows, compute the UPPER for each rows and compare. Imagine the speedup if a single
index lookup can be used, I would expect at least 10X faster on large dataset... probably 100X if multiple OR parts returns 1000s rows.
Regards,
Samuel
Le 25 févr. 2016 à 10:16, Morris, Mark < email@hidden> a écrit :
Hi Lon,
We run into this a lot, and I have a rather complex solution for a “case insensitive begins with” qualifier that actually works well, at least with Oracle and its indexing. My method returning this qualifier is actually about 60 lines, but most
of that is optimizations for special cases. (We have terabytes of data, so every little bit helps!)
The basic idea, though, is that it only takes a couple of characters to hit on an index and make a huge difference in performance. So…
Trying to case-insensitive-equals match " email@hidden” with the search term “ email@hidden”, you would build this sort of qualifier:
(emailAddress like ‘PA*’ OR emailAddress like ‘Pa*’ OR emailAddress like ‘pA*’ OR emailAddress like ‘pa*’) AND UPPER(emailAddress) = UPPER(' email@hidden’)
Hope this helps!
Regards,
Mark
On Feb 24, 2016, at 7:12 PM, Chuck Hill < email@hidden> wrote:
If you always want the comparison to be case insensitive, then you should be able to specify a database collation for the column that is insensitive. Then just just a regular equals qualifier. If you want it case sensitive sometimes and insensitive
others, that is a different problem.
Chuck
Hmm, it would be interesting if there was an alternative, but really the problem lies in the database. Any wildcard type searches (at least that start with a wildcard) are not likely to use indexes and cause a table scan. If your database
supported the concept of having case-insensitive indexes, then I would think it would be pretty trivial to implement in your own qualifier. In Sybase (the database I use mostly) you can create a functional index (an index based on a function), but it’s the
equivalent creating a second column that’s always lower and maintaining an index on it. The difference being that the database maintains it for you (which is usually annoying for EOF).
I’ve always wanted a better way to do this too.
-Lon
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list ( email@hidden)
Help/Unsubscribe/Update your Subscription:
This email sent to email@hidden
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list ( email@hidden)
Help/Unsubscribe/Update your Subscription:
This email sent to email@hidden
|