[Theory] For Developers + People with a brain.

Here is a theory about the database -> Targeting _Items in Shard.

Problem:

My Server currently has over 13million items in the _Items table, which is fucking insane considering we have only been on for 1 month but with over 120,000 characters + 40,000 users.


Now what i found is that these files are seriously lacking performance and incomplete, and there is no way any server with that many items in the database will say "We have 0% lag" because thats just bull shit and fake! And i assume im not the only one who realized this..



Solution:

- I Already modified 40% of my files stored procedures for better performance, but thats not good enough. So here is my real solution.


Step 1) Remove Items from the database when a user drops them [100%]
Step 2) Remove Items from the database when a char is deleted [100%]
Step 3) Remove Items from the database where items dont exist in "Pet inventory / Inventory / Guild ware house / Ware house" [100%]


I would like to hear some thoughts about this?

Quote:

Originally Posted by jangan322 Here is a theory about the database -> Targeting _Items in Shard. Problem: My Server currently has over 3million items in the _Items table, which is fucking insane considering we have only been on for 1 month but with over 120,000 characters + 40,000 users. Now what i found is that these files are seriously lacking performance and incomplete, and there is no way any server with that many items in the database will say "We have 0% lag" because thats just bull shit and fake! And i assume im not the only one who realized this and there are few people who are smart around here. Solution: - I Already modified 40% of my files stored procedures for better performance, but thats not good enough. So here is my real solution. Step 1) Remove Items from the database when a user drops them [40%] Step 2) Remove Items from the database when a char is deleted [10%] Step 3) Remove Items from the database where items dont exist in "Pet inventory / Inventory / Guild ware house / Ware house" [0%] I would like to hear some thoughts about this?

i think is a good idea
maybe items from chars that where deleted time ago, not immediately
but is a good idea

Better find a solution for the thing that causes that. If you have 3m items in database its probably because you start a char with about 100 items and when people just create new chars to get new nova+5 weapons to pimp their gear thats how you get stacked so much items.
So maybe make wipe for existing problem and add nova items to npc so you wont get 40k accounts and such problem?

well, every item that any monster in the game drops, still exists in game... it never gets removed.

Only isro and servers like zszc remvoed items from the game that dont get used. THis is a problem for all VSRO files :P

But look, if you start with clean inventory and get items from npc you wont get anywhere near 3m items again in long time.

Quote:

Originally Posted by Nova1337 But look, if you start with clean inventory and get items from npc you wont get anywhere near 3m items again in long time.

yeh, but will soon happen
thats why, he is interested on finding a way to totally remove unused items

What you are saying, though being a good idea, is not that easy to do.

From here on, take in account I have never seen any file nor DB of SRO in action.

The best approach to do what you are proposing would be a C++ (or C#) application which looped through the entire items table and cheking up spawn date + ownership deleted them or not.
Until here it is "kinda easy" to do, the problems come now:
1. If you delete an item from the DB which is just being used by the server, it would crash.
2. If you delete an item from DB but server keeps it in memory and further requests are made to the item, server would keep acting as the item existed while it didn't
3. If a player is next to the item when deleted from DB, the player could still view the item (no despawn packet could be send at all) though item didn't exist.
4. Anything like those 3.

The only solution to the above problems would be doing maintainance to the server each X days, and then restarting the server. But as you may understand that would imply not being able to despawn items at the moment (just as isro and so).

Hope this is useful =)

I cant create search loops with 1000 people on... that will destroy the server :P


Although you raise some interesting problems, Well, hopefully im right by saying this statment "i know 100% how to remove an item from the game".


My issues comes with after removing the unsed items, which is being scared of getting duplicates but i monitored how the game adds items to the database, and it doesnt fill holes, it justs continues with the Primary Counter Key.

joymax had a reason to do that. If it's causing any stability or speed issue then you're doing something wrong. MSSQL is extremely scalable, do it :)

Hehe its scary, i will have to spend few days making the perfect query so i dont destroy the database xD

The Perfection Network has found the solution to this problem. See the following post from Jangan for more info.

WE DID IT!!!!!!!!!!!!!!!!!

We removed 7 million items from the game! and fixed every VSRO problems! and We are the first to do it!!!

Now what happens:
- If you delete a character, all items related will be deleted
- If you drop an item and it disappears, it will be deleted from the game.


Mission successful, no lags, no duplicates, no holes!!!

would you share the way you did it :p ?


i'm really interested in vsro developing

Tradesman secret at the moment

Quote:

Originally Posted by LastThief would you share the way you did it :p ? i'm really interested in vsro developing

Come on skype more often <3 i really wanna talk to you lol