Remove select row_count() from Tig_OfflineMessages

Remove select row_count() from Tig_OfflineMessages_DeleteMessage (#1252)

Wojciech Kapcia (Tigase) 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.

Related
- Performance Optimization for stored procedure `Tig_OfflineMessages_AddMessage` and `last_insert_id` (#1539) Closed

Activities

Wojciech Kapcia (Tigase) changed state to 'Open' 10 months ago
Previous Value Current Value
Closed

Open
Wojciech Kapcia (Tigase) commented 10 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) changed fields 10 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) added to iteration "tigase-server-8.5.0" 10 months ago
Wojciech Kapcia (Tigase) added to iteration "tigase-server-8.2.0" 10 months ago
Davide Marrone commented 10 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) added "Related" #1539 10 months ago
Wojciech Kapcia (Tigase) commented 10 months ago

Yes, there is also #1539 that uses LAST_INSERT_ID but while it causes performance issues it's different from this one.
Andrzej Wójcik (Tigase) commented 2 weeks ago

@wojtek I've applied discussed changes and pushed them to offlinestorage-schema branch for a review.
Andrzej Wójcik (Tigase) changed state to 'In Progress' 2 weeks ago
Previous Value Current Value
Open

In Progress
Wojciech Kapcia (Tigase) commented 1 week ago

@andrzej.wojcik Why index creation is missing from mysql schema? It was in src/main/database/mysql-server-8.5.0.sql and looks like the intention was to move it to src/main/database/mysql-server-8.5.0-schema.sql but, I guess, was not saved/stashed/committed? (judging by the changes in other schemas)
Andrzej Wójcik (Tigase) commented 1 week ago

I think it got lost when I was moving content of files form .sql to -schema.sql to match SQL schema files layout of 8.4.0 and previous releases.

I've restored it in the latest commit.
Wojciech Kapcia (Tigase) commented 1 week ago

Could you merge it with previous commit for consistency and merge the offlinestorage-schema branch?
Wojciech Kapcia (Tigase) changed fields 1 week ago
Name Previous Value Current Value
Assignee

wojtek

andrzej.wojcik
Andrzej Wójcik (Tigase) commented 1 week ago

I've rebased changes (squashed commits to fix missing index in commit that caused the issue), pushed changes, and merged offlinestorage-schema branch with master branch.
Andrzej Wójcik (Tigase) changed fields 1 week ago
Name Previous Value Current Value
Assignee

andrzej.wojcik

wojtek
Andrzej Wójcik (Tigase) changed state to 'In QA' 1 week ago
Previous Value Current Value
In Progress

In QA
Wojciech Kapcia (Tigase) changed state to 'Closed' 6 days ago
Previous Value Current Value
In QA

Closed
Login to comment

Previous Value	Current Value
Closed	Open

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

Previous Value	Current Value
Open	In Progress

Name	Previous Value	Current Value
Assignee	wojtek	andrzej.wojcik

Name	Previous Value	Current Value
Assignee	andrzej.wojcik	wojtek

Previous Value	Current Value
In Progress	In QA

Previous Value	Current Value
In QA	Closed

Type	Task
Priority	Normal
Assignee	Wojciech Kapcia (Tigase)
Version	tigase-server-8.2.0, tigase-server-8.5.0
Spent time	0

Iterations

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

Issue Votes (0)

Watchers (4)

Reference

tigase/_server/server-core#1252