Please Help me. Problem with update

[Rguardado]

Hello:
I make an update in my app, but I cant understand why SQL uses an specific index for
find the row that I wanna update, i.e., I hope my SQL uses the index0 because is faster than
index1, but in the reality I see SQL uses index1 that is the slowest one.

The original syntax when SQL uses the slowest index is the following:

UPDATE table1
set field1=1, field2=2, field3=3
where field4=4 amd field5=5

I change the code for this, but I'm not sure that this can fix the problem:

UPDATE table1
set field1=1, field2=2, field3=3
from table1(index=index0)
where field4=4 amd field5=5



Can anyone explain this situation and How can I solve my problem?
Is correct the solution I gave to my problem?


Thanks

[Gregory]

1) use 'set showplan on' to check what index(s) (if any) query optimizer is using

2) may be u need run 'update statistics' (it's not a bad idea to run it every once in awhile, plus re-build indexes helps)


------------
Rguardado at 3/11/99 1:26:06 PM

Hello:
I make an update in my app, but I cant understand why SQL uses an specific index for
find the row that I wanna update, i.e., I hope my SQL uses the index0 because is faster than
index1, but in the reality I see SQL uses index1 that is the slowest one.

The original syntax when SQL uses the slowest index is the following:

UPDATE table1
set field1=1, field2=2, field3=3
where field4=4 amd field5=5

I change the code for this, but I'm not sure that this can fix the problem:

UPDATE table1
set field1=1, field2=2, field3=3
from table1(index=index0)
where field4=4 amd field5=5



Can anyone explain this situation and How can I solve my problem?
Is correct the solution I gave to my problem?


Thanks

[rguardado]

I already used these option. I also applied a DBCC and it says no errores. But along the workday
one of the indexes is damaging, I detected this with dbcc dbreindex, but I can't explain
why the index is corrupted every day. Someone says that is possible the disk is damaged and
maybe it is the reason of my problem.

It's correct the syntax for specifing the index that I wanna use instead of leave SQL to choice it's
own way



------------
Gregory at 3/11/99 1:55:57 PM

1) use 'set showplan on' to check what index(s) (if any) query optimizer is using

2) may be u need run 'update statistics' (it's not a bad idea to run it every once in awhile, plus re-build indexes helps)


------------
Rguardado at 3/11/99 1:26:06 PM

Hello:
I make an update in my app, but I cant understand why SQL uses an specific index for
find the row that I wanna update, i.e., I hope my SQL uses the index0 because is faster than
index1, but in the reality I see SQL uses index1 that is the slowest one.

The original syntax when SQL uses the slowest index is the following:

UPDATE table1
set field1=1, field2=2, field3=3
where field4=4 amd field5=5

I change the code for this, but I'm not sure that this can fix the problem:

UPDATE table1
set field1=1, field2=2, field3=3
from table1(index=index0)
where field4=4 amd field5=5



Can anyone explain this situation and How can I solve my problem?
Is correct the solution I gave to my problem?


Thanks