Help With Stored Procedures (low Performance)

Hi, im developing an action site and im having proplems with stored procedures


that are causing extreme load on the server.





PLEASE LOOK AT THE CODE AND TELL ME IF U SEE ANYTHING THAT CAUSES LOW PERFORMANCE OR SOME SUGGESTIONS ON HOW TO INCREASE THE PERFORMANCE.


SHOULD I F.E. TAKE THIS LOGIC FROM THE DB AND TRY DOING IT IN c#?





The action-site uses an auto-bid-feature that is causing a load.


Basically when a user makes a bid, i have to loop through all other users that have a bid in


the product and automatically increase a bid untill it matches the highest bid of a user.





F.e. a user bids auto-bid $2000 in a product where another user has a $2100 bid in the product and the current bid is $1200. So the SP loops through the users increases the bid-history untill it mathces the highest bid of a user.





- $1250


- $1300


- $1350


- etc...





This is the SP that loops through the users and calls another function each time to increase the bid in the bid-history.





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





CREATE PROC HaekkaBodIUppbodi


@uID INT


AS





DECLARE @curUpphaed DECIMAL


SELECT @curUpphaed = MAX(upphaed) FROM bodsaga WHERE uppbod_ID = @uID





IF(@curUpphaed IS null)


BEGIN


SET @curUpphaed = 0


END





DECLARE @curCount INT


SELECT @curCount = COUNT(kaupandi_netfang) FROM bod WHERE upphaed >


@curUpphaed AND uppbod_ID = @uID





WHILE(@curCount>0)


BEGIN


-- skilgreinum notanda breytu


DECLARE @notandi NVARCHAR(255)





IF(@curCount<2)


BEGIN


SELECT @notandi = kaupandi_netfang FROM bod WHERE upphaed >


@curUpphaed AND uppbod_ID = @uID





EXEC HaekkaBodAVoru @uID, @notandi





BREAK;


END





-- búum til cursor fyrir alla notendur


-- sem eru með hærri boð en núverandi boð


DECLARE crs CURSOR FAST_FORWARD FOR


SELECT kaupandi_netfang FROM bod WHERE upphaed > @curUpphaed


AND uppbod_ID = @uID


ORDER BY dags





-- opnum cursorinn


OPEN crs





-- sækjum fyrsta notandann inn í @notandi


FETCH NEXT FROM crs


INTO @notandi





DECLARE @returnValue INT


SET @returnValue = 0





DECLARE @tempValue INT


SET @tempValue = 0





-- lúppum í gegnum notendurna


WHILE @@FETCH_STATUS = 0


BEGIN


EXEC HaekkaBodAVoru @uID, @notandi





FETCH NEXT FROM crs


INTO @notandi


END





CLOSE crs


DEALLOCATE crs





-- sækjum hæstu upphæð í boðsögu


SELECT @curUpphaed = MAX(upphaed) FROM bodsaga WHERE uppbod_ID = @uID





SELECT @curCount = COUNT(kaupandi_netfang) FROM bod WHERE upphaed >


@curUpphaed AND uppbod_ID = @uID





END





GO








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


And here is the SP that increases the bid...











CREATE PROC HaekkaBodAVoru


@uID INT,


@notandi NVARCHAR(255)


AS





-- Hækkun á uppboði


DECLARE @haekkun DECIMAL


SELECT @haekkun = haekkun_upphaed


FROM uppbod WHERE id = @uID





-- Hæsta boð í vöru


DECLARE @haesta_bod DECIMAL


SELECT @haesta_bod = MAX(upphaed) FROM bodsaga


WHERE uppbod_id = @uID





-- Hæsta boð sem notandi vill gera


DECLARE @haesta_bod_notanda DECIMAL


SELECT @haesta_bod_notanda = upphaed FROM


BOD WHERE kaupandi_netfang = @notandi





-- Seinasti notandi sem gerði boð í uppboðinu


DECLARE @seinasti_notandi NVARCHAR(255)


SELECT @seinasti_notandi = notandi FROM


BODSAGA WHERE uppbod_id = @uID





DECLARE @upphaed DECIMAL


SET @upphaed = @haekkun





-- Ef búið að bjóða í vöruna


IF(@haesta_bod IS NOT null)


BEGIN


IF(@haesta_bod = @haesta_bod_notanda)


BEGIN


SET @upphaed = @haesta_bod


END


ELSE


BEGIN


SET @upphaed = @haesta_bod + @haekkun


END


END





-- Ef hæsta boð notanda er sama eða lægra en núverandi verð


-- og notandi var ekki sá síðasta til þess að setja inn boð


IF(@upphaed <= @haesta_bod_notanda)


BEGIN


-- Ef notandi hefur EKKI sett inn þessa upphæð áður...


IF((SELECT COUNT(*) FROM bodsaga WHERE notandi = @notandi AND upphaed = @upphaed)=0


-- Og notandi á EKKI núverandi hæsta boð


AND (SELECT COUNT(*) FROM bodsaga WHERE notandi = @notandi AND upphaed = @haesta_bod)=0)


BEGIN


INSERT INTO bodsaga


VALUES(@uID, @notandi, @upphaed, GETDATE())





RETURN 1


END


END





RETURN 0











GO