Merchant Archive

Ok, so reading through all of these posts. It's obvious that 99.9% of the people out there have no concept of a database. So I will try and lightly go over some basics on databases, and why, now after reading all of the posts on this forum, SOE is actually putting caps on vendors.

First, let me say that I am going to make a lot of assumptions here. These figures are not based on anything, but just to get the point across.

One misconception there is is that databases are unlimited in size. At least with SQL servers (not sure what SOE uses) you have to declare what the size of the database will be. You can change the size of the database, but that requires taking down the database and making the changes. This change can sometimes take some time. Second. Databases can in reality grow to a max of the hard drive size. Of course, you never want to do that, but that is another limitation. Third, you generally never want a database to grow to even half the size of the allotment. once this happens, then any actions you take on the database will slow down considerably, and it will increase the chances of failures.

Now say you have a database the size of probably what we are talking about with SOE. Inherently, as a database grows, the queries used to update and to search a database grows. you can do a search on a 1000 row document a lot quicker than you can on a 1 million row document. You may not notice it, but there is a gap between the two. Have you ever wondered why you get so much lag when you hit populated areas. Or even when you sometimes enter a house, and it takes a while for those items to show up?

Now the current situation with the vendor item caps. I am going to make the assumption that since the beginning, it was planned that players would be limited to the number of items that they could have. This is based on houses having caps, factories having caps, inventory having caps, and banks having caps.

So say we have 100 people on a server. If these people maxed out their capacity. Then they would each have 100 items in the bank, 100 items in personal inventory, and assuming that they have 10merchant tents, another 1,000 items So each person would have a max of some 1,200 items. So that would total some 120,000 rows for all the items in inventory and storage.

And for those that are saying that a backpack only takes up one slot, so even if full, it should only count as 1 item in your storage space. Even though it's a single backpack, you still have 50 items inside. And each item is an individual piece in a database. That's the main reason why there are serial numbers on each item. (That and it helps to prevent duping of items) So even though it's one space. it's still taking 51 spaces on the database. And why it still takes 51 items in a house.

So now we get to vendors. Say of the 100 people, only 20% have a vendor. And say theymax and have6 vendors each. So that would total to a total of 120 vendors. Now each vendor is unlimited. But for sake, let's say that each vendor has 1,000 items. (Since everyone that is complaining that they don't have enough storage space since they have 1-2k of items per vendor) So that 10% of the community now is taking up 120,000 extra rows in the database. So that 20% of the community isequaling the number of rows on the database as 80% of the community.

Now after this cap is institued. I have heard that a while back, the stats showed that 2% of the community was merchant on one server. So we'll use that figure. That would be 2 people in a community of 100. They have 6 vendors. Those total 12 vendors, and they are capped at 110. So that leaves a total of 1,320 items that they can have. This would equate to a saving in database space of 98.9%.

finally, think about how many people are on each server. Say there are 10 shuttle ports per planet. You have 5 planets inhabited, give or take. So that's 50 cities (major cities) at 55 people per. So that is just in these cities, and not including all the small cities, and other players not in a city of on the low end 2,750 people per server. You apply those same figures to a base allotable of 3.3 million. With a possible current vendor structure of 3.3 million. That would mean a total row count just to manage our inventory of 6.6 million rows. But after the proposed change, that number would fall to a vendor structure of 36,300 items using the 2% figure. So instead of 6.6 million rows, you now have 3.37 million rows. Simplistically, say each row comprises of a serial number and a identifier for the player. Minimally, you would have a 4 digit number for the player and I think it's a 7 digit code for the item. So that is 11 bytes minimally per row. (It's probably more like 1100 bytes) So the difference between now and then is 72.6 million bytes or 37.07 million bytes. Don't remember how many bytes equates to what. I think a megabyte is 1 million bytes?

Add to that, this 6.6 million rows is constantly being changed and updated with deletions, and additions and changes to owners/uses.

With data sizes this large, more use means more chances for corruption and lockouts.

Not to mention the time it takes to mirror databases this size, and to back this data up. (Have you noticed with server crashes, the rollback has been going backwards from about a 15 minute rollback to an hour rollback?) That's because when the server crashes, the system probably reverts back to the last successful backup. Due to the increasing sizes of the databases, the backup process is getting longer and longer. So the rollbacks will be further and further behind.

And querying a database that is 6.6 million rows would noticably take longer than one that is 3.37 million rows. So that adds to lagg within the game.

So for all those that are complaining about this upcoming fix. It may not be good for individuals, and the economy will have some adjustment periods. But for a game structural standpoint. I can see why they are doing it.

Zuker - Jedi
Zuke - Commando
Bastia - Structures Trader
SPAM Sales - South of Coronet Starport 84, -5588 Sales Inventory Lottery

- Strength In Numbers - Loyal Subjects of the Empire
Asia Brothers Industries - Asia Hall SiN CiTY, Dantooine (Offers Vendor at -4703 -1404)
A player bodyguard can't protect you either, something agroes you, you are dead. The
only difference between a pet and the person, is you pay the person to stand there
and watch you die. -- Straker Atrella

"I am a disturbance in the force..."
Aviatrix, Master Bounty Huntress

"Oh, No! I've been shot!"
AVX, Master Droid Tinkerer - AVX Industries
Loot vendor located at: 4892 2251 (Obsidian Point, Rori.)
Droid vendors located at: 5132 1745 (95m SE from Obsidian Point Shuttleport)
Stocking Combat, Entertainer, Crafter, Astromech and BH Droids.
Yes, I make Mando Helms and Jetpacks.

so why not a time limit on crafted items. just crafted not houses or harvesters because we pay mat on those.

say you made a dress on july 2, 90 days later it wil go bye bye

we would not have all the storage we now have today because we would be using items rather then oh ill hang onto it i might use it,

all loot will not have a life cycle nore the gifts,

its a idea.

- Strength In Numbers - Loyal Subjects of the Empire
Asia Brothers Industries - Asia Hall SiN CiTY, Dantooine (Offers Vendor at -4703 -1404)
A player bodyguard can't protect you either, something agroes you, you are dead. The
only difference between a pet and the person, is you pay the person to stand there
and watch you die. -- Straker Atrella

Some comments:

1) SOE use Oracle. Oracle has since a long time an autogrow feature and can add new segements to increase database. Database can be expanded beyond current diskapace without to much trouble since a) add a new disk in raid structure, b) database can use several physical datafiles (you can add another datafile on another disk to expand space on an existing database).

2) Search on 1000 row might not be much slower than 1 million if you search on an indexed columns. For example the vendor database (which I would assume exist of VendorId, ItemId, Date, Price, Comment) is probably indexed on Vendorid, Itemid, Date ro something. So when you browse one vendor is cosntraint the query on vendorid. Also it will probably read one chunk of 100 rows at a time. Since it (at least should) use an index the database don't need to find all rows for that vendor, only the 100 first which is listed in the vendor window.

3) The database structure (simplified; or IMO how it shoudl be done) is Items (all items each having a unique id), Vendors, "Vendor sales table". Te last one being the last used for the stringwhat is sold on vendors. it would typically use VendorId, ItemId etc like I said before. The table is joined to item and d vendor tables to keep tables normalized. Since the query when using vendors is a separate table from the total item database it does not matter much for performance/database load that there are a lto more items stored elsewhere (loojup is used the unique item id and that is fast and should not be much different with 1000 or 1 mill rows).

4) I don't think there are that many batch operations on the vendor sales database. Selling/buying is single row operations. The only batch operation I can think of is when time limit is checked and there is probably an index on date that make this query/update only constrain on the affected rows. At no point I can see why a there is a need to query entire database and can always be constrained in one way or another.

This is how I guess it works (and how I would design the database). I am application developer developing administrative applications so when designing a large game system like this there might be other needs to think about.

It might be that for some reason (performance probably) a query loading all items on the vendor you are browsing is executed and then the number of items on a vendor will affect database load a lot. Takes a lot more resources to handle a query of 2000 items than 100. But it seems like it would be a bad design.

If a query is run against database every time you click next item on vendor or browse another catgeory then the current vendor interface is very inefficient. let say I want to look at carbines on a vendor. I first click weapons, query run and 100 rows of all wepaons returned to client. Then I click carbines, another query of 100 rows are run.

---
Bermag [SiyBer Arms]

ex-NGE 12 pt Master Weaponsmith/FS Crafting Mastery- Wanderhome
Corellia: (Coronet -200, -5500) Dantooine Imp op -4422 -2383
High quality and low price
Now playing Eve

they are doing this to break up large monopolies,on the contorary its going to do the opposite...every crafter is going to have to join a large monopoly.Plain and simple,if this goes live...Can anyone say intergalactic appocalpyse?

I'm no expert on data bases. But i am an expert on computers. Maybe they need to break up the database into smaller segments using multiple hard drives. Just a stab in the dark but say one hard drive for deeds one for clothing the search paths should be simple enough to create.

Allow the creator of a vendor to eliminate some of the fields they will never use. In my personal experience in game when i go searching the galaxy for a Sword i have no choice but to hit theAll button. If a vendor is stocked with only weapons and Armor allowing for the removal of all the other feilds would certainly free up some reserved space. ( this was just a caffene induced thought I don't know if it is practical or not.)

What ever the solution is i'm very sure there is one out there that doesn't involve capping the vendors. The sad truth is it will besimpler and cheeper for SOE to cap the vendors than buy additional hardware and invest time into revamping the system.

Another thing i am wondering is how much, doesthis have to do with the upcoming release if JTL. After all ifJTL will blend seemlessly with SWG there will be some interaction between the existing system and the new.

one last thing. When i close my eyes and try to imagine the SWG network opperating center I have 2 very different pictures that emerge.

One is a bunch of old 286 systems running IBM dos 2.2. Bundles of coaxial cable taped together with multiple colors of electrical tape. Half eaten twinkies and empty mountain dew cans heeped on top of the moniters. Technicians slumped over their keyboards drooling as they sleep. Their hair being pulled staticly to the monitor.

The other is a huge mainframe system in an ultra clean environmet with clean shaven technicians briskly moving about in their white lab coats jotting notes down on clipboards.

Which picture emerges depends entirely on how bad the lag is.

Thydbault

Since at least version 7.0 and up of MSSQL it has also had an autogrow feature to the database.

If I remember right from way back when SWG was released, one of the problems was that the servers did not have their own Oracle DB setup, several world servers would share the same database. For those of you that have worked with DB, especially Oracle, you know the licensing on those bad boys comes at a pretty penny.

Very sad to see the devs constantly scrambling to deal with the results of some very poor decisions in the making of the game. Someone severely underestimated the DB requirements of SWG.

Zeck Ravenclaw
BH,day one player, Eclipse

SOE makes roughly $4,500,000 a month($54,000,000 per year) on SWG. How much database does that buy?

---

Truisms are true, hold on to that! The solid world exists, its laws do not change. Stones are hard, water is wet, objects unsupported fall towards the earth's centre. With the feeling that he was speaking to O'Brien, and also that he was setting forth an important axiom, he wrote: Freedom is the freedom to say that two plus two make four. If that is granted, all else follows.

---

Would something as simple as proper table indexing help at all?

K

Hogwash 101. A profession revamp because of database limitations? It's a design failure because a SQL Server or Oracle database can handle our requirements-- and more, if designed correctly.

Plus, with all these wonderful changes, the user base will only grow. Therefore, why have it at 110 items? Soon, we will outgrow that. Let's make it 57. That would give us lots of database space. Hey... why not 33 items-- even better? Or, 10? 5? 2?

Or, keep making stupid changes, have us all quit, and have an efficient database of 0 row.

Weerip, King of worms
Master Shipwright
Novice Smugler
Novice Carbeener