Have some troubles with monitoring plugin when openfire starts.
It takes about 10-15 minutes for Monitoring plugin initialization, at this time Openfire can’t handle incoming user connections, only web page works correctly.
After some research i’ve found problem SQL query (MSSQL 2012 as DB backend):
exec sp_prepare @p1 output,N’@P0 bigint’,N’SELECT DISTINCT ofConversation.conversationID, ofConversation.room, ofConversation.isExternal, ofConversation.startDate, ofConversation.lastActivity, ofConversation.messageCount, ofConParticipant.joinedDate, ofConParticipant.leftDate, ofConParticipant.bareJID, ofConParticipant.jidResource, ofConParticipant.nickname, ofMessageArchive.fromJID, ofMessageArchive.toJID, ofMessageArchive.sentDate, ofMessageArchive.body FROM ofConversation INNER JOIN ofConParticipant ON ofConversation.conversationID = ofConParticipant.conversationID INNER JOIN ofMessageArchive ON ofConParticipant.conversationID = ofMessageArchive.conversationID WHERE ofConversation.lastActivity > @P0 ',1
and after that -
exec sp_execute 5,1419117885898 that takes about 15 minutes to complete.
I can’t understand why SQL server chooses wrong exec plan for this query, if i execute it manually from SSMS - it takes 0 sec to complete because of short time range in where clause.
I have huge history tables (ofConversation - 4208721 records, ofMessageArchive - 16286713 records) so SQL server takes very loooong time to prepare this query with wrong plan .
Please help to resolve this problem, thanks!
P.S. Code line: Openfire/JdbcPersistenceManager.java at ac304ea9cf6abf989338bc5e34af006e45fbddf9 · igniterealtime/Openfire · GitHub
I’ve found that hint - option (OPTIMIZE FOR (@p0 = N))
(where N is the actual @P0 value)
makes query very fast as it should be.
But i doesn’t know java and openfire code very well, can it be added to query text in the plugin code?
Just sorry for poor english…