[SQL, PERFORMANCE, Proc]_Roya_AddItems_Fast

[Royalblade*]

huhuz, I feel like releasing smtn again and since simulation asked me to be nicer i won't write all the crap i usually write.

What is this?

It's a re-write of the procedure _ADD_ITEM_EXTERN.

Why is this better?
Because it's 80x+ faster than the original one.

So why would i use this? I barely ever add items!
Then don't use it. It's meant for people who have custom features that'd like to add items to characters without losing precious cpu time.

How to use it?
EXEC _ProcNameHere CharID*Here, _RefObjCommon.ID*Here, Amount*Here, Plus*here

No work menz?!?
Suicide is an option.


Procedure:

CREATE PROCEDURE [dbo].[_RoyalAddItems]
@CharID INT,
@ItemID INT,
@Amount INT,
@OptLvl INT
AS
SET XACT_ABORT ON
DECLARE	@FreeSlot TINYINT,
		@Link INT,
		@LatestItemSN BIGINT,
		@NewItemID BIGINT = 0;
 
SELECT	@Link = R.Link,
		@Amount =	CASE	
						WHEN TypeID1 = 3 AND TypeID2 = 1
							THEN Dur_L
						WHEN TypeID1 = 3 AND TypeID2 = 2 AND TypeID3 = 1 AND TypeID4 IN(1,2)
							THEN 0
						WHEN @Amount > RI.MaxStack OR @Amount < 0
							THEN MaxStack
						ELSE @Amount
					END
FROM	_RefObjCommon R
JOIN	_RefObjItem RI	ON R.Link = RI.ID
WHERE	R.ID = @ItemID

IF @Link IS NULL OR @Amount IS NULL
		RETURN -4 -- Self-explanatory

	
IF NOT EXISTS(SELECT * FROM _Char WITH (NOLOCK) WHERE CharID = @CharID)
		RETURN -3 -- Character doesnt exist.

SELECT TOP 1 @FreeSlot = Slot
FROM	_Inventory
WHERE	CharID = @CharID
AND		Slot > 12
AND		ItemID = 0

IF	@FreeSlot IS NULL
		RETURN -2 -- Inventory is full.


BEGIN TRAN
BEGIN TRY

UPDATE _LatestItemSerial SET LatestItemSerial += 1;

SELECT @LatestItemSN = LatestItemSerial 
FROM _LatestItemSerial 
WITH (UPDLOCK)

INSERT INTO _Items (RefItemID, OptLevel, Data, MagParamNum, Serial64) 
VALUES (@ItemID, @OptLvl, @Amount, 0, @LatestItemSN)
SELECT	@NewItemID = @@IDENTITY

UPDATE _Inventory SET ItemID = @NewItemID WHERE CharID = @CharID AND Slot = @FreeSlot

INSERT INTO _ItemPool (InUse, ItemID)
VALUES		(1,@NewItemID)
		
IF	@NewItemID = 0
	BEGIN
		ROLLBACK TRAN
		RETURN
	END
END TRY
BEGIN CATCH -- You can remove the try n catch block. This should always work.. I just wanted to be sure.
	ROLLBACK TRAN
	RETURN 
END CATCH
	
COMMIT TRAN

[Callum]

#approved

[Lisias]

Thanks

wieder mal alles rasiert hahaha

[bares1993]

Akasch wieder mal am rasieren

[CallM3B1tch]

THANKS