Cursor alternative?

[joejack0330]

We seem to be having problems all of a sudden with a cursor locking up other users and taking a lot longer than it use too. We are dealing with small amounts of data being paased back but every where I read, it says not to use cursors so wanted to see if someone could help us a little with a different way to do the following. It seems to be the in the declare and select that is the issue because we logged some begin and end times and have it narrowed down to here so not the process where we fetch and do some other code. If we run select by itself, it seems fine and tables seem good so kind of stuck. Thanks, Joe

DECLARE SecondaryIDsInResidualZone CURSOR FAST_FORWARD FOR
SELECT tbllpa_HeaderLabelID
FROM tblLabelPalletAisles
LEFT JOIN tblLabelPalletCombinations ON
tbllpc_HeaderID = tbllpa_HeaderLabelID AND
tbllpc_CombinationNumber = @PalletCombinationNumber
WHERE tbllpa_Facility = @Facility AND
tbllpa_ResidualZone = @ResidualZone AND
tbllpa_ColumnNumber IS NULL AND
tbllpa_HeaderLabelID NOT IN (@PrimaryPalletIDPrior, @PrimaryPalletID) AND
ISNULL(tbllpc_PrimaryPallet,2) <> 1
GROUP BY tbllpa_HeaderLabelID
ORDER BY COUNT(*)

 

[SQLSister]

what does the cursor itself do? It isn't the select that is the problem with a cursor, it is that the operation carried out by the cursor happens one row at a time instead of operating onthe whole set. The set-based solution is different depending onwhat action the cursor takes not the select.

"NOTHING is more important in a database than integrity." ESquared

 

[bborissov]

The solution depends of FOR WHAT you use that cursor next.
The cursor definition look great, but for what it is used ???

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[joejack0330]

Sorry, I copied and pasted and must have missed some. I think I found some help online at this link but I'll paste the rest of my code in case there is a better way or it helps someone else. Thanks, Joe

http://www.sqlbook.com/SQL/Avoiding-using-SQL-Cursors-20.aspx

-----------------------------------------------------

OPEN SecondaryIDsInResidualZone
FETCH NEXT FROM SecondaryIDsInResidualZone INTO
@SecondaryPalletID
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO tblLabelPalletCombinations (
tbllpc_HeaderID,
tbllpc_CombinationNumber,
tbllpc_PrimaryPallet)
VALUES (@SecondaryPalletID,
@PalletCombinationNumber,
3)
SELECT COUNT(*)
FROM tblLabelPalletCombinations
INNER JOIN tblLabelPalletAisles ON
tbllpa_HeaderLabelID = tbllpc_HeaderID
WHERE tbllpc_CombinationNumber = @PalletCombinationNumber AND
tbllpc_PrimaryPallet <> 2
GROUP BY tbllpa_ZoneAndAisle
SELECT @AilsesForThisCombination = @@ROWCOUNT
SELECT COUNT(*)
FROM tblLabelPalletCombinations
INNER JOIN tblLabelPalletAisles ON
tbllpa_HeaderLabelID = tbllpc_HeaderID
WHERE tbllpc_CombinationNumber = @PalletCombinationNumber AND
tbllpc_PrimaryPallet <> 2
GROUP BY tbllpa_HeaderLabelID,
tbllpa_ZoneAndAisle
SELECT @TotalAislesForThisCombination = @@ROWCOUNT
SET @BestPercentageWork = @TotalAislesForThisCombination /
((@ResidualPalletCountWork + 1) * @AilsesForThisCombination)
IF @ResidualPalletCountWork + 1 = @ResidualPalletCount AND
@BestPercentageWork = 1 --100% Match
BEGIN
DELETE tblLabelPalletCombinations
WHERE (tbllpc_PrimaryPallet = 2 AND
tbllpc_CombinationNumber = @PalletCombinationNumber) OR
(tbllpc_CombinationNumber <> @PalletCombinationNumber)

END
FETCH NEXT FROM SecondaryIDsInResidualZone INTO
@SecondaryPalletID
END
CLOSE SecondaryIDsInResidualZone
DEALLOCATE SecondaryIDsInResidualZone

 

[PDreyer]

Some variables used are not defined or initialized in the code posted e.g.
@ResidualPalletCountWork
@ResidualPalletCount
And this delete does not make sense to me with the OR in there
[tt]
DELETE tblLabelPalletCombinations
WHERE (tbllpc_PrimaryPallet = 2 AND
tbllpc_CombinationNumber = @PalletCombinationNumber) OR
(tbllpc_CombinationNumber <> @PalletCombinationNumber)
[/tt]

 

[SQLSister]

OK I'm making some big assumptions here (including that somewhere else in the code those variables @ResidualPalletCountWork and @ResidualPalletCount are defined). But it might give you some food for thought as to how to approach this from a set-based perspective instead of using a cursor. Don't use this code without a test to see if it is giving you the correct results as I could think of several interpretations as to what you are trying to do. I threw in some commented code to hel psee if this is returning the results you expect. You can also comment out the insert part and just run the selct to see what would be inserted into your table.

INSERT INTO    tblLabelPalletCombinations (  tbllpc_HeaderID,tbllpc_CombinationNumber, tbllpc_PrimaryPallet)             SELECT    tbllpa_HeaderLabelID, tbllpc_CombinationNumber, 3
	FROM     tblLabelPalletAislesLEFT 
	JOIN tblLabelPalletCombinations ON tbllpc_HeaderID = tbllpa_HeaderLabelID 
	WHERE    tbllpa_Facility = @Facility AND tbllpa_ResidualZone = @ResidualZone AND tbllpa_ColumnNumber IS NULL AND tbllpa_HeaderLabelID NOT IN (@PrimaryPalletIDPrior, @PrimaryPalletID) AND ISNULL(tbllpc_PrimaryPallet,2) <> 1
	GROUP BY tbllpa_HeaderLabelID
	ORDER BY COUNT(*)          

	select lpc.tbllpa_HeaderLabelID, a.AislesForThisCombination, a.llpc_CombinationNumber ,a.tbllpa_ZoneAndAisle,
	   b.TotalAislesForThisCombination
	into #temp 
	from tblLabelPalletCombinations lpc
	JOIN
	 (SELECT COUNT(*) as AislesForThisCombination, llpc_CombinationNumber ,tbllpa_ZoneAndAisle         
	 FROM    tblLabelPalletCombinations        
	 INNER JOIN tblLabelPalletAisles ON tbllpa_HeaderLabelID = tbllpc_HeaderID       
	  WHERE      tbllpc_PrimaryPallet <> 2        
	  GROUP BY tbllpa_ZoneAndAisle,tbllpc_CombinationNumber) a  
	  on lpc.llpc_CombinationNumber = a.llpc_CombinationNumber 
		and lpc.llpc_CombinationNumber = a.llpc_CombinationNumber     
	JOIN    
	 (SELECT COUNT(*)  as TotalAislesForThisCombination, tbllpa_ZoneAndAisle ,tbllpa_HeaderLabelID  
	 FROM    tblLabelPalletCombinations        
	 INNER JOIN tblLabelPalletAisles ON  tbllpa_HeaderLabelID = tbllpc_HeaderID    
	 WHERE     tbllpc_PrimaryPallet <> 2        
	 GROUP BY tbllpa_HeaderLabelID, tbllpa_ZoneAndAisle ) b on lpc.tbllpa_HeaderLabelID = b.tbllpa_HeaderLabelID   
	 
	 --select * from #temp
	            
	 DELETE lpc
	 --select lpc.*
	 From tblLabelPalletCombinations   lpc         
	 JOIN #temp t on lpc.tbllpa_HeaderLabelID = t.tbllpa_HeaderLabelID
	 WHERE    TotalAislesForThisCombination /((@ResidualPalletCountWork + 1) * AislesForThisCombination) = 1  
	 AND @ResidualPalletCountWork + 1 = @ResidualPalletCount
	 AND 
	 ((lpc.tbllpc_PrimaryPallet = 2 AND lpc.tbllpc_CombinationNumber = t.llpc_CombinationNumber) 
		OR (lpc.tbllpc_CombinationNumber <> t.llpc_CombinationNumber))

"NOTHING is more important in a database than integrity." ESquared