Information générale
Forum:
Microsoft SQL Server
Titre:
Time and resources problem
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.
Suivant
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