Pager counts destination strings, not source strings

Hm, I'm seeing what you mean, and it is definitely not intended. I could not tell the reason from a quick look at the code. The count query starts off with "SELECT COUNT(DISTINCT(s.sid)) FROM {l10n_community_string} s" and then adds any join and where conditions the same way as it applies to the lookup query. So theoretically it should count by the source string (s.sid), not the suggestion/translation (t.tid) count.

Confirm that count is wrong, but cant say actually what it counts. Pager sometimes is not visible but after translation new lines appear from hidden page

SELECT DISTINCT s.sid, s.value, s.context, t.tid, t.language, t.translation, t.uid_entered, t.uid_approved, t.time_entered, t.time_approved, t.is_suggestion, t.is_active, u.name as username, u2.name as username_approved, ts.has_suggestion, ts.has_translation FROM {l10n_server_string} s

after N times left join, it isn't returns DISTINCT results.

try this, tested on local, looks fine (but bad performance ??)

Looks promising. Anybody else can help test?

Tested with local server - works great! Counts exactly and no performance loss.

re-roll without CR and against module's folder

some new idea & test.

GROUP BY will using temp file on my side that make it slowly. around 9s for the query
split it into 2 query. 0.9~2ms

1. Query string #sid (0.x ms)

SELECT DISTINCT s.sid FROM l10n_server_string s LEFT JOIN l10n_server_status_flag ts ON s.sid = ts.sid AND ts.language = '%s' LEFT JOIN l10n_server_translation t ON ts.sid = t.sid AND ts.language = t.language AND t.is_active = 1 LEFT JOIN users u ON u.uid = t.uid_entered LEFT JOIN users u2 ON u2.uid = t.uid_approved LIMIT 0, 10

2. Query SELECT .........WHERE s.sid IN (#sid), remove LIMIT row here. (0.x ms)

SELECT s.sid, s.value, s.context, t.tid, t.language, t.translation, t.uid_entered, t.uid_approved, t.time_entered, t.time_approved, t.is_suggestion, t.is_active, u.name as username, u2.name as username_approved, ts.has_suggestion, ts.has_translation FROM l10n_server_string s LEFT JOIN l10n_server_status_flag ts ON s.sid = ts.sid AND ts.language = '%s' LEFT JOIN l10n_server_translation t ON ts.sid = t.sid AND ts.language = t.language AND t.is_active = 1 LEFT JOIN users u ON u.uid = t.uid_entered LEFT JOIN users u2 ON u2.uid = t.uid_approved WHERE s.sid IN (1,2,3,4,5,6,7,8,9,10) 

It will return all row with/without suggestions. so we can save some query for suggestions too.

@droplet Thats a great idea! Strings-per-page are limited by a fixed list of limits so this query should be a much faster

Well, we still need to build in all the options for the first query. Can you look into doing a patch?

quickly patched.
hope someone have time dig into deeply and test.
Thanks.

+  }else {
+    $strings_sid = db_query($sql_sid, $sql_args);

else - should start from new line. http://drupal.org/coding-standards

reroll patch format

This works for me, but still need another review about performance

Well, I looked into this. I reworked the patch to eliminate the optionality of the pager, since that was pretty scary... Letting that query run without a pager could end up with a list of sids in an IN condition with hundred, thousands of items... Bad. So this basically worked again :)

However, I was not happy that we just tack on yet another conditional and run with all the joins and conditions of the sid query, while we really should not do that. My understanding is that we can just skip the conditionals (but we do need to use the joins to get all the data we need). So at the end I'm overriding the where conditionals and the sql args with shorter appropriate items. This looks to be good in my local testing, hope it will work great on l.d.o too.

Yes, seems to be working fine online.

One of the Hungarian translation team leads pointed out to be (and I reproduced), that big projects got WSOD when you filtered for things such as "has suggestion". Smaller projects filtered for the same worked fine. So looks like this is not yet ready for deployment and I needed to roll back. The filter now works properly. An example URL that was WSOD with this patch (without error messages in the logs unfortunately): http://localize.drupal.org/translate/languages/hu/translate?project=drup...

I'm not at all ruling out that the WSOD was maybe due to my changes, but I could not figure out why it happens, so a rollback was important to keep the service running proper.

The exact patch I rolledback was: http://drupalcode.org/project/l10n_server.git/patch/9d7ca95

pretty hard to debug without info & sql dump.

run out of memory ??
old & wrong result always return fixed number of suggestions but new changes could end up much more result.

require DISTINCT on $select query:
+ $select = "SELECT s.sid, s.value, s.context, t.tid, t.language, t.translation, t.uid_entered, t.time_entered, t.time_changed, t.is_suggestion, t.is_active, u.name as username, ts.has_suggestion, ts.has_translation FROM {l10n_server_string} s";

to

$select = "SELECT DISTINCT s.sid, s.value, s.context, t.tid, t.language, t.translation, t.uid_entered, t.time_entered, t.time_changed, t.is_suggestion, t.is_active, u.name as username, ts.has_suggestion, ts.has_translation FROM {l10n_server_string} s";

Wait, I'really can't think of an out of memory condition in the patched code.

At maximum you can have page_size sids loaded at any time, regardless of the complexity of the original query.

There must be some hidden bug/corner case...
Have you checked what happens if the query returns 0 strings.

@aturetta,

on my end it works very well. Unluckily, no chance look into LDO DB.

Got the LDO DB, loaded the DB into my small virtualbox,
I patched the l10n_server to show 500 strings each page..on a page has 600 translation

Executed 1538 queries in 2603.22 milliseconds. Queries taking longer than 5 ms and queries executed more than once, are highlighted. Page execution time was 5092.62 ms.

Memory used at: devel_init()=1.03 MB, devel_shutdown()=45.09 MB.

Patched:

Executed 1870 queries in 3272.88 milliseconds. Queries taking longer than 5 ms and queries executed more than once, are highlighted. Page execution time was 6822.92 ms.

Memory used at: devel_init()=1.03 MB, devel_shutdown()=52.27 MB.

(Patched load more strings, so the query time & page execution time are normal.)