Core/Mailsystem: Optimized returning and deleting old mails, will no longer take minutes when starting using old character db

3 files changed, 26 insertions, 20 deletions

diff --git a/src/server/game/Globals/ObjectMgr.cpp b/src/server/game/Globals/ObjectMgr.cpp
index 595aa068142..22876b1467f 100755
--- a/src/server/game/Globals/ObjectMgr.cpp
+++ b/src/server/game/Globals/ObjectMgr.cpp
@@ -5446,12 +5446,29 @@ void ObjectMgr::ReturnOrDeleteOldMails(bool serverUp)
     PreparedQueryResult result = CharacterDatabase.Query(stmt);
     if (!result)
     {
-        sLog->outString(">> No expired mails found or DB table `mail` is empty.");
+        sLog->outString(">> No expired mails found.");
         sLog->outString();
         return;                                             // any mails need to be returned or deleted
     }
 
-    uint32 count = 0;
+    std::map<uint32 /*messageId*/, MailItemInfoVec> itemsCache;
+    stmt = CharacterDatabase.GetPreparedStatement(CHAR_GET_EXPIRED_MAIL_ITEMS);
+    stmt->setUInt64(0, basetime);
+    if (PreparedQueryResult items = CharacterDatabase.Query(stmt))
+    {
+        MailItemInfo item;
+        do
+        {
+            Field* fields = items->Fetch();
+            item.item_guid = fields[0].GetUInt32();
+            item.item_template = fields[1].GetUInt32();
+            uint32 mailId = fields[2].GetUInt32();
+            itemsCache[mailId].push_back(item);
+        } while (items->NextRow());
+    }
+
+    uint32 deletedCount = 0;
+    uint32 returnedCount = 0;
     do
     {
 
@@ -5482,21 +5499,9 @@ void ObjectMgr::ReturnOrDeleteOldMails(bool serverUp)
         // Delete or return mail
         if (has_items)
         {
-            stmt = CharacterDatabase.GetPreparedStatement(CHAR_GET_MAIL_ITEM_LITE);
-            stmt->setUInt32(0, m->messageID);
-            if (PreparedQueryResult resultItems = CharacterDatabase.Query(stmt))
-            {
-                do
-                {
-                    Field *fields2 = resultItems->Fetch();
-
-                    uint32 item_guid_low = fields2[0].GetUInt32();
-                    uint32 item_template = fields2[1].GetUInt32();
+            // read items from cache
+            m->items.swap(itemsCache[m->messageID]);
 
-                    m->AddItem(item_guid_low, item_template);
-                }
-                while (resultItems->NextRow());
-            }
             // if it is mail from non-player, or if it's already return mail, it shouldn't be returned, but deleted
             if (m->messageType != MAIL_NORMAL || (m->checked & (MAIL_CHECK_MASK_COD_PAYMENT | MAIL_CHECK_MASK_RETURNED)))
             {
@@ -5533,6 +5538,7 @@ void ObjectMgr::ReturnOrDeleteOldMails(bool serverUp)
                     CharacterDatabase.Execute(stmt);
                 }
                 delete m;
+                ++returnedCount;
                 continue;
             }
         }
@@ -5541,11 +5547,11 @@ void ObjectMgr::ReturnOrDeleteOldMails(bool serverUp)
         stmt->setUInt32(0, m->messageID);
         CharacterDatabase.Execute(stmt);
         delete m;
-        ++count;
+        ++deletedCount;
     }
     while (result->NextRow());
 
-    sLog->outString(">> Loaded %u mails in %u ms", count, GetMSTimeDiffToNow(oldMSTime));
+    sLog->outString(">> Processed %u expired mails: %u deleted and %u returned in %u ms", deletedCount + returnedCount, deletedCount, returnedCount, GetMSTimeDiffToNow(oldMSTime));
     sLog->outString();
 }
 
diff --git a/src/server/shared/Database/Implementation/CharacterDatabase.cpp b/src/server/shared/Database/Implementation/CharacterDatabase.cpp
index 89ddcf10a2f..af6cd018025 100755
--- a/src/server/shared/Database/Implementation/CharacterDatabase.cpp
+++ b/src/server/shared/Database/Implementation/CharacterDatabase.cpp
@@ -85,7 +85,7 @@ void CharacterDatabaseConnection::DoPrepareStatements()
     PREPARE_STATEMENT(CHAR_ADD_MAIL_ITEM, "INSERT INTO mail_items(mail_id, item_guid, receiver) VALUES (?, ?, ?)", CONNECTION_ASYNC)
     PREPARE_STATEMENT(CHAR_DEL_EMPTY_EXPIRED_MAIL, "DELETE FROM mail WHERE expire_time < ? AND has_items = 0 AND body = ''", CONNECTION_ASYNC)
     PREPARE_STATEMENT(CHAR_GET_EXPIRED_MAIL, "SELECT id, messageType, sender, receiver, has_items, expire_time, cod, checked, mailTemplateId FROM mail WHERE expire_time < ?", CONNECTION_SYNCH)
-    PREPARE_STATEMENT(CHAR_GET_MAIL_ITEM_LITE, "SELECT item_guid, itemEntry FROM mail_items mi INNER JOIN item_instance ii ON ii.guid = mi.item_guid WHERE mail_id = ?", CONNECTION_SYNCH)
+    PREPARE_STATEMENT(CHAR_GET_EXPIRED_MAIL_ITEMS, "SELECT item_guid, itemEntry, mail_id FROM mail_items mi INNER JOIN item_instance ii ON ii.guid = mi.item_guid LEFT JOIN mail mm ON mi.mail_id = mm.id WHERE mm.id IS NOT NULL AND mm.expire_time < ?", CONNECTION_SYNCH)
     PREPARE_STATEMENT(CHAR_SET_MAIL_RETURNED, "UPDATE mail SET sender = ?, receiver = ?, expire_time = ?, deliver_time = ?, cod = 0, checked = ? WHERE id = ?", CONNECTION_ASYNC)
     PREPARE_STATEMENT(CHAR_SET_MAIL_ITEM_RECEIVER, "UPDATE mail_items SET receiver = ? WHERE item_guid = ?", CONNECTION_ASYNC)
     PREPARE_STATEMENT(CHAR_SET_ITEM_OWNER, "UPDATE item_instance SET owner_guid = ? WHERE guid = ?", CONNECTION_ASYNC)
diff --git a/src/server/shared/Database/Implementation/CharacterDatabase.h b/src/server/shared/Database/Implementation/CharacterDatabase.h
index 55edf264f38..7a967f8bd11 100755
--- a/src/server/shared/Database/Implementation/CharacterDatabase.h
+++ b/src/server/shared/Database/Implementation/CharacterDatabase.h
@@ -94,7 +94,7 @@ enum CharacterDatabaseStatements
     CHAR_ADD_MAIL_ITEM,
     CHAR_DEL_EMPTY_EXPIRED_MAIL,
     CHAR_GET_EXPIRED_MAIL,
-    CHAR_GET_MAIL_ITEM_LITE,
+    CHAR_GET_EXPIRED_MAIL_ITEMS,
     CHAR_SET_MAIL_RETURNED,
     CHAR_SET_MAIL_ITEM_RECEIVER,
     CHAR_SET_ITEM_OWNER,