insert statements too slow??

Hi All,

I have a foxpro master table, which consists of > 100000 records.
those master records have to be copied to around 6 "children" table
separately, according to their categories

for example,

records of "select * from master_table where category = 1" will copy to
child_table_1
records of "select * from master_table where category = 2" will copy to
child_table_2
................
................
................
etc...........

I was using the cursors to fetch the master table record one by one and
insert into related child table, and i've found that it is very slow. The
full process, copy from master table to different children, took me more
than 3 hours !!

I wonder if there is any faster method to do so?

p.s. i am using foxpro 5.0

Regards,
Angus

Andrew
Fri Dec 03 05:01:30 CST 2004

angus wrote:
> Hi All,
>
> I have a foxpro master table, which consists of > 100000 records.
> those master records have to be copied to around 6 "children" table
> separately, according to their categories
>
> for example,
>
> records of "select * from master_table where category = 1" will copy
> to child_table_1
> records of "select * from master_table where category = 2" will copy
> to child_table_2
> ................
> ................
> ................
> etc...........
>
> I was using the cursors to fetch the master table record one by one
> and insert into related child table, and i've found that it is very
> slow. The full process, copy from master table to different children,
> took me more than 3 hours !!
>
> I wonder if there is any faster method to do so?

Indeed, INSERT is dog slow ;)

If you use
SELECT * ;
FROM master_table ;
WHERE category=1 ;
INTO TABLE c:\tempdir\tempfoo

IF _TALLY>0
SELECT childtable
APPEND FROM c:\tempdir\tempfoo
DELETE FILE c:\tempdir\tempfoo.dbf
ENDIF

then it will be far faster.


Actually I believe you can do
SELECT * ;
FROM master_table ;
WHERE category=1 ;
INTO CURSOR temp

SELECT childtable
APPEND FROM DBF('temp')
* (doesn't require tidying up temp files)

I've just been a bit superstitious about it.

--
HTH
Andrew Howell

Stefan
Fri Dec 03 05:36:36 CST 2004

> I wonder if there is any faster method to do so?

There are some missing details I think.
Which part of your code is the "bottleneck" and how does it look
like exactly?

> records of "select * from master_table where category = 1" will copy to
Here you'd get Rushmore optimization by having an
Index On category Tag xy
What does Sys(3054) show for the repeated statement?

Andrew's Append From .. For suggestion looks promising.
Or try Set Order To <your order> and Scan/Endscan master_table
instead of multiple SQL Select's might give better performance, too.


hth
-Stefan

"angus" <angus@angus.com> schrieb im Newsbeitrag
news:O3sjvMS2EHA.2608@TK2MSFTNGP10.phx.gbl...
> Hi All,
>
> I have a foxpro master table, which consists of > 100000 records.
> those master records have to be copied to around 6 "children" table separately,
> according to their categories
>
> for example,
>
> records of "select * from master_table where category = 1" will copy to
> child_table_1
> records of "select * from master_table where category = 2" will copy to
> child_table_2
> ................
> ................
> ................
> etc...........
>
> I was using the cursors to fetch the master table record one by one and insert into
> related child table, and i've found that it is very slow. The full process, copy
> from master table to different children, took me more than 3 hours !!
>
> I wonder if there is any faster method to do so?
>
> p.s. i am using foxpro 5.0
>
> Regards,
> Angus
>

Stefan
Fri Dec 03 05:37:14 CST 2004


"Andrew Howell" <ajh@work> schrieb
>
> Indeed, INSERT is dog slow ;)

Your Append From suggestion is probably much quicker than multiple
Insert-SQL statements in the OP's scenario.
But that's a "single vs. mutiple statements" issue, I think.
Insert-SQL on it's own is much quicker than Append Blank + Replace,
isn't it.


Regards
-Stefan

Andrew
Fri Dec 03 07:10:05 CST 2004

Stefan Wuebbe wrote:
> "Andrew Howell" <ajh@work> schrieb
>>
>> Indeed, INSERT is dog slow ;)
>
> Your Append From suggestion is probably much quicker than multiple
> Insert-SQL statements in the OP's scenario.
> But that's a "single vs. mutiple statements" issue, I think.
> Insert-SQL on it's own is much quicker than Append Blank + Replace,
> isn't it.

Agree, good point. I though we were talking about hundreds or
maybe thousands of records but that may not be the case necessarily.

--
Regards
Andrew Howell

Stefan
Fri Dec 03 08:04:43 CST 2004


"Andrew Howell" <ajh@work> schrieb im Newsbeitrag
news:e$u4qlT2EHA.804@TK2MSFTNGP12.phx.gbl...
> Stefan Wuebbe wrote:
>> "Andrew Howell" <ajh@work> schrieb
>>>
>>> Indeed, INSERT is dog slow ;)
>>
>> Your Append From suggestion is probably much quicker than multiple
>> Insert-SQL statements in the OP's scenario.
>> But that's a "single vs. mutiple statements" issue, I think.
>> Insert-SQL on it's own is much quicker than Append Blank + Replace,
>> isn't it.
>
> Agree, good point. I though we were talking about hundreds or
> maybe thousands of records but that may not be the case necessarily.

Right, nothing wrong with your advice in this context! I was just trying
to "defend" the reputation of the good old Insert-SQL in general :-)


-Stefan

David
Fri Dec 03 10:16:37 CST 2004

Angus,

I'm not sure that you have to go through the double work Andrew suggests.

select * ;
from master
into table child_table_1 ;
where category = 1

There is only one read and one write pass that needs to be made.

You can even put it in a loop like this:

select distinct category ;
from master ;
into array laCategories

n = _tally
for i = 1 to n
select * ;
from master
into table ("child_table_" + transform( laCategories[i] )) ;
where category = laCategories[i]
endfor

Unless you are on a dog slow machine/network I can't imagine this taking
more than a couple of minutes to run. If it's a network issue it might be a
lot faster to copy the tables local generate the child tables locally and
then copy them back out to the network drive.

--
df - Microsoft MVP FoxPro http://www.geocities.com/df_foxpro

"angus" <angus@angus.com> wrote in message
news:O3sjvMS2EHA.2608@TK2MSFTNGP10.phx.gbl...
> Hi All,
>
> I have a foxpro master table, which consists of > 100000 records.
> those master records have to be copied to around 6 "children" table
> separately, according to their categories
>
> for example,
>
> records of "select * from master_table where category = 1" will copy to
> child_table_1
> records of "select * from master_table where category = 2" will copy to
> child_table_2
> ................
> ................
> ................
> etc...........
>
> I was using the cursors to fetch the master table record one by one and
> insert into related child table, and i've found that it is very slow. The
> full process, copy from master table to different children, took me more
> than 3 hours !!
>
> I wonder if there is any faster method to do so?

George
Fri Dec 03 21:11:57 CST 2004

Hey guys,

Do you think that deleting the indexes in the children tables, inserting
the records, then recreating the indexes would help?
Just wondering if for every insert, the indexes (and trigers,
constraints, etc) have to be updated/executed.

Just wondering, any insights ?

angus wrote:
> Hi All,
>
> I have a foxpro master table, which consists of > 100000 records.
> those master records have to be copied to around 6 "children" table
> separately, according to their categories
>
> for example,
>
> records of "select * from master_table where category = 1" will copy to
> child_table_1
> records of "select * from master_table where category = 2" will copy to
> child_table_2
> ................
> ................
> ................
> etc...........
>
> I was using the cursors to fetch the master table record one by one and
> insert into related child table, and i've found that it is very slow. The
> full process, copy from master table to different children, took me more
> than 3 hours !!
>
> I wonder if there is any faster method to do so?
>
> p.s. i am using foxpro 5.0
>
> Regards,
> Angus
>
>

David
Sat Dec 04 10:03:46 CST 2004

George,

It would definately.improve performance. It'll also make the CDX file
smaller.

--
df - Microsoft MVP FoxPro http://www.geocities.com/df_foxpro

"George" <SpamMyAss@Mailinator.com> wrote in message
news:%23ZkaD8a2EHA.3132@TK2MSFTNGP14.phx.gbl...
> Hey guys,
>
> Do you think that deleting the indexes in the children tables, inserting
> the records, then recreating the indexes would help?
> Just wondering if for every insert, the indexes (and trigers, constraints,
> etc) have to be updated/executed.
>
> Just wondering, any insights ?

Dan
Sat Dec 04 23:57:07 CST 2004

And it may whack RI code.

David Frankenbach wrote:
> George,
>
> It would definately.improve performance. It'll also make the CDX file
> smaller.
>
>
> "George" <SpamMyAss@Mailinator.com> wrote in message
> news:%23ZkaD8a2EHA.3132@TK2MSFTNGP14.phx.gbl...
>> Hey guys,
>>
>> Do you think that deleting the indexes in the children tables,
>> inserting the records, then recreating the indexes would help?
>> Just wondering if for every insert, the indexes (and trigers,
>> constraints, etc) have to be updated/executed.
>>
>> Just wondering, any insights ?