>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)
>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
Sylvain,
When I first told something similar to this years ago I had to wear flame suit on :)
I think the developer really coded it nice and don't touch a single bit.
PS: If array limit (65000) would allow it (otherwise coding is more complex) you could replace :
COPY TO (m.tcPathDest + "TmpMatPonde.dbf") WHILE m.lcCle = inst + transit + LEFT(DTOS(dt_prod),6)
SELECT res
APPEND FROM (m.tcPathDest + "TmpMatPonde.dbf")
COPY TO array arrTemp WHILE m.lcCle = inst + transit + LEFT(DTOS(dt_prod),6)
insert into res from array arrTemp
Cetin