Speed up the loading of the database structure

MKleusberg · MKleusberg · commit 5589bd9da44b · 2019-06-28T14:58:50.000+02:00
While testing the original database from issue #1892 which contains hundreds of tables and fields, I noticed how long the loading of the database structure takes. This is especially problematic since it needs to be reloaded on various occasions, e.g. running most statements in the Execute SQL tab, which stalls the application every time. According to a profiler it is the QIcon constructor which requires most of the time. By introducing this small icon cache class we can reduce the time for loading icons to almost nothing. While still not perfect the UI already feels much more responsive with this patch.
diff --git a/CMakeLists.txt b/CMakeLists.txt
@@ -127,6 +127,7 @@ set(SQLB_HDR
 	src/grammar/Sqlite3Lexer.hpp
 	src/grammar/Sqlite3Parser.hpp
 	src/Data.h
+	src/IconCache.h
 )
 
 set(SQLB_MOC_HDR
@@ -228,6 +229,7 @@ set(SQLB_SRC
 	src/CondFormat.cpp
 	src/RunSql.cpp
 	src/ProxyDialog.cpp
+	src/IconCache.cpp
 )
 
 set(SQLB_FORMS
diff --git a/src/DbStructureModel.cpp b/src/DbStructureModel.cpp
@@ -1,4 +1,5 @@
 #include "DbStructureModel.h"
+#include <IconCache.h>
 #include "sqlitedb.h"
 #include "sqlitetablemodel.h"
 #include "Settings.h"
@@ -160,12 +161,12 @@ void DbStructureModel::reloadData()
     // The second node contains four sub-nodes (tables, indices, views and triggers), each containing a list of objects of that type.
     // This way we only have to have and only have to update one model and can use it in all sorts of places, just by setting a different root node.
     browsablesRootItem = new QTreeWidgetItem(rootItem);
-    browsablesRootItem->setIcon(ColumnName, QIcon(QString(":/icons/view")));
+    browsablesRootItem->setIcon(ColumnName, IconCache::get("view"));
     browsablesRootItem->setText(ColumnName, tr("Browsables"));
 
     // Make sure to always load the main schema first
     QTreeWidgetItem* itemAll = new QTreeWidgetItem(rootItem);
-    itemAll->setIcon(ColumnName, QIcon(QString(":/icons/database")));
+    itemAll->setIcon(ColumnName, IconCache::get("database"));
     itemAll->setText(ColumnName, tr("All"));
     itemAll->setText(ColumnObjectType, "database");
     buildTree(itemAll, "main");
@@ -174,7 +175,7 @@ void DbStructureModel::reloadData()
     if(!m_db.schemata["temp"].empty())
     {
         QTreeWidgetItem* itemTemp = new QTreeWidgetItem(itemAll);
-        itemTemp->setIcon(ColumnName, QIcon(QString(":/icons/database")));
+        itemTemp->setIcon(ColumnName, IconCache::get("database"));
         itemTemp->setText(ColumnName, tr("Temporary"));
         itemTemp->setText(ColumnObjectType, "database");
         buildTree(itemTemp, "temp");
@@ -187,7 +188,7 @@ void DbStructureModel::reloadData()
         if(it.first != "main" && it.first != "temp")
         {
             QTreeWidgetItem* itemSchema = new QTreeWidgetItem(itemAll);
-            itemSchema->setIcon(ColumnName, QIcon(QString(":/icons/database")));
+            itemSchema->setIcon(ColumnName, IconCache::get("database"));
             itemSchema->setText(ColumnName, QString::fromStdString(it.first));
             itemSchema->setText(ColumnObjectType, "database");
             buildTree(itemSchema, it.first);
@@ -321,22 +322,22 @@ void DbStructureModel::buildTree(QTreeWidgetItem* parent, const std::string& sch
 
     // Prepare tree
     QTreeWidgetItem* itemTables = new QTreeWidgetItem(parent);
-    itemTables->setIcon(ColumnName, QIcon(QString(":/icons/table")));
+    itemTables->setIcon(ColumnName, IconCache::get("table"));
     itemTables->setText(ColumnName, tr("Tables (%1)").arg(calc_number_of_objects_by_type(objmap, "table")));
     typeToParentItem.insert({"table", itemTables});
 
     QTreeWidgetItem* itemIndices = new QTreeWidgetItem(parent);
-    itemIndices->setIcon(ColumnName, QIcon(QString(":/icons/index")));
+    itemIndices->setIcon(ColumnName, IconCache::get("index"));
     itemIndices->setText(ColumnName, tr("Indices (%1)").arg(calc_number_of_objects_by_type(objmap, "index")));
     typeToParentItem.insert({"index", itemIndices});
 
     QTreeWidgetItem* itemViews = new QTreeWidgetItem(parent);
-    itemViews->setIcon(ColumnName, QIcon(QString(":/icons/view")));
+    itemViews->setIcon(ColumnName, IconCache::get("view"));
     itemViews->setText(ColumnName, tr("Views (%1)").arg(calc_number_of_objects_by_type(objmap, "view")));
     typeToParentItem.insert({"view", itemViews});
 
     QTreeWidgetItem* itemTriggers = new QTreeWidgetItem(parent);
-    itemTriggers->setIcon(ColumnName, QIcon(QString(":/icons/trigger")));
+    itemTriggers->setIcon(ColumnName, IconCache::get("trigger"));
     itemTriggers->setText(ColumnName, tr("Triggers (%1)").arg(calc_number_of_objects_by_type(objmap, "trigger")));
     typeToParentItem.insert({"trigger", itemTriggers});
 
@@ -377,24 +378,24 @@ void DbStructureModel::buildTree(QTreeWidgetItem* parent, const std::string& sch
                 fldItem->setText(ColumnSQL, QString::fromStdString(field.sql));
                 fldItem->setText(ColumnSchema, QString::fromStdString(schema));
                 if(contains(pk_columns, field.name))
-                    fldItem->setIcon(ColumnName, QIcon(":/icons/field_key"));
+                    fldItem->setIcon(ColumnName, IconCache::get("field_key"));
                 else if(isFK)
-                    fldItem->setIcon(ColumnName, QIcon(":/icons/field_fk"));
+                    fldItem->setIcon(ColumnName, IconCache::get("field_fk"));
                 else
-                    fldItem->setIcon(ColumnName, QIcon(":/icons/field"));
+                    fldItem->setIcon(ColumnName, IconCache::get("field"));
             }
         }
     }
 }
 
 QTreeWidgetItem* DbStructureModel::addNode(QTreeWidgetItem* parent, const sqlb::ObjectPtr& object, const std::string& schema)
 {
-    QString type = QString::fromStdString(sqlb::Object::typeToString(object->type()));
+    std::string type = sqlb::Object::typeToString(object->type());
 
     QTreeWidgetItem *item = new QTreeWidgetItem(parent);
-    item->setIcon(ColumnName, QIcon(QString(":/icons/%1").arg(type)));
+    item->setIcon(ColumnName, IconCache::get(type));
     item->setText(ColumnName, QString::fromStdString(object->name()));
-    item->setText(ColumnObjectType, type);
+    item->setText(ColumnObjectType, QString::fromStdString(type));
     item->setText(ColumnSQL, QString::fromStdString(object->originalSql()));
     item->setText(ColumnSchema, QString::fromStdString(schema));
 
diff --git a/src/IconCache.cpp b/src/IconCache.cpp
@@ -0,0 +1,22 @@
+#include "IconCache.h"
+
+QIcon IconCache::null_icon;
+std::unordered_map<std::string, QIcon> IconCache::icons;
+
+const QIcon& IconCache::get(const std::string& name)
+{
+    // Check if we already have an icon object with that name in the cache. If so, just return that
+    auto it = icons.find(name);
+    if(it != icons.end())
+        return it->second;
+
+    // If now, try to load an icon with that name and insert it into the cache
+    QIcon icon(":/icons/" + QString::fromStdString(name));
+    auto ins = icons.insert({name, icon});
+
+    // If the insertion was successful, return the inserted icon object. If it was not, return a null icon.
+    if(ins.second)
+        return ins.first->second;
+    else
+        return null_icon;
+}
diff --git a/src/IconCache.h b/src/IconCache.h
@@ -0,0 +1,21 @@
+#ifndef ICONCACHE_H
+#define ICONCACHE_H
+
+#include <QIcon>
+
+#include <string>
+#include <unordered_map>
+
+class IconCache
+{
+public:
+    IconCache() = delete;
+
+    static const QIcon& get(const std::string& name);
+
+private:
+    static QIcon null_icon;
+    static std::unordered_map<std::string, QIcon> icons;
+};
+
+#endif
diff --git a/src/src.pro b/src/src.pro
@@ -75,7 +75,8 @@ HEADERS += \
     sql/Query.h \
     RunSql.h \
     sql/ObjectIdentifier.h \
-    ProxyDialog.h
+    ProxyDialog.h \
+    IconCache.h
 
 SOURCES += \
     sqlitedb.cpp \
@@ -127,7 +128,8 @@ SOURCES += \
     sql/Query.cpp \
     RunSql.cpp \
     sql/ObjectIdentifier.cpp \
-    ProxyDialog.cpp
+    ProxyDialog.cpp \
+    IconCache.cpp
 
 RESOURCES += icons/icons.qrc \
              translations/flags/flags.qrc \

Original file line number	Diff line number	Diff line change
`@@ -127,6 +127,7 @@ set(SQLB_HDR`
`127`	`127`	`src/grammar/Sqlite3Lexer.hpp`
`128`	`128`	`src/grammar/Sqlite3Parser.hpp`
`129`	`129`	`src/Data.h`
	`130`	`+ src/IconCache.h`
`130`	`131`	`)`
`131`	`132`
`132`	`133`	`set(SQLB_MOC_HDR`
`@@ -228,6 +229,7 @@ set(SQLB_SRC`
`228`	`229`	`src/CondFormat.cpp`
`229`	`230`	`src/RunSql.cpp`
`230`	`231`	`src/ProxyDialog.cpp`
	`232`	`+ src/IconCache.cpp`
`231`	`233`	`)`
`232`	`234`
`233`	`235`	`set(SQLB_FORMS`