31
Jan
13

Sometimes I make myself happy

One challenge with RummyFight is to constantly optimize the servers. Since there is an increase in both players and playing there will sooner or later be a limit of what the servers can cope with.

At the moment that limit is still pretty far away but I rather spend some time with it now because I want that to do it later because I must.

So what can be optimized then? Well, even though things are pretty fast as they already are there’s always a few milliseconds more to tweak out of the system here and there. My focus lately has been on the “FetchLobbyFights”-routine which is called pretty often by the apps. As a matter of fact, it’s called about 10 times a second at peak time which means that if I can optimize it a little it might make a good difference after all.

So… up until now, the FetchLobbyFights has been a combination of three database calls and some post processing logic. 10 calls a second * 3 = 30 database operations per second and that’s a bit too much according to me. Also, each fetch took about 110ms to make meaning that every minute there’s been about 66 seconds of work for the server (many of them in parallel though)

But with some clever coding I’ve been able to reduce the database calls to ONE per fetch and now the call takes about 67ms instead. This means a reduction of database operations of 2/3 plus a reduced workload of 39%. That’s a lot! And besides making fewer database operations, the one remaining is also a lot friendlier to the database since the sorting is made in the apps afterwards, not in the database (outsourcing the work, anyone?)

And I do like the size of the database query now too 😉

SELECT d1, d2, d3, d4, d5, d6, d7, d8, d9, p1.d10, p2.d10, p3.d10, p4.d10, d11, d12, d13, d14, d15, d16, d17, d18, d19, d20, d21, d22, m.d23, d24, d25, d26, d27, d28, m.d29, m.d30, p1.d31, p2.d31, p3.d31, p4.d32, d33, d34, p1.d35, p2.d35, p3.d35, p4.d35 FROM TABLE1 m join TABLE2 p1 on abs(d1)=p1.d1 join TABLE2 p2 on abs(d1)=p2.d1 join TABLE2 p3 on abs(d1)=p3.d1 join TABLE2 p4 on abs(d1)=p4.d1 join TABLE3 on d2=pk_d2 WHERE (abs(p1.d10)=d1 OR abs(p2.d10)=d1 OR abs(p3.d10)=d1 OR abs(p4.d10)=d1)

All row and table names have been changed in the above query so there’s no use for you to memorize anything. Just wanted to show that I find it amusing that a query with 5 JOIN and a WHERE with mathematical operations can be as fast as it is.

And now… back to the app 😉


1 Response to “Sometimes I make myself happy”


  1. March 15, 2013 at 11:28 am

    curious to see if anything is being done to the servers – have had problems getting in the past 2 days. it even resulted in my opponent having to forfeit a game.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s


Categories


%d bloggers like this: