| Author |
 Topic  |
|
|
mrjack
Yak Posting Veteran
50 Posts |
 Posted - 07/18/2006 : 03:01:43
|
Hi All,
Beside cursor, what else can i use to speed up my processing? Now i have about 2mils rows need to update using one daily reference table(30k rows).
Thanks,
Jack |
Edited by - mrjack on 07/18/2006 03:09:57
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16746 Posts |
Posted - 07/18/2006 : 03:06:04
|
Set based query.
update u
set col1 = cola,
col2 = colb,
...
from two_mil_table u inner join daily_ref_table r
on u.some_col = r.some_col
KH
|
 |
|
|
mrjack
Yak Posting Veteran
50 Posts |
Posted - 07/18/2006 : 03:09:40
|
thx khtan,
But i need more complex checking. It will lookup two columns in 2mil table..like if there any changes to any of those column, it will update it, and insert one row,...cannot find the record..will insert new row..
Thanks |
|
|
|
mrjack
Yak Posting Veteran
50 Posts |
Posted - 07/18/2006 : 03:18:58
|
here is my current query..hope this helps..
declare os cursor For select * from table2 where b_status = 1 OR b_status = 2
open os
Fetch next from os into @b_id, @b_number, @b_subid, @b_type, @b_status, @b_timestamp
while @@fetch_status = 0
BEGIN
set @a_id = 0
set @a_type = 0
set @a_status = 0
set @ver_id = 0
set @a_subid = 0
set @a_number = 0
select @a_id=a_id, @a_type=a_plan, @a_status=a_status, @ver_id=a_version, @a_subid=a_subid, @a_number=a_number
from table1 where a_subid=@b_subid and a_number=@b_number
set @sid=@sid +1
if @a_id > 0
BEGIN
if @a_type != @b_type or @a_status != @b_status
BEGIN
update table1 set sub_to_dttm=@b_timestamp where a_id=@a_id
Insert into table1(columns)
values (values)
END
END
else
BEGIN
Insert into table1(columns)
values (values)
END
Fetch next from os into @b_id, @b_number, @b_subid, @b_type, @b_status, @b_timestamp
END
close os
deallocate os
GO
|
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16746 Posts |
Posted - 07/18/2006 : 03:23:58
|
-- UPDATE
update u
set col1 = cola,
col2 = colb,
...
from two_mil_table u inner join daily_ref_table r
on u.some_col = r.some_col
where col1 <> cola
or col2 <> colb
-- INSERT
insert into daily_ref_table ( . . .)
select . . .
from two_mil_table t
where not exists (select * from daily_ref_table x where x.col = t.col)
KH
|
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16746 Posts |
Posted - 07/18/2006 : 03:41:26
|
modify from the code from your last post
update t1
set sub_to_dttm = t2.b_timestamp
from table1 t1 inner join table2 t2
on t1.a_subid = t2.b_subid
and t1.a_number = t2.b_number
where t1.a_plan <> t2.b_type
or t1.a_status <> t2.b_status
insert into table1 ( . . . )
select . . .
from table2 t2
where not exists (select * from table1 x where x.a_id = t2.a_id)
KH
|
|
|
|
mrjack
Yak Posting Veteran
50 Posts |
Posted - 07/18/2006 : 03:50:55
|
Thx khtan, from the code i think it will goin to work, but it wont deadlock rite? cos the table increasing day by day..
and if u notice, after the update, it will inserted a new row based on the update info...
BEGIN
update table1 set sub_to_dttm=@b_timestamp where a_id=@a_id
Insert into table1(columns)
values (values)
END
insert into table1 ( . . . )
select . . .
from table2 t2
where not exists (select * from table1 x where x.a_id = t2.a_id)
this query only if the record didnt exist in the table1..
|
|
|
|
mrjack
Yak Posting Veteran
50 Posts |
Posted - 07/18/2006 : 04:13:44
|
the logic is like this..
if row exist
UPDATE table1
set sub_to_dttm = t2.b_timestamp
then it will insert new row (col1,col2,sub_to_dttm) values (val1,val2,2010-12-31) <--same column value like updated row .. except for few have latest value from table2
else (row didnt exist)
insert new row (col1,col2,sub_to_dttm) values (val1,val2,2010-12-31)
|
|
|
|
mrjack
Yak Posting Veteran
50 Posts |
Posted - 07/19/2006 : 04:03:02
|
| im stuck with cursor rite? hehehe... |
|
|
|
AndrewMurphy
Flowing Fount of Yak Knowledge
Ireland
2915 Posts |
Posted - 07/19/2006 : 07:38:35
|
im stuck with cursor rite? hehehe...
I don't think so....have you tried the code suggested on a smaller subset to prove it does what you want it to do....or did you just read it and discard on the basis of either not knowing what it does or not believing it'll solve your problem?
khtan doesn't get to post 2800+ items here without the odd useful snippet!! |
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16746 Posts |
Posted - 07/19/2006 : 07:50:00
|
"khtan doesn't get to post 2800+ items here without the odd useful snippet!!"
I think i contribute more to this forum than others 
KH
|
|
|
| |
Topic |
|
Alternative to cursor..
