CODEWITHSUNDEEP
sqlsql-serversql-server-2005cursorlinked-server
Rauf
Rauf

Reputation: 12842

Stored Procedure is too slow (using CURSOR )

I have the below code to insert the unprocessed (processed=0) records from server 1 to server 2 (using linked servers), and once inserted it should be updated to processed=1

I was just using

Query 1:

INSERT INTO SELECT FROM WHERE processed=0
UPDATE processed=1 WHERE processed=0

Query 2:

DECLARE pending_records CURSOR LOCAL FOR
SELECT FROM WHERE processed=0

OPEN pending_records

FETCH NEXT FROM pending_records INTO @UniqueID

WHILE @@FETCH_STATUS=0
BEGIN

INSERT INTO SELECT FROM WHERE UniqueID=@UniqueID

IF @@ROWCOUNT=1 .... UPDATE processed=1 WHERE UniqueID=@UniqueID

FETCH NEXT FROM pending_records INTO @UniqueID

END

CLOSE pending_records

DEALLOCATE pending_records

Query 1 is super fast and Query using cursor is too slow ( takes 30 seconds to updates 1 records)

I stay away from Query 1 because if there is any failure in database, it will effect the records. Note: I cannot use DISTRIBUTED TRANSACTION right now because it needs additional setup.

Upvotes: 0

Views: 790

Answers (2)

galloleonardo
galloleonardo

Reputation: 154

Have you tried using the 'FAST_FORWARD' argument?

The 'FAT_FORWARD' specifies the FORWARD_ONLY, READ_ONLY cursor with performance optimizations enabled. FAST_FORWARD can not be specified if SCROLL or FOR_UPDATE is also specified.

Read more here.

Upvotes: 1

user8527410
user8527410

Reputation: 493

You could try this: add an extra column to your table where you put an extra processing-flag. Generate a GUID at the beginning of your stored procedure and update your table:

UPDATE <table>
SET processing_flag = <GUID>
WHERE processed = 0;

And then you can simply transfer your rows to the other server by

INSERT INTO <target>
SELECT <columns>
FROM <source>
WHERE processed = 0 AND processing_flag = <GUID>;

After this you can set processed = 1 and erase the processing_flag. If something Fails you can select all not transfered rows by processed = 0 and processing_flag != NULL.

I had a similar problem with transfering single rows. By putting them all in one my problem was solved.

Maybe your target-server or the connection is too slow too.

Upvotes: 0

Related Questions