Unfortunatly it's already down to one insert into from an array (tried gather too, identical speed), and I tried making the array 2 dimensional so it could do 100 records at a time but it made no difference, it seems that internally it's doing the fpt updates one by one however I pass the update. thanks for the thought though.
I'm working on replacing the memo fields with numeric fields referencing byte positions in the original data so that should solve it, I'll post back again on how it goes.


>>i think the "FPT data will be wriiten virtually as it changes." is where the problem lies, that and perhaps somthing to do with foxpro ordering everything to be written straight away. the machine has 2 gig of ram it's xp and I've tried both the optimise for programs and system options (no notable difference), vfp can have as much ram as it wants, and there is no flush/forces.
>
>Right, it is VFP that is "ordering" the .FPT data to be written right away.
>
>The reason I write (again) is that I had a thought that may be at play...
>Are you writing (REPLACE or INSERT INTO) the memo fields in a single statement? If perchance you are using multiple REPLACEs to write those 30 memo fields, I would expect there to be a huge difference if you can get it down to one.
>
>I do not believe the same applies to the DBF/CDX, but quite sure about the FPT.
>
>good luck
>
>
>>
>>pretty much all there is:
>>500 meg text file
>>50 field table, 30 memo, 20 character/numeric fields
>>I use a dll call to pull in a 'record' from the text file, this will be about 70 lines or 2500 ish bytes of data, it takes 0.000006 of a second
>>I do a alines on that and from that array pull out a few important bits of data, trim/modify some of the data etc etc, and put it back together in a new array which fits the table, all that takes about 0.000020 of a second
>>I then do either a insert into table from array or a gather from array memo, I've tried both and in either case they take about 0.00425 of a second, which is about 20 seconds per 5k records. so I tried looping the reading bit 100 times making a 2 dimension array and then doing the insert (gather no longer an option) and it took exactly 100 times as long! that's the bit I didn't expect, I thought if I batched the writes up it's see a huge improvement but it's exactly the same.
>>
>>it's all writing to the local drive which is a 2 disk raid 0 array, there is no other disk to separate the read/write however they are fast disks and even given that they are switching between read and write operations should get through half a gig in somthing like 14 seconds rather than 14 minutes.
>>
>>I think bypassing the memo fields is going to be the way, do the read fill in the table with the important fields along with numeric byte locations to the original data files, then do all the matching and sorting, and at the point of export back to text file read directly from the original files. it'll be the same effect as if
>>I had the memo field but with a little more code to avoid foxpro handling that part of the data.
>>
>>Thanks for the suggestions, if I do find a good way around this I'll report back.
>>
>>
>>>Have you limited VFP's use of RAM in any way? If so, can you remove it for test purposes?
>>>
>>>Does your system have lots of RAM? Is the system set to optimize for performance or ??? (sorry, only have VISTA aailable and MS has buried the thing I am referring to, but in XP it's undr an adnavced settings button on a page of "System" in Control Panel).
>>>
>>>Are you writing to a local HD or to a network drive? If local, is it the same physical drive as C: and if so, can you try another physical drive?
>>>
>>>Are you using FLUSH / FORCE? If so, can you test without it? I can see that slowing things down a lot in certain circumstances.
>>>
>>>It has ben my obseravtion that, given lots of available RAM, VFP (and/or Windows) will wait until cache (RAM) is in jeopardy before physically writing DBF and CDX files. However, FPT data will be wriiten virtually as it changes.
>>>
>>>Good luck
>>>
>>>
>>>>been trying a few things to get the best performance I can out of importing a lot of data. I've got some really big data files up to around 500 meg, these need to be pulled into tables where they are sorted and matched and then output into pipe delimited files for some other software down the line.
>>>>
>>>>due to the amount of data (when all in just a dbf I was hitting the 2 gig mark) and the fact that only a small amount of it is important for the sorting and matching I have tables with useful fields and memo fields. our data comes in in text files where every 70 - 80 lines is a record, and then you hit an ascii character 12, I've speeded up the reading of the files by replacing my fgets(), I wrote a c++ dll that uses memory mapped file io and returns a full record at a time which I can then use alines on and extract the usefull fields and put the rest in the memo fields.
>>>>
>>>>the reading and splitting part takes just over a second per 100meg ie as fast as the hd array can manage
>>>>
>>>>however writing that data back to the dbf adds 14 minutes to the process.
>>>>
>>>>admittedly when all is said an done the dbf+fpt is around a gig when the file being read in is only 500meg but surely there is a way of getting better than 14 mins without me writting a new dll for writing the table and fpt bypassing foxpro's internal io?
>>>>
>>>>initally I was outputting a record at a time from my array using gather from array memo
>>>>
>>>>every 5000 records took about 20 seconds
>>>>
>>>>I thought that batching it would be the solution so I read into the array 50 records at a time and used insert into blah from array
>>>>
>>>>every 5000 records then took..... 20 seconds! no difference
>>>>
>>>>I've profiled the program and 95% of all the time taken is in that write to table/fpt command, is there anything I can do?
>>>>
>>>>
>>>>Ken.