Enrique,
Hey, how are you? How's everyone else?
I was reading the query and I thought the column names looked familiar - then I saw the company.
>> it's taking too much time and consuming a lot of resources. If you have any any suggestion I'll really appreciate it.
This is a rough query (did I write this one?). It's doing a self-join for every row in the outer table. You might be able to unroll the nested query using the UPDATE..FROM form.
Does the showplan from the QA show anything out of the ordinary? What happens if you pull
cw_clevel from the commwork_Indx1 index?
You might also get better performance by placing the indexes on a seperate physical drive. This would allow NT to use Async IO.
There's a whitepaper of performance tuning on Microsoft's website. You should check it out. Also, buy Delaney's Inside SQL Server 7.0.
-Mike