Honestly I just finished a port of goldmine data to sapdb and the query speed is still pretty damn good even using UPPER() in the query. This leads me to think that even though the indexed data may not match exactly all you are losing is the overhead of each UPPER function call. Why do I think that you may ask?
As the index is traversed the UPPER function is applied to the fetched data and then the comparison is made, if a match is found it is returned. This only differs in that if the UPPER function is not used all you gain back is the overhead of each UPPER function call. Also, if the column you specify is indexed that index will be searched and you will not hit a table scan, only non-indexed columns will result in table scans. I do think there may be databases that will switch to a table scan if what you are looking for is not in the index but this is only a guess. It has been a while since I have done C code so forgive me if this is wrong but using strcmp(a,b) vs strcmp(toupper(a),b)) shouldn't be that much of a hit. This is only a guess as I am sure the fine programmers as sap use much more efficient methods of comparing data but anyone feel free to chime in and correct me if I am mistaken. -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of [EMAIL PROTECTED] Sent: Thursday, June 19, 2003 2:06 PM To: Dave Guyer Cc: [EMAIL PROTECTED]; SAP List Subject: RE: Case insensitive queries in SAP? You can add another field which is the uppercase version of the field you're comparing too, but obviously that requires a lot of space if it's a large table. But that lets you index on that. Unfortunately I haven't found an elegant solution! Cheers, David Dave Guyer <[EMAIL PROTECTED]> on 19/06/2003 02:59:00 PM To: [EMAIL PROTECTED], SAP List < [EMAIL PROTECTED]> cc: Subject: RE: Case insensitive queries in SAP? Yes, that's what I'm hearing. The required change to code is a pain, but the big problem that I see is that to do a selection query requires reading the entire table. An index on the character fields does us no good. -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Thursday, June 19, 2003 11:01 AM To: Dave Guyer Cc: SAP List; Scott Carlson; Nathan Morrow Subject: Re: Case insensitive queries in SAP? Yeah, we hit this too. :-( There is no flag - you have to change your queries to convert both parts of your comparisons to uppercase (or lower) - so when you are creating the SQL, change the parameter you are comparing against to upper case, and then use the UPPER function in SAP to create the field to upper to do the compare. HTH, David Dave Guyer <[EMAIL PROTECTED]>@listserv.sap.com on 19/06/2003 01:11:49 PM Sent by: [EMAIL PROTECTED] To: SAP List <[EMAIL PROTECTED]> cc: Scott Carlson <[EMAIL PROTECTED]>, Nathan Morrow <[EMAIL PROTECTED]> Subject: Case insensitive queries in SAP? I just received a call from our JAVA development Team Lead indicating that initial experiments with SAP DB shows that queries of character data are case sensitive. This testing was done both with SQL Studio and with JAVA code and the JDBC connection. They have coded for databases where queries are not case sensitive. Is there a flag that can be set to change this or some other way to change it besides modifying the code for every query? If not, what change would be required in the code? Part of the problem is that we will be working with data from Progress databases. Progress by default is not case sensitive, so we can't depend on the case of the data. _______________________________________________ sapdb.general mailing list [EMAIL PROTECTED] http://listserv.sap.com/mailman/listinfo/sapdb.general (See attached file: C.htm) _______________________________________________ sapdb.general mailing list [EMAIL PROTECTED] http://listserv.sap.com/mailman/listinfo/sapdb.general
