buddycloud/buddycloud-server-java — pull request #198 by lloydwatkin (2014-07-08 11:06:38+00:00)
Title: Slow query

The new database queries added as part of https://github.com/buddycloud/buddycloud-server-java/pull/179 are causing bottlenecks on our local buddycloud server.

In development performance looked good and indexing only improved the situation further. However when scaled up to the team at Surevine we are seeing queries take approximately 1040ms each, which causes a database backlog as it tries to process all the queries.

Due to the way the server works user's refreshing their browser then more requests will queue up (rather than stopping as per HTTP for example) only making the issue exponentially worse.

This fixes that issue by using a LEFT JOIN so we always have a record of subscriptions even if there is no related affiliation in the database (as happens when someone subscribes). This is perfectly fine for current usage of the buddycloud server.

Potentially in future we should look at also sending an affiliation update or having an additional stanza which could send node membership (to include affiliation and subscription data).