I have a table (Table1) which consists of one field..a CHAR(254). I
append several million records(sdf). The first line in a set starts
with a key number. Info is taken from that line by substrings and
inserted into TABLE2. The next 3-5 lines in the scan all have info
about that key number. After all loaded, a scan goes through and
depending on the info in that line, decides what kind of info it is,
puts it in a variable through use of substring, and after filling all
the variables, replaces the fields in TABLE2 with those variables where
keynum = value. This process is running incredibly slow. I've tried:

update TABLE2 set field1=var1, field=var2 where keynum = keyvar

and also

replace field1 with var1 etc for keynum =keyvar.

I get the correct results, but running on a 3.2 Gig processor with 1
Gig ram, it still crawls.

After loading Table1, I create an index on the KEYNUM field (index on
keynum to i_keynum)which I thought would help the speed, but no.

Is there an advantage to creating an index on the character string in
Table1? Since I'm doing a scan and searching for substrings in each
line, I didn't see where it would help, but I'll never get through 12
million records this way (not all at once). Any ideas would help!
Thanks.

Molly - lets flip it a bit ?
put an index on table2 on the keynum field/column .
----
also [and instead]
once you are in table 1, and before you do an insert into table 2 -
can't you handle your intermediate processing / info type determination
immediately after reading a record from table 1 ?
if you are already setting up the string extrapolation [field1, field2, etc]
from the long character string in table1, then why not add a few lines of
code [which you may have already written] and set a variable for the info
type ? then ? when you got all of your fieldvariables 'set' the way you
want, then do an insert into table2 with the fieldvariables going to the
field values.

let me know your thoughts ?
mondo regards [Bill]
--
William Sanders / Electronic Filing Group Remove the DOT BOB to reply via
email.
Mondo Cool TeleCom -> http://www.efgroup.net/efgcog.html
Mondo Cool WebHosting -> http://www.efgroup.net/efglunar.html
Mondo Cool Satellites -> http://www.efgroup.net/sat
VFP Webhosting? You BET! -> http://efgroup.net/vfpwebhosting
mySql / VFP / MS-SQL

"MollyShark" <monica (AT) datashark (DOT) net> wrote