I want to compare in a where clause of a select statement two variables typed char. The variable of the database table has the length 40 and the comparative variable 32. Is it possible to compare only the first 32 chars of the database's variable in a where condition? When yes how i can do it? And when if it isn't possible what are my alternatives? The database has over 300.000 entries so i cant loop them all in an internal table.
1 Answers
3
votes
Welcome, little programmer. first I would like to say, and to pinpoint to these (if You are not interested, scroll down to my recommended answer):
The database has more then 300.000 entries. You surely mean, the datatable, You want to select from, right? 300.000 entries for an entire database are peanuts.
I assume 300.000 entries in the table. You can't loop ? Sure You can. These times are over, dude, 300.000 are today's always growing standard. Do You know, why cloud, big data, hana, in memory-datastorages etc. evolved ?
But in the case (and I understand, that it is not really nice, to loop over more data, then it is necessary) there are some steps to shrink down the data after a full-table-scan. Let us deal with this word first.
1. AVOID FULL-TABLE-SCANS. WHENEVER POSSIBLE.
You always have to look, to get some where-parameters to define Your conditions properly. There are mostly some values to be used as where-conditions. Check also the existing indexes for usability.
2. OMIT SELECTING * from into corresponding.
Sometimes two selects can be better then one big, and expecially then, when data is getting bigger. For this case it is useful to create a type for an internal table, which holds ONLY the keys of the records, which are selected NOT by keys/foreign keys. Additionally You can add the where-clause-params as fields, or in Your case, these character fields (in general fields, which will be worked on to narrow down the results). And then select ONLY those desired fields into the internal table.
3. USE ALL DB-Functions, You can use
This passes all work, which the RDBMS can do, instead that it later on has to be run on the AS. Aggregating, no duplicates, sums, etc...
4. Do You know Your database? Then use it's hints
Similar to point 4, this passes execution down to the rdbms, resp. the database computer, where optimized rdbms runs, which might be able to do things better. This should be used with extreme caution.
5. Do You know the size of the result ? Choose the right internal table
Three things come together: Type of the internal table, amount of results, available values to be used as conditions for "read-table/loop at".
Hashed tables ? The fastest. They consume much memory. Especially for large structures. But You can define, how You want to create them. An small strucured internal table with many records can be faster, and consume
less memory, then a big structure with fewer records. This should be verified with the trace prior to a decision, on high traffic peak times.Sorted tables ? Best, if You have a numeric key in it, and a small structure. This is mostly the fastest combination after hashed.
Standard-tables ? You now migth interpret this for Yourself.
Keys ? Combine, if necessary, but try to use one primary key.
These are some crucial tips in terms of performance.
Once, You are in the situation, where You have Your (perhaps too huge result), especially in Your case, You can delete the internal table where the string does not contain the pattern of the where clause. This is a macro, for a similar thing, a loop would do, but i think, it is faster. "CP" is the contains pattern, "NP" is for not contains pattern. This might shrink down the results.
The answer is a list of alternatives ? No!
Why am I writing this, and post it as an answer? In the case, there is no answer, this would at least be the closest point to be used as an alternative.
In our SAP-environment we have this answer:
http://scn.sap.com/thread/1786222
CONCLUSION:
Create a ranges table, use "CP" as option and "I" as sign. Pass the where-condition-value into low. Execute the select with where "in blabla"
