SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Alternative to cursor..
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mrjack
Yak Posting Veteran

50 Posts

Posted - 07/18/2006 :  03:01:43  Show Profile  Reply with Quote
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
17635 Posts

Posted - 07/18/2006 :  03:06:04  Show Profile  Reply with Quote
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

Go to Top of Page

mrjack
Yak Posting Veteran

50 Posts

Posted - 07/18/2006 :  03:09:40  Show Profile  Reply with Quote
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
Go to Top of Page

mrjack
Yak Posting Veteran

50 Posts

Posted - 07/18/2006 :  03:18:58  Show Profile  Reply with Quote
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
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17635 Posts

Posted - 07/18/2006 :  03:23:58  Show Profile  Reply with Quote


-- 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

Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17635 Posts

Posted - 07/18/2006 :  03:41:26  Show Profile  Reply with Quote
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

Go to Top of Page

mrjack
Yak Posting Veteran

50 Posts

Posted - 07/18/2006 :  03:50:55  Show Profile  Reply with Quote
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..
Go to Top of Page

mrjack
Yak Posting Veteran

50 Posts

Posted - 07/18/2006 :  04:13:44  Show Profile  Reply with Quote
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)
Go to Top of Page

mrjack
Yak Posting Veteran

50 Posts

Posted - 07/19/2006 :  04:03:02  Show Profile  Reply with Quote
im stuck with cursor rite? hehehe...
Go to Top of Page

AndrewMurphy
Flowing Fount of Yak Knowledge

Ireland
2916 Posts

Posted - 07/19/2006 :  07:38:35  Show Profile  Reply with Quote
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!!
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17635 Posts

Posted - 07/19/2006 :  07:50:00  Show Profile  Reply with Quote
"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

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000