OpenCATS 0.9.4-3

Free, Open-Source Applicant Tracking System including job-board. Entirely customisable!

What do you want to see in OpenCATS? What the must-have, useful feature that's missing? Is it major redesign or a small tweak? Share here and we can doscuss, agree, add to the roadmap.

Moderators: RussH, cptr13

#4868
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 :D


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
Attachments
(357.12 KiB) Downloaded 19 times
Last edited by KarlR on 06 Oct 2020, 14:33, edited 1 time in total.
#4869
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 :)))
How to enable and use ACL

Not sure if still needed, I found this as I am wor[…]

现在玩 北美传奇 的人都讲究情怀什么的,也对啊,传奇从画面上来说现在可能并不出众,甚至已经远远落[…]

Automatic database Backup

Read all the trending news in India in Hindi. Know[…]

Bulk resume upload not working

Hi there I tried to install the openCATS in my loc[…]

Quick Search BOX

unique gifts for dad , best gifts for couples, gif[…]

PPS. .... we have changed all the login .. applica[…]

PS.... Also you will notice the below in the orig[…]