Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 948
  • Last Modified:

Cursor replacement


I have a stored procedure that I am trying to optimize. There is a cursor being use in the stored procedure that I am trying to figure out a better alternative.

Is there is a better way of processing these records without the use of this cursor. I'm concern that as we process more and more records going forward, I don't the stored procedure processing records one at a time.
DECLARE cur CURSOR READ_ONLY
	FOR SELECT * FROM #TempTriggerCandidate
	
	OPEN cur
	
	FETCH NEXT FROM cur INTO
		@Location_Id,
		@HouseHoldId,
		@CustomerId,
		@TriggerId2,
		@TriggerStatusId2,
		@Vin,
		@DealerDataId,
		@StartDate,
		@EndDate,
		@Merge_Birthdate
	
	WHILE @@FETCH_STATUS =0
	BEGIN
		
		--insert into TriggerCandidate
		INSERT INTO TriggerCandidate (Location_Id, HouseHoldId, CustomerId, TriggerId, TriggerStatusId, Vin, DealerDataId, BroadcastModeId, StartDate, EndDate, CreateDateTime)
							VALUES (@Location_Id, @HouseHoldId, @CustomerId, @TriggerId, @TriggerStatusId, @Vin, @DealerDataId, @BroadcastModeId, @StartDate, @EndDate, @IncludeDateTime)
		
		--get new TriggerCandidateId
		SELECT @NewTriggerCandidateId = SCOPE_IDENTITY()
		
		--insert into TriggerCandidateMergeField
		INSERT INTO dbo.TriggerCandidateMergeField (TriggerCandidateId,	[Key], Value) VALUES (@NewTriggerCandidateId, 'Birthdate', @Merge_Birthdate)
 
		--fetch next
		FETCH NEXT FROM cur INTO
			@Location_Id,
			@HouseHoldId,
			@CustomerId,
			@TriggerId2,
			@TriggerStatusId2,
			@Vin,
			@DealerDataId,
			@StartDate,
			@EndDate,
			@Merge_Birthdate
			
	END	
	
	CLOSE cur
	DEALLOCATE cur

Open in new window

0
jorbroni
Asked:
jorbroni
1 Solution
 
reb73Commented:
The trigger can be replaced if you follow the following pseudo-code template -

1) Create an identity column in #TempTriggerCandidate
2) Start an explicit transaction [BEGIN TRAN]
3) Reset the Identity seed value in #TempTriggerCandidate to match TriggerCandidate.TriggerCandidateId using DBCC CHECKIDENT(#TempTriggerCandidate, RESEED, <max value of TriggerCandidate.TriggerCandidateId>)

4) Populate the table #TempTriggerCandidate
5) SET IDENTITY_INSERT TriggerCandidate ON
6) Populate the table TriggerCandidate with explicity values for TriggerCandidateID  from #TempTriggerCandidate
7) Populate the table TriggerCandidateMergeField directly from #TempTriggerCandidate
8) SET IDENTITY_INSERT TriggerCandidate OFF
9) COMMIT TRAN


0
 
dqmqCommented:
IF TriggerCandidate has a natural key, then you can do it without a cursor.
--insert into TriggerCandidate
  INSERT INTO TriggerCandidate (Location_Id, HouseHoldId, CustomerId, TriggerId, TriggerStatusId, Vin, DealerDataId, BroadcastModeId, StartDate, EndDate, CreateDateTime)
  SELECT @Location_Id, @HouseHoldId, @CustomerId, @TriggerId, @TriggerStatusId, @Vin, @DealerDataId, @BroadcastModeId, @StartDate, @EndDate, @IncludeDateTime
  FROM #TempTriggerCandidate
		
 		
--insert into TriggerCandidateMergeField
INSERT INTO dbo.TriggerCandidateMergeField (TriggerCandidateId,	[Key], Value) 
  SELECT CandidateId, 'Birthdate', Merge_Birthdate
  FROM #TempTriggerCandidate TTC inner join TriggerCandidate TC 
    ON TTC.naturalkey = TC.naturalkey    --replace with join on actual key
 
		

Open in new window

0
 
jorbroniAuthor Commented:

Sorry, I'm not familiar with natural keys. Are they like composite keys?
0
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
dqmqCommented:
CandidateID (I'm guessing at the actual column name) is a surrogate key because it is generated by the Identity propery and has no meaning to your business.  The natural key is one or more columns besides that one that uniquely identify the row.  

To do this without a cursor, you need a natural key to join TriggerCandidate to the temp table in order to extract the surrogate key.  
0
 
corpuslabsCommented:

Insert into TriggerCandicate
Select Location_Id, HouseHoldId, CustomerId, TriggerId, TriggerStatusId, Vin, DealerDataId, BroadcastModeId, StartDate, EndDate, IncludeDateTime 
From #TempTriggerCandidate
 
Insert into TriggerCandidateMergeField
Select (Select IsNull(Max(TriggerCandidateMergeField),0) + 1) as Id 
From TriggerCandidateMergeField),'Birthdate', @Merge_Birthdate 
From  #TempTriggerCandidate

Open in new window

0
 
jorbroniAuthor Commented:
Thanks
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now