Leaderboards

<span>So you want to create a leaderboard for certain information from the DATA API. This is pretty simple to do if you restrict it to a locationdata.worldid<span> (or locationdata.worldid/type.classid) due to the way the data is indexed.  If you don't provide a worldid, it will be extremely slow.

Here is a query that gives the top 10 characters with the most kills on Permafrost:

census.daybreakgames.com/xml/get/eq2/character/?locationdata.worldid=202&c:show=displayname,statistics&c:resolve=statistics(kills)&c:sort=statistics.kills.value:-1&c:limit=10

Here is a query that gives the top 10 paladin (classid=10) characters with the most kills on Permafrost:

census.daybreakgames.com/xml/get/eq2/character/?locationdata.worldid=202&type.classid=10&c:show=displayname,statistics&c:resolve=statistics(kills)&c:sort=statistics.kills.value:-1&c:limit=10

We do not yet have an index that will give you a quick result across all worlds. I'm not certain that this is especially useful, but I'm willing to listen to reasons why it would. Please post on this topic with your feedback.

-dan

 

This is useful, but I'm currently doing a much more brute-force method for http://eq2mission.flame.org/leaderboards

The problem I run into is when I try to issue a query like:

census.daybreakgames.com/json/get/eq2/ch...0&c:sort=id

the back-end times out.  I assume this is because the id field has some sort of non-ordered index like a hash, so ordering by it is painful.  However, I would think name.first would be a btree or similar index, but using c:sort=name.first also causes a timeout on the back-end.

Without the c:sort option at all, data is returned in a random order, and from experience with SQL databases, this is actually harder on systems than no ordering.  With ordering on a btree or other ordered index, the database back-end can use the index to perform the offset and limit operations, and only touch the data tables afterwards.  Without any ordering, it has to scan for the offset.

Is there a field I can sort on that won't kill the database back-end, to get the queries to be as light-weight as possible?

 

Hypothetical situation: Lets say you are a Paladin on Permafrost.  You pull up the leaderboards and see that you're number 35 for kills but you're only a few thousand behind the leader. 

So you decide to go on a killing spree over the course of several days.  You're watching your rank creep up one position at a time until you finally reach the top spot. 

So now, you're #1 for kills on Permafrost.  But how do you rank across all servers?  Are you #1 overall?  Are you 5 million behind somone on another server? 

That's when I think it would come into play, for the characters near the top of the list, they would probably like to see how they rank across all servers.  I'm pretty sure this is something the old EQ2Players site had available btw.

 

Thanks Dethdlr...that is what I was looking for.

I am going to increasingly look to the folks within this forum to provide input when we make policy decisions.  Look for a new thread on this soon.

-dan

 

Quicktiger...

Try not to think of this as a SQL database.  We are presenting data that comes from multiple sources.  While we do have some indexing, it does not behave exactly as you might think with a standalone database.

Your example is a bit troubling.  Why are you asking for all characters?  Could you be a bit more specific to give our system a better chance of responding in a timely manner?

We have built the system to be able to quickly respond to data questions for presentation.  While it can be used to mine data in bulk for you to store in your own system, that is not its primary purpose and using it that way may become a problem.  I understand that this was required before, but we are trying to make the data more accessible, not less.

It seems that your leaderboards page is useful, but some of those lists could be simple calls to the REST API and formatting the response.  I'm interested in working together with you to make that possible.

-dan

 

Without having access to all the characters, either via requests or a subscription-type feed, I will be constantly limited to the summaries you wish to provide, which really means there is no clever data-crunching I can do to be unique.  Please don't think of what I am doing as data mining, though, as it's really not intended for that purpose.  However, without a complete copy of the guilds, characters, and other information in the sony-side of the equation and a way to update it effeciently and without serious impact for either of us, all the users of this data are merely clones of one another, at the end of the day.

 

BTW, in the 24 hours since I've done my data crunching of making my own leaderboards, and was smart enough to add caching of them even though it only takes 2 seconds to make a leaderboard query...

80% of the people using the site are Russian.  I was told that the original eq2players was English only, so this is perhaps the first time they are able to get their rulers out and compare themselves to their companions.

75% of the data accesses are for leaderboard data.  20% are for individual character lookups, and the remaining accesses are various other parts of the site.

Oh, and you apparently spell "search" as Поиск in Russian, since that's what the browsers are renaming my "search" button to behind the scenes.

 

There should be no need for keeping complete copies of that much data.  Nightly dumps of data are not only going be be resource/time consuming on your end, but also on SOE's end.  I deal with multiple processes doing large data extracts on a daily basis here at work, so I know the stress your requests will cause.  It may seem feasible now, but there are only so many folks using it at the moment.  Later on, multiple processes doing data dumps would be painful to say the least.  I'd really prefer if they didn't have to put limits on the data we can pull do to processing like this.

If you're just storing new custom data per guild/character/item, then one should be able to lookup additional data for REST results easily via a local relational db design, linking REST result to data based upon some particular key (or key-pair) from the result data. 

If you are just doing the nightly dumps to pre-format/prepare the data for display, which I think is the case here, then that can be done on the fly in the webpage.  Yes, your current pages are fairly fast, but at the cost of resources on the SOE servers when you pull all the data.

 

The top line of each character record has a last updated timestamp. It's not necessary to pull a character that has not been updated since the last time it was pulled. Also I found c:sort=id to be slow. I think it's better to pull the data and then sort it locally.

One thing I'd do is reduce footprint is to add limiters like this:&c:hide=bio,appearanceslots,alternateadvancements,spells,factions,achievements,huses,quests

Or use c:show to only call what you need.

Your query is requesting tens of thousands of characters which are inactive or haven't been updated since the last query, and a lot of the data being pulled is ending up discarded. No offense, but you're trying to drink an elephant through a straw.

 

Just a note - when I asked about c:show versus c:hide from a resource point of view, c:show is the prefered way to do it if you are trying to play nice with the backend.