I have a DELL computer with 2 gigabytes in RAM, one hard disk with more than 10 gigabytes free and I'm running a SELECT statement from VFP 6.0 to update my Commwork table in SQL 7.0 which contains more than 100000 rows and it's taking too much time and consuming a lot of resources. If you have any any suggestion I'll really appreciate it.

The indexes I'm using in the Commwork table are NONCLUSTERED:
CREATE INDEX commwork_Indx1 ON commwork(cw_left, cw_right, cw_level,cw_clevel)
CREATE INDEX commwork_Indx2 ON commwork(cw_dist_id)
CREATE INDEX commwork_Indx3 ON commwork(cw_sponsor1)

The UPDATE statement is:

cSQL = [UPDATE commwork ] + ;
[SET cw_clevel = cw_clevel - ] + ;
[(SELECT COUNT(*) ] + ;
[FROM commwork a INNER JOIN commwork b ] + ;
[ON b.cw_left > a.cw_left AND b.cw_right < a.cw_right ] + ;
[WHERE b.cw_dist_id = commwork.cw_dist_id AND a.cw_active = 0) ] + ;
[WHERE cw_customer = 0 ]


If I run the UPDATE statement with 100000 rows in Commwork the elapsed time is 3 1/2 hours.

If I run the UPDATE statement with 200000 rows in Commwork, it never finished and the computer got locked.