[KarlR]

Hi Team,

No sure if you have fixed this in latest version of OpenCats ( we can't get it to work on AWS LAMP 7 so we are using cats_0.9.4.3 on AWS LAMP 5.6 and it works perfectly!). Also didn't find any forum notes about it.

Thought this might assist.

Background: We are in UAT for OpenCats 0.9.4.3 on AWS Bitnami LAMP 5.6 - 16GB RAM, 20GB SSD , we have 5x global offices, 20x recruitment consultants, and 50,000 plus Candidates in our database.

During UAT we noticed extremely poor performance of the "Candidates" Tab, ie. CLICK on "CANDIDATES" tab at top and wait 10-18 seconds every time the tab is clicked. You can imagine the frustration of 20x consultants who will use this all day, everyday !

So like all good DBA's I tried to find the slow sql. ie. look at the SQL for that page being used that creates the "Candidates: Home" Results DataGrid. Ran session tracing for slow-query in mysql (log below). From this I found the keyword: SQL_CALC_FOUND_ROWS. Recursive grep -R on the root of the installation found file: lib/Candidates.php

The "naughty" code is in Candidates.php (LINES: 1479 - 1509).... attached to this ticket.

The problem with the code is the logic and use of LEFT (OUTER) JOINS, DISTINCT and GROUP BY just to get 15 Rows or whatever the user selects from the "Rows Per Page" dropdown. It may be ok for small Recruiter firms, but when you get to +50k candidates, this is really slow. When you look at the attached SESSION_TRACE.log you will see that Rows_sent: 15 Rows_examined: 346500 which means all the LEFT JOINS etc have generated 346,500 rows in TEMP space to only return 15x rows.


# Time: 201002 16:05:56
# User@Host: cats[cats] @ localhost [] Id: 785
# Query_time: 11.986854 Lock_time: 0.000207 Rows_sent: 15 Rows_examined: 346500

So I re-wrote the SQL.

1) COLUMNS are preserved in the same order as the original query in Candidates.php.
2) DataSet returned for both queries are identical.
3) the SQL now completes 5,200% faster... ie. 12.12 secs vs 0.03-0.23 secs


You can see the result sets are identical (note: columns are MD5 encypted to protect privacy data but the hashes are the same). Just remove the SUBSTR(MD5(columm_name)),1,7).

Let me know if you need any more info. Would be great if this could be included in next fork, so we can update OC_0943 on ourside to fix the issue.

Thanks
KarlR

[KarlR]

PS.... Also you will notice the below in the original SQL qry before the WHERE clause:

LEFT JOIN saved_list_entry
ON saved_list_entry.data_item_type = 100
AND saved_list_entry.data_item_id = candidate.candidate_id
AND saved_list_entry.site_id = 1

I undestand the first 2x LEFT JOINS to give you 1/0. I can see the icons on the 2nd COL of the datagrid display as icons for each. I have handle those in the SELECT (CASE (SELECT))) COLS in the new query.

I dont see any benefit of doing the final " LEFT JOIN saved_list_entry"is you do not predicate the RESULTS IN COLUMN OUTPUTS ??? eg. why do a LEFT (OUTER) JION if you are NOT GOING TO USE THE DATA IN THE APPLICATION?

ie. if you are not going to use the output of PREDICATE in the RESULT SET then why do it anyways?

anyways... just thinking out loud ))