FoxPro Help and Support - Fastest way to modify 2.5 million records?

Author

Fastest way to modify 2.5 million records?

Barley Man

2005-05-05, 7:25 am

Continuing with the same text file.........

I've imported 2.5 million records from a text file. I need to amend about 10
fields in each record. I have been using 'replace with' for each of the
changes but this is SLOW. I will need to repeat the exercise weekly and thus
speed matters here.

What's the quickest way to update such records?

Will it be best to create a new file and populate it with "insert into..."

Ian

Jeroen van Kalken

2005-05-05, 7:25 am

On Thu, 5 May 2005 03:31:02 -0700, "Barley Man"
< BarleyMan@discussion
s.microsoft.com> wrote:

Replace should be the fastest option. However a little is dependend on
wheter you have to update all records or just a few (check Rushmore).
Also replace all the fields in one command, instead of 10 seperate
replace-commands
The slowness could also be the result of what functions you use to
created the new field info.

"Insert into ..." wll be slower because it has to create and write the
complete file again, whereas replace only writes small parts of the
file.

If the file is on a network, it is sometimes faster to copy the file
to a local disk, do the replaces and copy it back to the network. (or
create it locally in the first place)

>Continuing with the same text file.........
>
>I've imported 2.5 million records from a text file. I need to amend about 10
>fields in each record. I have been using 'replace with' for each of the
>changes but this is SLOW. I will need to repeat the exercise weekly and thus
>speed matters here.
>
>What's the quickest way to update such records?
>
>Will it be best to create a new file and populate it with "insert into..."
>
>Ian

Barley Man

2005-05-05, 7:25 am

How do I replace all the the fields in one command?

I am doing something like:-
Replace orders.date with orders.date+10
Replace orders.name with Proper(orders.name)
Replace orders.blank with date()
(etc.)

How do I combine those three into one command?

Secondly, I have just tested 'insert into' and it is about 5 times faster
than my earier ('replace with') method but it's still slow!

I am doing it on a local disk, not over a network.

The whole 'update' ('replace with' or 'insert into') method is considerably
slower than the importation of the text file which initially creates the 2.5
million record file in the first place!

Ian
Thanks anyway, Jeroen

"Jeroen van Kalken" wrote:

> On Thu, 5 May 2005 03:31:02 -0700, "Barley Man"
> < BarleyMan@discussion
s.microsoft.com> wrote:
>
> Replace should be the fastest option. However a little is dependend on
> wheter you have to update all records or just a few (check Rushmore).
> Also replace all the fields in one command, instead of 10 seperate
> replace-commands
> The slowness could also be the result of what functions you use to
> created the new field info.
>
> "Insert into ..." wll be slower because it has to create and write the
> complete file again, whereas replace only writes small parts of the
> file.
>
> If the file is on a network, it is sometimes faster to copy the file
> to a local disk, do the replaces and copy it back to the network. (or
> create it locally in the first place)
>
>
>

Jan Bucek

2005-05-05, 7:25 am

Barley Man napsal(a):
> How do I replace all the the fields in one command?
>
> I am doing something like:-
> Replace orders.date with orders.date+10
> Replace orders.name with Proper(orders.name)
> Replace orders.blank with date()
Replace orders.date with orders.date+10,orders.name with
Proper(orders.name),orders.blank with date()
[color=darkred]
> (etc.)
>
> How do I combine those three into one command?
>
> Secondly, I have just tested 'insert into' and it is about 5 times faster
> than my earier ('replace with') method but it's still slow!
>
> I am doing it on a local disk, not over a network.
>
> The whole 'update' ('replace with' or 'insert into') method is considerably
> slower than the importation of the text file which initially creates the 2.5
> million record file in the first place!
>
> Ian
> Thanks anyway, Jeroen
>
> "Jeroen van Kalken" wrote:
>
>

Barley Man

2005-05-05, 9:24 am

Thanks for the help, guys.

The concatenated 'replace' aproach took 79.1 seconds,
the 'insert into' approach took 81.6 seconds

That's close!

Both methods were a great improvement on my original approach which took
around half an hour!

Thanks again, guys! I really didn't know that you could concatenate
'replace' statements; that's a major improvement on what I have done before!

Ian

"Jan Bucek" wrote:

> Barley Man napsal(a):
> Replace orders.date with orders.date+10,orders.name with
> Proper(orders.name),orders.blank with date()
>
>

Jeroen van Kalken

2005-05-05, 9:24 am

X-Newsreader: Forte Agent 1.93/32.576 English (American)
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: microsoft.public.fox.helpwanted
NNTP-Posting-Host: jedisoftware.demon.nl 82.161.161.33
Path: TK2MSFTNGP08.phx.gbl!TK2MSFTNGP15.phx.gbl
Lines: 1
Xref: TK2MSFTNGP08.phx.gbl microsoft.public.fox.helpwanted:33535

On Thu, 5 May 2005 06:28:08 -0700, "Barley Man"
< BarleyMan@discussion
s.microsoft.com> wrote:

Don't use the alias in the replace command. This could cause you some
hard to find errors. If necessary add an IN clause to the replace
replace date with date+10, name with proper(name) in orders.

Are you scanning through the alias and replacing every record?
A "replace all date with ..." will be a lot faster. Or a "replace for
date<{^1900/12/31} date with ..." if you only have to do some
records...
..[color=darkred]
>Thanks for the help, guys.
>
>The concatenated 'replace' aproach took 79.1 seconds,
>the 'insert into' approach took 81.6 seconds
>
>That's close!
>
>Both methods were a great improvement on my original approach which took
>around half an hour!
>
>Thanks again, guys! I really didn't know that you could concatenate
>'replace' statements; that's a major improvement on what I have done before!
>
>Ian
>
>"Jan Bucek" wrote:
>

Al Marino

2005-05-05, 1:24 pm

along with what everyone else has said
if there is an index on any of the amended fields
then 1st delete it and recreate at end

al

"Barley Man" < BarleyMan@discussion
s.microsoft.com> wrote in message
news:958DC528-C17A-4FB3-ADB9- 5CF6DCD682A8@microso
ft.com...
> Continuing with the same text file.........
>
> I've imported 2.5 million records from a text file. I need to amend about
10
> fields in each record. I have been using 'replace with' for each of the
> changes but this is SLOW. I will need to repeat the exercise weekly and
thus
> speed matters here.
>
> What's the quickest way to update such records?
>
> Will it be best to create a new file and populate it with "insert into..."
>
> Ian

Sponsored Links