Alternative to Cursors for improving performance | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Alternative to Cursors for improving performance

All, I know there are many alternative’s to SQL Cursors, like temp Tables, derived tables, etc. Here is an example of the use of cursor in store procedure which takes hell lot of time, if there are large number of records to be inserted from the XML document. Here is the scenario this stored procedure below calls the following 2 spEMSInsertProposalFromXML, spEMSInsertIntoNestedTables (which in turn calls another 10 stored procs). All the above 13 stored procedure use cursors for inserting records into 12 different tables all together. I’m trying to improve the performance of this, probably using temp tables, but I don’t know the right way, I’m hoping that someone would help me with the best way possible: CREATEPROCEDURE spEMSInsertFromXMLData
@docXML text,
@UserID int
AS
DECLARE @docHandle int
DECLARE @lvProposalsRecCnt int
DECLARE @lvCID int
DECLARE @lvProposalID smallint
DECLARE @NewCID int
DECLARE @strError varchar(1000) EXEC sp_xml_preparedocument @docHandle OUTPUT, @docXML
[email protected] = CID
,@lvProposalID = ProposalID
FROMOPENXML(@docHandle, ‘/ROWDATA/ROW’, 1)
WITH Proposals
WHERE ProposalID = 0 — 0. Proposals
IF @lvCID < 0
BEGIN
EXEC spEZPGetNewID ‘Client’ , 1 , @[email protected] output EXEC spEMSInsertClient @docHandle, ‘/ROWDATA/ROW’, ‘/ROWDATA/ROW/Participant/ROWParticipant’, @NewCID END
ELSE BEGIN
EXEC spEMSDeleteClient @lvCID SET @NewCID = @lvCID — Insert Client
EXEC spEMSInsertClient @docHandle, ‘/ROWDATA/ROW’, ‘/ROWDATA/ROW/Participant/ROWParticipant’, @NewCID
END — Insert Proposals (current Plan) with CID & ProposalID
EXEC spEMSInsertProposalFromXML @docHandle, ‘/ROWDATA/ROW’, @UserID, @NewCID, @lvProposalID IF @@ERROR <> 0 GOTO HANDLEERROR
ELSE BEGIN
— ADD code to check-in nested tables data
EXEC spEMSInsertIntoNestedTables @docHandle, @NewCID, @lvProposalID
END
— For the remaining Plans/Proposals
DECLARE curXMLProposalsList CURSOR FOR
SELECTCID, ProposalID
FROMOPENXML (@docHandle, ‘/ROWDATA/ROW’, 1)
WITH Proposals xp
WHERE xp.ProposalID <> 0 — 0. Proposals
[email protected] = count(*)
FROMOPENXML(@docHandle, ‘/ROWDATA/ROW’, 1)
WITH Proposals xp
WHERE xp.ProposalID <> 0 IF @@ERROR = 0
BEGIN
IF @lvProposalsRecCnt > 0
BEGIN
OPEN curXMLProposalsList FETCH NEXT FROM curXMLProposalsList
INTO @lvCID, @lvProposalID — Insert Proposals with CID & ProposalID
EXEC spEMSInsertProposalFromXML @docHandle, ‘/ROWDATA/ROW’, @UserID, @NewCID, @lvProposalID IF @@ERROR <> 0 GOTO HANDLEERROR1
ELSE BEGIN
— ADD code to check-in nested tables data
EXEC spEMSInsertIntoNestedTables @docHandle, @NewCID, @lvProposalID
END SET @lvProposalsRecCnt = @lvProposalsRecCnt-1 WHILE (@lvProposalsRecCnt > 0)
BEGIN
FETCH NEXT FROM curXMLProposalsList
INTO @lvCID, @lvProposalID — Insert Proposals with CID & ProposalID
EXEC spEMSInsertProposalFromXML @docHandle, ‘/ROWDATA/ROW’, @UserID, @NewCID, @lvProposalID IF @@ERROR <> 0 GOTO HANDLEERROR1
ELSE BEGIN
— ADD code to check-in nested tables data
EXEC spEMSInsertIntoNestedTables @docHandle, @NewCID, @lvProposalID
END SET @lvProposalsRecCnt = @lvProposalsRecCnt-1
END CLOSE curXMLProposalsList
DEALLOCATE curXMLProposalsList
END
END
ELSE BEGIN
SET @strError = ‘Error while fetching Proposals Data from XML Document’
GOTO HANDLEERROR
END EXEC sp_xml_removedocument @docHandle RETURN @NewCID HANDLEERROR1:
ROLLBACK TRANSACTION
EXEC sp_xml_removedocument @docHandle
CLOSE curXMLProposalsList
DEALLOCATE curXMLProposalsList
RETURN -1 HANDLEERROR:
ROLLBACK TRANSACTION
EXEC sp_xml_removedocument @docHandle
RETURN -1
RAISERROR (@strError, 16, 1) Thanks in advance…
Amit
Since XML seems to force you to handle stuff one row at a time, you should consider converting the XML data into a holding table (or a variable of the TABLE type) and then work with the table data in set-based manner.
Hi ya, yes you definitely do not want to keep going through the XML for every thing. As Adriaan suggested put the data from the XML into a temporary table structure and manipulate it from there. You then have the option of doing insert/update/delete using selects from these table(s) I’ve used this myself to process order information from an XML submitted document, and it made a huge difference to performance Cheers
Twan
]]>