>>Hi,
>>
>>I am looking to see if we're using the right code to extract some data. Tables are networked, can be from 500Mo to 1,5Go (10 millions to 35 millions records), index keys can be from 25 to 35 bytes long and each tables has 2 or 3 indexes.
>>
>>Currently, the code looks like this :
>>

>>=oTable.OuvTabNom("matponde", "cle", m.tcMode)
>>
>>SELECT matponde
>>COPY STRU TO (m.tcPathDest + "MatPonde.dbf")
>>
>>USE (m.tcPathDest + "MatPonde.dbf") ALIAS res IN 0 EXCLUSIVE
>>
>>SELECT (m.tcCurTransit)  &&list of data to extract
>>SCAN FOR INDEXSEEK(id_client + dte, .T., "matponde", "cle")
>>     lcCle = id_client + dte
>>
>>     SELECT matponde
>>     COPY TO (m.tcPathDest + "TmpMatPonde.dbf") WHILE m.lcCle = inst + transit + LEFT(DTOS(dt_prod),6)
>>
>>     SELECT res
>>     APPEND FROM (m.tcPathDest + "TmpMatPonde.dbf")
>>
>>     SELECT (m.tcCurTransit)
>>ENDSCAN
>>
>>USE IN res
>>=toTable.FerTabNom("matponde")

>>
>>Note about the previous code : The source tables isn't always the same, we have many different. The cursor containing the list of data to extract can be from 1 to 30 records (1 records results in 1500 to 3500 records resultset according to source table). tcPathDest is always a local temporary folder. This code is part of an automated Import/Export application running on the network and process at a daily average of 500 extraction requests.
>>
>>I was wondering if this technique (COPY TO WHILE and then APPEND FROM) was really better than a SELECT-SQL or not. I haven't develloped this but I have been told it was faster than SELECT-SQL because the data is quite large (and networked) and also the index keys are large (too heavy for RUSHMORE to read the index keys on the network).
>>
>>Any suggestions about the technique we're using VS SELECT-SQL considering the environment ?
>>
>>Thanks,
>>Sylvain Bujold
>
>Both COPY TO and SELECT-SQL will have to create a new file so if speed is what you're looking for, the difference may be negligible. Either COPY TO or SELECT will require a read then a write, and APPEND will require a read and a write.
>
>You may be able to increase performance by replacing COPY TO WHILE with SCAN WHILE then replacing APPEND FROM with INSERT INTO. This would create a Read, Read, Write instead of Read, Write, Read, Write.

Mark,
While that's true from read-write point I think multiple insert into would slow it down.
Cetin