Alternative to cursor.. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Alternative to cursor..

Author

Topic

mrjack
Yak Posting Veteran

50 Posts

Posted - 2006-07-18 : 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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-07-18 : 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

mrjack
Yak Posting Veteran

50 Posts

Posted - 2006-07-18 : 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 - 2006-07-18 : 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)

17689 Posts

Posted - 2006-07-18 : 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)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-07-18 : 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)

mrjack
Yak Posting Veteran

50 Posts

Posted - 2006-07-18 : 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 - 2006-07-18 : 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 - 2006-07-19 : 04:03:02

im stuck with cursor rite? hehehe...

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2006-07-19 : 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)

17689 Posts

Posted - 2006-07-19 : 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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources