Performance Discrepancy in SQLite/WASM: Object vs. Array RowMode

Understanding the Performance Gap Between Object and Array RowMode in SQLite/WASM

When working with SQLite in a WebAssembly (WASM) environment, one of the critical decisions developers face is how to structure the results of their queries. The choice between rowMode: 'object' and rowMode: 'array' can have a significant impact on performance, particularly when dealing with large datasets. In this analysis, we will delve into the reasons behind the performance discrepancy, explore the underlying causes, and provide actionable solutions to mitigate the issue.

The core of the problem lies in the way SQLite/WASM handles the conversion of query results into JavaScript objects. When using rowMode: 'object', each row of the result set is converted into a JavaScript object where the keys are the column names and the values are the corresponding row data. This process involves decoding the column names from their native representation to JavaScript strings for every single row, which introduces a non-trivial overhead. On the other hand, rowMode: 'array' returns the results as arrays, bypassing the need for column name decoding and thus offering better performance.

The Overhead of Column Name Decoding in Object RowMode

The primary source of the performance discrepancy between rowMode: 'object' and rowMode: 'array' is the repeated decoding of column names. In rowMode: 'object', the SQLite/WASM interface must convert the native column names into JavaScript strings for each row in the result set. This conversion process involves calling functions like target.cstrToJs, which are relatively expensive operations, especially when performed millions of times in a large dataset.

The expectation that column name decoding should be a one-time operation is reasonable. However, the current implementation of SQLite/WASM does not cache the decoded column names. Instead, it performs the conversion for every row, leading to a significant performance penalty. This inefficiency becomes particularly noticeable when querying large datasets, where the overhead of repeated decoding can double the execution time compared to rowMode: 'array'.

Optimizing Query Performance with Array RowMode and Column Names

Given the performance issues associated with rowMode: 'object', a practical workaround is to use rowMode: 'array' in combination with the columnNames option. This approach allows developers to retrieve the column names once and then manually construct the desired JavaScript objects from the array-based results. While this method introduces an additional step of converting arrays to objects, the overall performance is significantly better than using rowMode: 'object' directly.

The implementation of this workaround involves the following steps:

1. Set rowMode to ‘array’: This ensures that the query results are returned as arrays, avoiding the overhead of column name decoding.
2. Use the columnNames option: This option provides the column names as a separate array, computed only once.
3. Manually construct JavaScript objects: After retrieving the results, iterate over the array-based rows and construct objects using the column names and corresponding row values.

Here is an example of how this can be implemented:

const rowMode = 'array';
const columnNames = [];
const result = db.exec({
  sql: 'SELECT * FROM t;',
  rowMode,
  columnNames,
  returnValue: 'resultRows'
});

if (rowMode === 'array') {
  for (let i = 0; i < result.length; i++) {
    const obj = Object.create(null);
    for (let c = 0; c < columnNames.length; c++) {
      obj[columnNames[c]] = result[i][c];
    }
    result[i] = obj;
  }
}

Preliminary tests indicate that this approach is only negligibly slower than using rowMode: 'array' directly and roughly twice as fast as using rowMode: 'object'. For instance, on a Mac M2, the execution time for a query might be around 150ms with rowMode: 'array', 155-160ms with the workaround, and 300ms with rowMode: 'object'.

Long-Term Solutions and Future Improvements

While the workaround provides a viable solution for the time being, it is essential to consider long-term improvements to the SQLite/WASM interface. One potential enhancement is to modify the internal callback mechanism that converts statement objects into the desired row mode. By caching the decoded column names and reusing them for each row, the performance of rowMode: 'object' could be significantly improved.

The current implementation of the callback is generic and does not have enough information to determine whether it will be called once or multiple times. Rewriting this callback to cache and reuse column names could be a feasible solution, although it would require careful consideration of the API design to ensure compatibility and maintainability.

Another approach is to introduce a new option that allows developers to specify whether column names should be cached. This would provide more control over the performance characteristics of the query and allow developers to optimize their applications based on specific use cases.

In conclusion, the performance discrepancy between rowMode: 'object' and rowMode: 'array' in SQLite/WASM is primarily due to the repeated decoding of column names. By using a combination of rowMode: 'array' and the columnNames option, developers can achieve better performance while still obtaining the desired object-based results. Long-term improvements to the SQLite/WASM interface, such as caching column names and optimizing the callback mechanism, could further enhance performance and provide a more seamless experience for developers working with large datasets.