Performance Coding suggestion

[Zackman [LDEV]]

Hi Team !

First of all, thank you for all your work in your free time and effort to bring back this great game!

I try to word this carefully so it doesnt appear as a smart-ass posting (not intended), all i want is to contribute a little bit.

I am a developer by profession and i also worked on emulation projects myself. My strength is coding and even more SQL/MySQL.

I have no intention to file an application since i do not have the time due to my business (unfortunatly).

While the server is down i had some time to "investigate" an issue i realized - so i looked up the code.

It is a bout the loading time for the vendor/market items which takes a very long time - too long imho.

I stumbled across some code that could be optimized a big deal, missing sql-indexes and an ugly/big Switch in the code (switch/case).

itemBaseSQL.cpp, the FOR-IF loop beginning at Line 101.

The MySQL query is querying the item_base, within the loop it is querying diff. item type tables (missiles, engines, shields etc) constantly over and over again, depending on the item_type (case switch).

This will fire a huge amount of childprocesses up on the MySQL server, it will initialize the query queue over and over and almost every single item in the "master query" is causing many "isolated" child-querys in the switch again.

This is a huge performance issue and resource waste.

I changed the query a big time to this:

Index-changes:
Table item_effect_container
Index-name : item_id    Field: itemID
Table item_effects
Index-name : item_id    Field: itemID
Table item_ammo
Index-name : ammo_type_id    Field: ammo_type_id
Table item_projectile
Index-name : ammo_type_id    Field: ammo_type_id

This query is executing in 0.7 seconds.

This will return in a bigger result (like 100 fields) which could be still cut down ( i was lazy, didnt select only needed fields).

The memory consumption of this query is WAY lower then the current code - with 4293 items in the tables its about 3,2 MB RAM for the query.

If you pimp the MySQL-Server and spend some more Key-Cache, Table-Cache and (important) QUERY!-Cache, the market will be load almost every time from the servers RAM which will result in a huge performance-boost!

There are indexes missing in the tables. Without some changes the query took 6 seconds, with changed indexes it was 0.7 sec.

Index-changes:
Table item_effect_container
Index-name : item_id    Field: itemID
Table item_effects
Index-name : item_id    Field: itemID
Table item_ammo
Index-name : ammo_type_id    Field: ammo_type_id
Table item_projectile
Index-name : ammo_type_id    Field: ammo_type_id

The switch in the code could stay, switching on the diff. item_types, but the querys in the case-blocks can be deleted and simply query the according (item based) fields - a NULL-check is needed of course.

Another thing i realized is, there are no MySQL-escape-sequence-capsulating in most ( all? ) querys.

This could cause problems on certain characters within querys.

This posting is intended to contribute to the project, please do not understand it in any other way.

I hope it helps a bit.

PS.:

Didn't find a "Developer" Section here, so i hope this board is fine for the topic.

[vasi]

And you say you have no time to contribute to this project?

[Zackman [LDEV]]

That was done quickly to be honest

[Numiah2]

That was done quickly to be honest

Please stay with us

[Zackman [LDEV]]

I am not leaving ...

This was my first (love) MMO and will be my last

I dunno if these kinds of suggestions are welcome, maybe a dev can comment this.

[Slayerman]

I am not leaving ...

This was my first (love) MMO and will be my last

I dunno if these kinds of suggestions are welcome, maybe a dev can comment this.

Suggestions are always welcome

[ghent96]

Zackman, you should definitely be a server dev here. I would like to encourage you. I believe your time involvement with EnBemu is whatever you make of it none, a little, or tons - always your choice, and I don't think anyone will think any less of a dev for being busy IRL and contributing when they can. Just as you did above, in a few minutes of free time, so you might be able to do again, right? Maybe next week, maybe 2 months from now... that's ok. Your knowledge and ability whenever you can afford to share it, will be of great use to the community. Plus if you are actually on the team, you can have the position to implement ("check in") your code changes immediately, rather than waiting around for someeone else to do it for you and/or play politics on you or whatever.