Slowness in data transfer - 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

SQL Server 2008 Forums

Transact-SQL (2008)

Slowness in data transfer

Author

Topic

GustiX
Starting Member

28 Posts

Posted - 2012-09-20 : 05:09:20

Hi,

I am moving data between servers using cursors and it's pretty slow.
I wanted to see if you have any ideas on how I can improve it.

Basically what I am doing is:

create cursor as
select top 1000 rows from source where pulled = false (and some more conditions)
open cursor
fetch next
insert into destination db
update source db set pulled = true for this row
close cursor
delete from source db where pulled = true

The connection between the servers is ok but this still takes about 2 minutes for each 1000 rows which is
too slow because I need to pull ~ 1 million records every week

Ideas?

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2012-09-20 : 12:14:35

Why do you need a cursor for this?[CODE]begin tran

insert into destinationDB..MyTable(column list)
select top 1000 column list
from sourceDB..MyOtherTable
where pulled = 0 (and some other conditions)

update s
set pulled = 1
from sourceDB..MyOtherTable
where pulled = 0 (and some other conditions)

commit tran[/CODE]

=================================================
We are far more concerned about the desecration of the flag than we are about the desecration of our land. -Wendell Berry

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources