Information générale
Catégorie:
Codage, syntaxe et commandes
Titre:
Speed Issue (from Oct 16): My Answer !
Big Thanks !!! to all who gave me input about the
performance issue (SET RELATION .vs. SET FILTER .vs.SCAN .vs. SELECT SQL)
I posed on Oct 16. I am trying to maximize performance in running calculations on ~20 related tables of Manufacturing data.
The winner of the performance challenge was a quirky combination of
1)SQL-Select and 2) UPDATE ON ... REPLACE (the old UPDATE - no longer documented.
1)SQL-Select is a fast way to combine table values and
2)UPDATE ON... is the fastest way to replace values in one table based on
values in another table (using a common key, in this case recno() )
NOTE: None of this would have been necessary if SQL-Update was able to
operate on multiple tables like it is in many server based
SQLs (like MS SQL Server)
NOTE2: This is a much simplified version of the tables/code I was testing.
*The tables
parent.dbf
parent_id,
value_1
child.dbf
child_id,
parent_id,
value_2,
calc_value,
recnum &&Holds the recno() for each record.
*OLD METHOD (Benchmark on 3000 child records: 5 seconds) :
* (I did index on child.parent_id to help optimize the scan)
SELECT parent
scan
SELECT child
scan for child.parent_id = parent.parent_id
replace calc_value with parent.value_1 * child.value_2
endscan
endscan
*Suprisingly FASTER METHOD(Benchmark on 3000 child records: <1 second) :
* First, refresh the stored recno() each time.
* I get away with this because I am not adding any records just
* processing/updating existing records. Also, I would not have
* to do this if SQL behaved properly using recno() in multi-table
* SELECTs. Luckily, this replace is fast.
select child
replace recnum with recno() for .T.
SELECT recnum, parent.value_1 * child.value_2 as new_value ;
from parent, child ;
into cursor cursor_1 ;
where parent.parent_id = child.parent_id
select child
update on recnum from cursor_1 replace calc_value with c1.new_value
Sorry for the long message ! Thanks again.
Peter.
Répondre
Voir le fil de ce thread
Voir le fil de ce thread à partir de ce message seulement
Voir tous les messages de ce thread
Voir tous les messages de ce thread à partir de ce message seulement