You can try all options and see for yourself, my gut feeling is they'll vary in single percentages about 1-2% from each other, considering even an index only helps with one value it's even saving disk wear to not create them at all. Creating an index is a full table scan operation and thus only cn accelerate searches that can make use of the index multiple times. You're then doing a full table scan to use an index once, which is not preventing a full table scan. An index only used for one specific cn could be persisted, but only will help find this one cn again, later, even shorter filtered indexes will accumulate to more than double the data size due to organizational meta data.
farzad321 said:
paid for 2196409 - 2196410 / 2196411
Looks like you can extract all CNs, at least you don't shorten this three cn range to 2196409 - 2196411. Which would make searching for 2196410 fruitless.
If CNs are all numbers you can find all matches with the help of regexp:
Code:
LOCAL oRegExp
oRegExp = CREATEOBJECT("vbscript.regexp")
oRegExp.Global = .T.
oRegExp.Pattern = '\d+'
oMatches = oRegExp.Execute('paid for cn 60711982 / 4930028 89236711')
clear
? Textmerge('<<oMatches.Count>> CNs found:')
FOR EACH oMatch IN oMatches
? oMatch.value
ENDFOR
If you only want to extract numbers as a CN, when they are at leawst 5 digits long, the pattern becomes \d{5,}.
Instead of ? oMatch.value you can put an insert into a 1:n detail table, exactly. And then have a id and cn in there.
Looking for a CN then becomes SELECT * FROM CNs left Join table on CNs.id = table.id Where CNs.cn = '1234567' And an index on CNs.CN as well as on the Ids will make this a fast and exaustive search for all records related to a CN (and probably also others).
Once you have these records, you might expand on other CNs related to the table.ids and expand the set until you have all transaction for the group of CNs and verify total of payments with total billed amount of all the CNs. Which might balance to 0 or need another rate or turns out overpayed.
It's not that hard to do and then add a field stating the Desc1 field was parsed and extended 1:n data already exists. If Desc1 can change, that'd mean adding to the detail data, updating or deleting it, in the simplest case delete all for the updated table.id and create the new set of records. But whatever suits you better.
There unfortunately is no magic that turns a 100 Mbit LAN to a 1GBit LAN. But there is data normalization.
Chriss