| jacques.dhondt@free.fr 2006-03-22, 8:23 pm |
|
Let's take those 2 tables
create table Car_val
( id numeric(19) identity not null primary key ,
val_int int not null ,
val_num numeric(19,6) not null
)
with about 7 000 000 lines
create table #Car_val
( id numeric(19) identity not null primary key ,
val_int int not null ,
val_num numeric(19,6) not null
)
with about 2, or 20 ,or 200 ....until 200 000 lines
In a sub procedure (with recompile)
update Car_val
set val_int = B.val_int
from #car_val B
where Car_val.id = B.id
if I hadn't seen it from my own eyes , i would not bother you.....
I have had a QP, with
update mode direct,
First table #car_val in TS,
Second table Car_val
what is marvellous, even if Sybase explains that UPDATE with join lead
always to deferred update mode.
The most of the time, I get
update mode is deferred
First table is #car_val
Second table is Car_val
I have made a lot of test, with #car_val with 2, 20, 200.... until 200
0000 lines.
I would like to know the reason why, suddendly, after having may be 20
QP with deferred mode,I have had 5 times
a QP with direct mode. That I can no more get today.
I have also tried with a table myCar_val resulting from a select into
from Car_val, plus the primary key.
I have had some QP with direct mode, and today impossible to reproduce
this marvel.
I am working on Sybase 12.5.3 on HP. These examples have been realised
on a test DS on which I was alone.
Which dbcc could provide me info to explain why Sybase sometimes does a
perfect QP?
For RobVershoort, Any chance to see your Tricks and tips being
reprinted?
|