DEV Community

Cover image for Sharing the Optimization Process for Slow Database Queries
kouwei qing
kouwei qing

Posted on

Sharing the Optimization Process for Slow Database Queries

#harmonyosnext

HarmonyOS Next IM Combat: Sharing the Optimization Process for Slow Database Queries

1. Background Introduction

In the development of IMSDK, the client uses a relational database to store data such as conversations, users, and messages. Initially, when developing consumer (C-end) applications, no issues were found. However, this year, when business (B-end) users started using the application, they reported lags and message delays. Through troubleshooting, it was found that B-end users have more conversations and messages, leading to slower database queries. Since previous operations were all performed on the main thread, this caused application lags and delays. This article shares the problems encountered during the entire optimization process, problem-solving approaches, and final effects.

2. Introduction to HarmonyOS Next Relational Database

The HarmonyOS Next relational database is based on the SQLite component, suitable for scenarios requiring storage of data with complex relationships. For example, a user's conversation information needs to include the conversation name, type, ID, etc., while chat information in conversations needs to include message IDs, types, contents, etc. Due to the strong correspondence between data elements—with complexity higher than key-value data—a relational database is required for persistent storage.

The relational database provides universal operation interfaces for applications, using SQLite as the underlying persistent storage engine. It supports database features of SQLite, including but not limited to transactions, indexes, views, triggers, foreign keys, parameterized queries, and precompiled SQL statements.

To implement data persistence using a relational database, a RdbStore must be obtained, which includes operations such as database creation, table creation, and version upgrades/downgrades. Sample code is as follows:

import { relationalStore } from '@kit.ArkData'; // Import the module
import { UIAbility } from '@kit.AbilityKit';
import { BusinessError } from '@kit.BasicServicesKit';
import { window } from '@kit.ArkUI';


// This example is implemented in an Ability, but users can also use it in other reasonable scenarios
class EntryAbility extends UIAbility {
  onWindowStageCreate(windowStage: window.WindowStage) {
    // If a tokenizer is desired, call isStorageTypeSupported to check if the desired tokenizer is supported on the current platform.
    let tokenType = relationalStore.Tokenizer.ICU_TOKENIZER;
    let tokenTypeSupported = relationalStore.isTokenizerSupported(tokenType);
    if (!tokenTypeSupported) {
      console.error(`ICU_TOKENIZER is not supported on this platform.`);
    }
    const STORE_CONFIG: relationalStore.StoreConfig = {
      name: 'RdbTest.db', // Database file name
      securityLevel: relationalStore.SecurityLevel.S3, // Database security level
      encrypt: false, // Optional parameter to specify if the database is encrypted (default: not encrypted)
      customDir: 'customDir/subCustomDir', // Optional parameter for the database custom path. The database will be created in the directory structure: context.databaseDir + '/rdb/' + customDir, where context.databaseDir is the app sandbox path, '/rdb/' indicates a relational database, and customDir is the custom path. If this parameter is not provided, the RdbStore instance is created in the app sandbox directory by default.
      isReadOnly: false, // Optional parameter to specify if the database is opened in read-only mode (default: false, readable and writable). When true, only data reading is allowed; write operations will return error code 801.
      tokenizer: tokenType // Optional parameter to specify the tokenizer used in full-text search (FTS) scenarios. If not provided, only English tokenization is supported in FTS, not other languages.
    };


    // Determine the database version and perform upgrade/downgrade operations if it does not match
    // Assume the current database version is 3, with table structure: EMPLOYEE (NAME, AGE, SALARY, CODES, IDENTITY)
    const SQL_CREATE_TABLE = 'CREATE TABLE IF NOT EXISTS EMPLOYEE (ID INTEGER PRIMARY KEY AUTOINCREMENT, NAME TEXT NOT NULL, AGE INTEGER, SALARY REAL, CODES BLOB, IDENTITY UNLIMITED INT)'; // Table creation SQL statement, where IDENTITY is a bigint type specified as UNLIMITED INT in SQL


    relationalStore.getRdbStore(this.context, STORE_CONFIG, (err, store) => {
      if (err) {
        console.error(`Failed to get RdbStore. Code:${err.code}, message:${err.message}`);
        return;
      }
      console.info('Succeeded in getting RdbStore.');


      // When the database is created, the default version is 0
      if (store.version === 0) {
        store.executeSql(SQL_CREATE_TABLE) // Create the data table for subsequent insert operations
          .then(() => {
            // Set the database version to an integer greater than 0
            store.version = 3;
          })
          .catch((err: BusinessError) => {
            console.error(`Failed to executeSql. Code:${err.code}, message:${err.message}`);
          });
      }


      // If the database version is not 0 and does not match the current version, upgrade/downgrade operations are required
      // When the database exists and is assumed to be version 1 (e.g., the app upgrades to the current version, requiring the database to upgrade from version 1 to 2)
      if (store.version === 1) {
        // version = 1: Table structure: EMPLOYEE (NAME, SALARY, CODES, ADDRESS) => version = 2: Table structure: EMPLOYEE (NAME, AGE, SALARY, CODES, ADDRESS)
        store.executeSql('ALTER TABLE EMPLOYEE ADD COLUMN AGE INTEGER')
          .then(() => {
            store.version = 2;
          }).catch((err: BusinessError) => {
            console.error(`Failed to executeSql. Code:${err.code}, message:${err.message}`);
          });
      }


      // When the database exists and is assumed to be version 2 (e.g., the app upgrades to the current version, requiring the database to upgrade from version 2 to 3)
      if (store.version === 2) {
        // version = 2: Table structure: EMPLOYEE (NAME, AGE, SALARY, CODES, ADDRESS) => version = 3: Table structure: EMPLOYEE (NAME, AGE, SALARY, CODES)
        store.executeSql('ALTER TABLE EMPLOYEE DROP COLUMN ADDRESS')
          .then(() => {
            store.version = 3;
          }).catch((err: BusinessError) => {
            console.error(`Failed to executeSql. Code:${err.code}, message:${err.message}`);
          });
      }
      // Ensure that after obtaining the RdbStore instance and completing data table creation, perform database operations such as insert, delete, update, and query
    });
  }
}
Enter fullscreen mode Exit fullscreen mode

HarmonyOS Next provides relationalStore for database operations, but in CRUD (Create, Read, Update, Delete) operations, we generally implement a layer of encapsulation to convert database result sets to actual objects.

3. Introduction to ORM Frameworks

HarmonyOS Next provides the dataORM library for data relationship mapping. dataORM is a lightweight ORM (Object-Relational Mapping) library that simplifies local database operations, offering high database access performance and low memory consumption. It supports features such as multi-threading, chain calls, backup, upgrade, caching, etc. Designed to be lightweight, fast, and easy to use, it helps developers quickly build high-performance applications. However, dataORM has some limitations (e.g., no support for composite primary keys in database tables), and the official maintenance of this library has been discontinued.

Later, ByteDance open-sourced rdbStore, which performs database operations in the form of DTO (Data Transfer Object) objects. It encapsulates capabilities such as database creation and automatic upgrade, database predicate construction, query result deserialization, and quality optimization, enabling simple and efficient database operations. In terms of operation and maintenance, it features comprehensive unit testing, quality data tracking and reporting, full-link logging, etc. Due to issues encountered in database upgrades, we switched to rdbStore for data relationship mapping.

4. Multi-Threading Optimization

In scenarios with large data volumes, querying data may cause long processing times or even application freezes. General recommendations include:

  • Limit single query data volume to no more than 5,000 records.
  • Perform queries in a TaskPool.
  • Keep SQL statement concatenation concise.
  • Implement reasonable batch querying.

When the number of user conversations reached a certain level, due to a join query involving 50 columns after expansion, the query took 6-7 seconds, causing severe application lags and even freeze exceptions. Therefore, complex and time-consuming queries were moved to child threads.

@Concurrent  
async function getConvDetailObservableThread(context: Context, param: Param, convId: number): Promise<ConvBean | undefined> {  
  console.log('ConvImpl', "getConvDetailObservableThread,convId=" + convId);  
  // Store parameters, context, etc., in the thread
  await DBManager.getInstance().initDB();  
  const conv = await DBManager.getInstance()  
    .getConvDaoHelper()  
    .getConvById(convId);  
  ...
}

const result = await taskpool.execute(getConvDetailObservableThread,  
  context,
  param,
  convId);
Enter fullscreen mode Exit fullscreen mode

By default, data transfer between threads uses a copy method. For ultimate performance, it can be changed to the Sendable method.

5. Optimization for Slow Data Queries

Although the lag was basically resolved after the above optimizations, the issue of long query times persisted. The first entry into the conversation list took 7-8 seconds, resulting in poor user experience. Additionally, a strange phenomenon occurred: the Mac version simulator took tens of milliseconds, while real devices took 6-7 seconds.

Analyzing the query process step by step, since it involved a join query, using relationalStore interfaces for direct query execution was fast, but the process of resultSet.goToNextRow() in the result set was time-consuming.

Initially, traversal read data row by column: resultSet.getLong(resultSet.getColumnIndex('unreadMsgCount'));. The official API12 provided the getRow interface, which can read an entire column at once. After switching to the getRow method, the query time was optimized from 7-8 seconds to 700-800 milliseconds—ten times faster.

In API18, a new getRows interface was added, which can read all specified rows directly, yielding even better results.

Summary

This article focuses on the optimization process for database query lags caused by large conversation and message data volumes among B-end users in IMSDK development based on HarmonyOS Next. It first introduces the SQLite-based features of the HarmonyOS Next relational database, including transactions, indexes, and other functions, as well as the usage of RdbStore. It then compares two ORM frameworks, dataORM and rdbStore, and switches to the more efficient rdbStore due to dataORM being discontinued and upgrade-related issues.

In terms of performance optimization, a multi-threading solution moves complex queries to child threads to avoid main thread blocking, while adhering to principles such as limiting single query data to under 5,000 records and concise SQL concatenation. For query latency issues, analysis revealed low result set traversal efficiency, which was improved by using HarmonyOS-provided new interfaces like getRow and getRows, reducing query time from 7-8 seconds to 700-800 milliseconds and significantly enhancing data reading efficiency. This optimization not only resolves application lags and message delays but also provides referable practical experience for database operations in large data volume scenarios on the HarmonyOS platform.

Top comments (0)