Odoo Changelog — 2024-02-06

Tuesday, February 6, 2024

1 change

Improvements(1)

Enhancements to existing features

[IMP] im_livechat: speed up operator selection

Core17.0im_livechatHIGH

This update significantly speeds up the process of assigning live chat operators to visitors. The system was taking multiple seconds to find available operators on large databases, which caused delays when many visitors were using auto-popup chat. The improvement reduces this time to under 50 milliseconds by optimizing how the system searches through operator availability data.

Original PR description

Since #135113, calls to /im_livechat/get_session can become very expensive on large databases with a long history of livechat sessions. Operators who have thousands of past livechat sessions…

Since #135113, calls to /im_livechat/get_session can become very expensive on large databases with a long history of livechat sessions. Operators who have thousands of past livechat sessions (channels) can cause the SQL query in get_operator() to take multiple seconds.

When the livechat is set to auto-popup and there are lots of visitors, this can become very significant.

This patch changes two aspects:
- introduce a CTE for the RTC session part, in order to avoid a JOIN cardinality explosion between livechat channels and channel_members - we only care about channel membership for a RTC session AFAICT
- restrict the selection of livechat channels created **within the last 24h**, considering that we only care about messages sent in the last 30 minutes anyway

The second part may change the result ordering in the presence of old channels (>24h) with recent messages - but that is rather unlikely, and an operator will be selected anyway.

This change makes a huge difference on a database with 300k livechat channels and 1 million `discuss_channel_member` records, from several seconds to >50ms. The results are identical or extremely similar in most cases, and the database only needs to look at a few hundred records instead of millions.

#performance#live_chat#operator_assignment#database_optimization

Since #135113, calls to /im_livechat/get_session can become very expensive on large databases with a long history of livechat sessions. Operators who have thousands of past livechat sessions (channels) can cause the SQL query in get_operator() to take multiple seconds. When the livechat is set to auto-popup and there are lots of visitors, this can become very significant. This patch changes two aspects: - introduce a CTE for the RTC session part, in order to avoid a JOIN cardinality explosion between livechat channels and channel_members - we only care about channel membership for a RTC session AFAICT - restrict the selection of livechat channels created **within the last 24h**, considering that we only care about messages sent in the last 30 minutes anyway The second part may change the result ordering in the presence of old channels (>24h) with recent messages - but that is rather unlikely, and an operator will be selected anyway. This change makes a huge difference on a database with 300k livechat channels and 1 million `discuss_channel_member` records, from several seconds to >50ms. The results are identical or extremely similar in most cases, and the database only needs to look at a few hundred records instead of millions.