Geek Answers Handbook

Is the cursor the only alternative for this kind of operation

I am trying to optimize a long transaction, and I have seen that the following is executed several times:

Declare @myCursor CURSOR FAST_FORWARD FOR SELECT field1, MIN(COALESCE(field2, -2)) FROM MyTable tempfact LEFT JOIN MyTable sd ON tempfact.ID = sd.ID AND sd.TransactionId = @transactionId WHERE tempfact.SomeField IS NULL AND tempfact.TransactionId = @transactionId GROUP BY tempfact.field1 OPEN @myCursor FETCH NEXT FROM @myCursor INTO @field1Variable, @field2Variable WHILE @@FETCH_STATUS = 0 BEGIN EXEC USP_SOME_PROC @field1Variable, @field2Variable FETCH NEXT FROM @myCursor INTO @field1Variable, @field2Variable END CLOSE @myCursor DEALLOCATE @myCursor

The code for USP_SOME_PROC sproc is as follows:

 IF NOT EXISTS (SELECT * FROM SomeTable WHERE Field1 = @field1) BEGIN INSERT INTO SomeTable (Field1, Field2) VALUES (@field1, @field2) END

As I mentioned, this is done in quite a few places, tables and fields are related to each other, but the idea remains the same, and I'm sure there can be a way to increase the performance of these sprocs if the cursors are not and, possibly, by speeding up this transaction , the problem that we have with a dead end (topic for another message) can be solved.

+4
source share
3 answers

You can use MERGE for this

 ;WITH Source AS ( SELECT field1, MIN(COALESCE(field2, -2)) as field2 FROM MyTable tempfact LEFT JOIN MyTable sd ON tempfact.ID = sd.ID AND sd.TransactionId = @transactionId WHERE tempfact.SomeField IS NULL AND tempfact.TransactionId = @transactionId GROUP BY tempfact.field1 ) MERGE SomeTable AS T USING Source S ON (T.Field1 = S.Field1) WHEN NOT MATCHED BY TARGET THEN INSERT (Field1, Field2) VALUES (field1, field2) ;
+4
source

I did not have the opportunity to verify this, but it should be close: you need to insert from the SELECT statement, but you also need to make sure that the corresponding record does not yet exist in SomeTable

 INSERT INTO SomeTable (Field1, Field2) SELECT field1, MIN(COALESCE(field2, -2)) FROM MyTable tempfact LEFT JOIN MyTable sd ON tempfact.ID = sd.ID AND sd.TransactionId = @transactionId LEFT JOIN SomeTable st ON st.Field1 = tempfact.field1 WHERE tempfact.SomeField IS NULL AND tempfact.TransactionId = @transactionId AND st.Field1 IS NULL GROUP BY tempfact.field1
+1
source

You do not need to have a cursor and you can use the volume insertion logic as shown below

 INSERT INTO SomeTable (Field1, Field2) SELECT field1, MIN(COALESCE(field2, -2)) FROM MyTable tempfact LEFT JOIN MyTable sd ON tempfact.ID = sd.ID AND sd.TransactionId = @transactionId WHERE tempfact.SomeField IS NULL AND tempfact.TransactionId = @transactionId GROUP BY tempfact.field1

Hope this helps!

0
source

More articles:


All Articles