Remove select row_count() from Tig_OfflineMessages

Remove select row_count() from Tig_OfflineMessages_DeleteMessage (#1252)

Activities

wojciech.kapcia@tigase.net opened 4 years ago

In Tig_OfflineMessages_DeleteMessage() we have a call select row_count() as deleted_rows; (https://github.com/tigase/tigase-server/blob/05fa90bfa40ad4b253819b4be7b897fd76b7c8b2/src/main/database/mysql-issue #8.0.0-sp.sql#L185-L185)

but in tigase.server.amp.db.JDBCMsgRepository#deleteMessage (https://github.com/tigase/tigase-server/blob/05fa90bfa40ad4b253819b4be7b897fd76b7c8b2/src/main/java/tigase/server/amp/db/JDBCMsgRepository.java#L483-L483) we don't use it?

We should change the SP to:

create procedure Tig_OfflineMessages_DeleteMessage(_msg_id bigint)
begin
   delete from tig_offline_messages where msg_id = _msg_id;
end //

to avoid performance penalty.

wojciech.kapcia@tigase.net changed state to 'Open' 3 months ago

Previous Value	Current Value
Closed	Open

wojciech.kapcia@tigase.net commented 3 months ago

The issue wasn't fixed as I made a type-o and used Tig_OfflineMessages_DeleteMessage SP name instead of correct one Tig_OfflineMessages_DeleteMessages (with "s")

wojciech.kapcia@tigase.net changed fields 3 months ago

Name	Previous Value	Current Value
Version	tigase-server-8.2.0	tigase-server-8.2.0, tigase-server-8.5.0

wojciech.kapcia@tigase.net added to iteration "tigase-server-8.5.0" 3 months ago

wojciech.kapcia@tigase.net added to iteration "tigase-server-8.2.0" 3 months ago

Davide Marrone commented 3 months ago

@wojtek actually the problem was in both the stored procedures, so the problem was partially fixed but not totally fixed, as you find out

wojciech.kapcia@tigase.net added "Related" #1539 3 months ago

wojciech.kapcia@tigase.net commented 3 months ago

Yes, there is also #1539 that uses LAST_INSERT_ID but while it causes performance issues it's different from this one.

Type	Task
Priority	Normal
Assignee	wojciech.kapcia@tigase.net
Version	tigase-server-8.2.0, tigase-server-8.5.0
Spent time	30m

Iterations

tigase-server-8.5.0 Open
tigase-server-8.2.0 Closed

Customers/catapush-s-r-l#23 You are not authorized to access this issue
#1539 Open

Performance Optimization for stored procedure `Tig_OfflineMessages_AddMessage` and `last_insert_id`

Issue Votes (0)

Watchers (3)

Reference

tigase/_server/server-core#1252