>>>I am having a problem with the SQL Update below. From the amount of time it takes to run with only 1 or 2 records in the cursor c_chng I believe it is ignoring the index on k_ivinvt and scanning all 186K recs in the inventory table IVINVT. The last time I had this problem was on updating invoice lines and I fixed it by adding a 2nd line to the where clause to include the invoice key. This time there is no other index I can use. The two tables are tied only by the ivinvt.k_ivinvt = c_chng.k_ivinvt. This has got to be a really common event so I know I am missing something here.
>>>
>>>I added set enginebehavior 90 but it made no difference
>>>
>>>
set enginebehavior 90
>>>
>>>* Update inventory allocated amounts
>>>TEXT TO mSqlCommand noshow pretext 15
>>> update ivinvt
>>> set allocated = allocated + c_chng.chng
>>> from c_chng
>>> where ivinvt.k_ivinvt = c_chng.k_ivinvt
>>>ENDTEXT
>>>&mSqlCommand
>>
>>If the field Allocated is string you must ALLTRIM or RTRIM() it.
>>About slow, what is the result of:
>>
set enginebehavior 90
>>
>>* Update inventory allocated amounts
>>SYS(3054,12,[TestMe])
>>TEXT TO mSqlCommand noshow pretext 15
>> update ivinvt
>> set allocated = allocated + c_chng.chng
>> from c_chng
>> where ivinvt.k_ivinvt = c_chng.k_ivinvt
>>ENDTEXT
>>&mSqlCommand
>>MessageBox(TestMe)
>>SYS(3054,0)
>>
>
>Borislav,
>Allocated is a numberic 7,2 that holds the number if inventory items on sales orders that have not been invoiced.
>
>The result of SYS(3054)is:
>
&mSqlCommand
>Rushmore optimization level for intermediate result: none
>Rushmore optimization level for table ivinvt: none
>Joining table ivinvt and intermediate result using temp index
>
>Good suggestion but I don't know why it is not using Rushmore.
>
>Thanks for the help.
Did you have ORDER by K_Ivinvt of Ivinvt table?
Did ivinvt.k_ivinvt AND c_chng.k_ivinvt have the same length (if they are strings)?
What is SET DELETED status (WAIT WINDOW SET{DELETED]))?
Against Stupidity the Gods themselves Contend in Vain - Johann Christoph Friedrich von Schiller
The only thing normal about database guys is their tables.