[SQL, PERFORMANCE] Add Equip to a char 80x faster!

Here we go, my 2nd and last release for this year.
Again, its performance based... after creating a small procedure and running it, i found out that _ADD_ITEM_EXTERN took god damn 80ms to add an item to a char... And yes, I'm using an SSD essentially plus you can imagine that my DB is optimized as fuck... Still took ages though.

So I looked into the _ADD_ITEM_EXTERN procedure and found out its completely useless. Yet again, Joymax failed.

So in case you've got a procedure running which requires adding equipment to an character.. use this standalone proc instead of joymaxs shiz which requires 4 procedures.

This one basically does the same thing but 40-80x faster.

Joymaxs shiz

Spoiler:

Code:

SQL Server Execution Times: CPU time = 15 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 16 ms, elapsed time = 11 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 4 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 5 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 5 ms. SQL Server Execution Times: CPU time = 16 ms, elapsed time = 26 ms. SQL Server Execution Times: CPU time = 31 ms, elapsed time = 29 ms. Total CPU Time: 78 ms Total Elapsed Time: 80ms

After optimization:

Spoiler:

Code:

SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. (1 row(s) affected) SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. (1 row(s) affected) SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. (1 row(s) affected) SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 1 ms.

Procedure:

Code:

USE [SRO_VT_SHARD] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE _RoyalAddEquipment @CharID INT, @ItemID INT, @Amount INT, @optLvl INT AS SET XACT_ABORT ON DECLARE @FreeSlot TINYINT, @link INT, @TiD1 TINYINT, @TiD2 TINYINT; SELECT @link = Link, @TiD1 = TypeID1, @TiD2 = TypeID2 FROM _RefObjCommon WHERE ID = @ItemID IF @link IS NULL BEGIN PRINT 'Item doesn''t exist' RETURN END IF NOT EXISTS(SELECT * FROM _Char WITH (NOLOCK) WHERE CharID = @CharID) BEGIN PRINT 'Char doesn''t exist' RETURN END SELECT TOP 1 @FreeSlot = Slot FROM _Inventory WHERE CharID = @CharID AND Slot > 12 AND ItemID = 0 IF @FreeSlot IS NULL BEGIN PRINT 'Inventory is full' RETURN END DECLARE @NewItemID BIGINT = 0; BEGIN TRAN IF @TiD1 = 3 AND @TiD2 = 1 BEGIN SELECT @Amount = Dur_L FROM _RefObjItem WHERE ID = @link DECLARE @LatestItemSN BIGINT 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) END IF @NewItemID = 0 BEGIN ROLLBACK TRAN PRINT 'Huge Error' RETURN END COMMIT TRAN

Re: [SQL, PERFORMANCE] Add Equip to a char 80x faster!

thanks akasch that would help a lot more SQL speed (joymax are gays xD )
thanks again i will use it :D

Re: [SQL, PERFORMANCE] Add Equip to a char 80x faster!

Quote:

Originally Posted by Muhab Ashraf

(joymax are gays xD )

i will leave silkroad if you can add avatar

Re: [SQL, PERFORMANCE] Add Equip to a char 80x faster!

Quote:

Originally Posted by fuddreckers2

i will leave silkroad if you can add avatar

lol , are u sure ?

Re: [SQL, PERFORMANCE] Add Equip to a char 80x faster!

don't forget to change ALTER PROCEDURE to CREATE PROCEDURE

Re: [SQL, PERFORMANCE] Add Equip to a char 80x faster!

Quote:

Originally Posted by Muhab Ashraf

lol , are u sure ?

Muhab Ashtaf : Hi Google

Google : Hi Muhab

Muhab Ashtaf : Please Google let Me know how to ADD Avatar to VSRO Server files

Google : Here You go......

Re: [SQL, PERFORMANCE] Add Equip to a char 80x faster!

Quote:

Originally Posted by adminsxza

don't forget to change ALTER PROCEDURE to CREATE PROCEDURE

If someone cant change that... then he's shouldn't even have a server. There are many idiots. This isn't meant to be for idiots.

Quote:

Originally Posted by B1QB0SS

Muhab Ashtaf : Hi Google
Google : Hi Muhab
Muhab Ashtaf : Please Google let Me know how to ADD Avatar to VSRO Server files
Google : Here You go......

Why are you even talking? It's not like you've got any more brain that that dude. The fact that your writing "to vsro server files" already explains how much of an idiot you are.

Re: [SQL, PERFORMANCE] Add Equip to a char 80x faster!

Quote:

Originally Posted by B1QB0SS

Muhab Ashtaf : Hi Google

Google : Hi Muhab

Muhab Ashtaf : Please Google let Me know how to ADD Avatar to VSRO Server files

Google : Here You go......

lol i don't need google help to add an avatar i can do it alone !
i have already added shitloads of items to my server so shut up please

Re: [SQL, PERFORMANCE] Add Equip to a char 80x faster!

Quote:

Originally Posted by Muhab Ashraf

lol i don't need google help to add an avatar i can do it alone !
i have already added shitloads of items to my server so shut up please

yeah prob by following tutorials from s4a or epvp :D he is talking about adding an avatar that was NOT made by joymax, he meant creating your own 3d model of an avatar and add it to the game :huh:

Re: [SQL, PERFORMANCE] Add Equip to a char 80x faster!

Quote:

Originally Posted by lemoniscool

yeah prob by following tutorials from s4a or epvp :D he is talking about adding an avatar that was NOT made by joymax, he meant creating your own 3d model of an avatar and add it to the game :huh:

seems u born as programmer or designer ! , everyone have to look for tutorials when he gonna start to learn smth or he must be have knowledge about the language he gonna work on
about creating 3d model, designing is not my hobby but if i learned that so why wouldn't i do it ?

Re: [SQL, PERFORMANCE] Add Equip to a char 80x faster!

Quote:

Originally Posted by Royalblade

Why are you even talking? It's not like you've got any more brain that that dude. The fact that your writing "to vsro server files" already explains how much of an idiot you are.

Vsro Server files Means The files you are using

so try to search for How to add avatar to ZSZC

you will find nothing

Re: [SQL, PERFORMANCE] Add Equip to a char 80x faster!

Quote:

Originally Posted by Muhab Ashraf

seems u born as programmer or designer ! , everyone have to look for tutorials when he gonna start to learn smth or he must be have knowledge about the language he gonna work on
about creating 3d model, designing is not my hobby but if i learned that so why wouldn't i do it ?

no actually i just started coding when i was 7 years old but thats not important. even if you would do 3d modeling as your hobby, you wouldnt get it into the game trust me ^^ also i didnt say following tutorials is bad, thats why we are writing them ... BUT in this conversation following tutrials isnt something you can win an award with ... just saying

Quote:

Originally Posted by B1QB0SS

Vsro Server files Means The files you are using

so try to search for How to add avatar to ZSZC

you will find nothing

youre not adding anything to the server files, youre adding to the database and the client data files, nothing else ... cmon you have to understand that now

Re: [SQL, PERFORMANCE] Add Equip to a char 80x faster!

Quote:

Originally Posted by lemoniscool

no actually i just started coding when i was 7 years old but thats not important. even if you would do 3d modeling as your hobby, you wouldnt get it into the game trust me ^^ also i didnt say following tutorials is bad, thats why we are writing them ... BUT in this conversation following tutrials isnt something you can win an award with ... just saying

youre not adding anything to the server files, youre adding to the database and the client data files, nothing else ... cmon you have to understand that now

and the database setup is on all serverfiles the same soo... if he dont understand this then srysl... reset your learning process

and begin from new :D

Re: [SQL, PERFORMANCE] Add Equip to a char 80x faster!

all egypt people attacked each other why all that ? before the fucking game ? i think we should respect each other

Re: [SQL, PERFORMANCE] Add Equip to a char 80x faster!

Quote:

Originally Posted by lemoniscool

no actually i just started coding when i was 7 years old but thats not important. even if you would do 3d modeling as your hobby, you wouldnt get it into the game trust me ^^ also i didnt say following tutorials is bad, thats why we are writing them ... BUT in this conversation following tutrials isnt something you can win an award with ... just saying

youre not adding anything to the server files, youre adding to the database and the client data files, nothing else ... cmon you have to understand that now

-.- I know that he's adding to the database

VSRO + database = VSRO Server files -.-

Re: [SQL, PERFORMANCE] Add Equip to a char 80x faster!

Quote:

Originally Posted by B1QB0SS

-.- I know that he's adding to the database

VSRO + database = VSRO Server files -.-

VSRO? Wuts VSRO huh? That's the games name.

VSRO Server Files AND VSRO Database are TWO different things. But never expected you to know that either.

Re: [SQL, PERFORMANCE] Add Equip to a char 80x faster!

Quote:

Originally Posted by gotsha

all egypt people attacked each other why all that ? before the fucking game ? i think we should respect each other

try again: http://prntscr.com/3i6cms

Quote:

Originally Posted by B1QB0SS

-.- I know that he's adding to the database

VSRO + database = VSRO Server files -.-

LOL seriously? think about it for a second ... and then correct me if i say: that makes no sense at all

anyways .. back to topic:
nice stored proc, works like a charm, and as always a good thread for flaming started by you royalblade :D

Re: [SQL, PERFORMANCE] Add Equip to a char 80x faster!

i know i don't say this to you i say this to Muhab Ashraf And
B1QB0SS
and
fuddreckers2

- - - Updated - - -

i know i don't say this to you i say this to Muhab Ashraf And
B1QB0SS
and
fuddreckers2

Re: [SQL, PERFORMANCE] Add Equip to a char 80x faster!

Why you're just creating a new Procedure instead of rewriting the old one (_ADD_ITEM_EXTERN), this would increase the Servers performance way more?

Re: [SQL, PERFORMANCE] Add Equip to a char 80x faster!

I essentiall did re-write it.

Re: [SQL, PERFORMANCE] Add Equip to a char 80x faster!

Royal can you give me Tip
i want to make this script from 1 til 8 Sun FB MAX 61% AND D9 items Seal oF moon

Re: [SQL, PERFORMANCE] Add Equip to a char 80x faster!

Quote:

Originally Posted by dafuq99

Royal can you give me Tip
i want to make this script from 1 til 8 Sun FB MAX 61% AND D9 items Seal oF moon

thanks for this post, really!
you made my day a bit better today .. had a good laugh =)

Re: [SQL, PERFORMANCE] Add Equip to a char 80x faster!

Quote:

Originally Posted by dafuq99

Royal can you give me Tip
i want to make this script from 1 til 8 Sun FB MAX 61% AND D9 items Seal oF moon

Yes, i can give you a tip. However, first complete this: http://psychologytoday.tests.psychte...idRegTest=3040
After passing it with a positive result, you can ask again. Oh, I might ask you to visit another test page to check your english before I do actually help you.

In case you don't pass, there are multiple mental institutions in your city, just checked, those will take you in just after a quick look. There are many retards there, you can join em officially. At least, then I'll feel pity instead of disgust.

Re: [SQL, PERFORMANCE] Add Equip to a char 80x faster!

Quote:

Originally Posted by Royalblade

Yes, i can give you a tip. However, first complete this: http://psychologytoday.tests.psychte...idRegTest=3040
After passing it with a positive result, you can ask again. Oh, I might ask you to visit another test page to check your english before I do actually help you.

In case you don't pass, there are multiple mental institutions in your city, just checked, those will take you in just after a quick look. There are many retards there, you can join em officially. At least, then I'll feel pity instead of disgust.

i quit vSRO developeing

Re: [SQL, PERFORMANCE] Add Equip to a char 80x faster!

hey guys.
this release work or no ? :D