Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Optimize sqlite3.oo1.DB.exec() for the rowMode='object' case to avoid converting the object property keys (column names) from native code to JS for each row. This speeds up large data sets considerably and addresses the report in forum post 3632183d2470617d. |
---|---|
Downloads: | Tarball | ZIP archive | SQL archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA3-256: |
8b41ef8690001eb299f5b7182c28f531 |
User & Date: | stephan 2024-04-04 22:53:09 |
Context
2024-04-05
| ||
11:23 | When compiling with SQLITE_ALLOW_ROWID_IN_VIEW, if the RETURNING clause of an UPDATE of a view specifies a rowid, then return NULL for the value of that rowid. dbsqlfuzz 7863696e9e5ec10b29bcf5ab2681cd6c82a78a4a. (check-in: c7896e88 user: drh tags: trunk) | |
2024-04-04
| ||
22:53 | Optimize sqlite3.oo1.DB.exec() for the rowMode='object' case to avoid converting the object property keys (column names) from native code to JS for each row. This speeds up large data sets considerably and addresses the report in forum post 3632183d2470617d. (check-in: 8b41ef86 user: stephan tags: trunk) | |
14:26 | Add the "interstage-heuristic" that attempts to avoid wildly inefficient queries that use table scans instead of index lookups because the output row estimates are inaccurate. (check-in: 7bf49e2c user: drh tags: trunk) | |
Changes
Changes to ext/wasm/api/sqlite3-api-oo1.js.
︙ | ︙ | |||
429 430 431 432 433 434 435 | toss3("Invalid returnValue value:",opt.returnValue); } if(!opt.callback && !opt.returnValue && undefined!==opt.rowMode){ if(!opt.resultRows) opt.resultRows = []; out.returnVal = ()=>opt.resultRows; } if(opt.callback || opt.resultRows){ | | > > > > > > > > > > > > | | > > > > | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | 429 430 431 432 433 434 435 436 437 438 439 440 441 442 443 444 445 446 447 448 449 450 451 452 453 454 455 456 457 458 459 460 461 462 463 464 465 466 467 468 469 470 471 472 473 474 475 476 477 478 479 480 481 482 483 484 485 486 487 488 489 490 491 492 | toss3("Invalid returnValue value:",opt.returnValue); } if(!opt.callback && !opt.returnValue && undefined!==opt.rowMode){ if(!opt.resultRows) opt.resultRows = []; out.returnVal = ()=>opt.resultRows; } if(opt.callback || opt.resultRows){ switch((undefined===opt.rowMode) ? 'array' : opt.rowMode) { case 'object': out.cbArg = (stmt,cache)=>{ if( !cache.columnNames ) cache.columnNames = stmt.getColumnNames([]); /* https://sqlite.org/forum/forumpost/3632183d2470617d: conversion of rows to objects (key/val pairs) is somewhat expensive for large data sets because of the native-to-JS conversion of the column names. If we instead cache the names and build objects from that list of strings, it can run twice as fast. The difference is not noticeable for small data sets but becomes human-perceivable when enough rows are involved. */ const row = stmt.get([]); const rv = Object.create(null); for( const i in cache.columnNames ) rv[cache.columnNames[i]] = row[i]; return rv; }; break; case 'array': out.cbArg = (stmt)=>stmt.get([]); break; case 'stmt': if(Array.isArray(opt.resultRows)){ toss3("exec(): invalid rowMode for a resultRows array: must", "be one of 'array', 'object',", "a result column number, or column name reference."); } out.cbArg = (stmt)=>stmt; break; default: if(util.isInt32(opt.rowMode)){ out.cbArg = (stmt)=>stmt.get(opt.rowMode); break; }else if('string'===typeof opt.rowMode && opt.rowMode.length>1 && '$'===opt.rowMode[0]){ /* "$X": fetch column named "X" (case-sensitive!). Prior to 2022-12-14 ":X" and "@X" were also permitted, but having so many options is unnecessary and likely to cause confusion. */ const $colName = opt.rowMode.substr(1); out.cbArg = (stmt)=>{ const rc = stmt.get(Object.create(null))[$colName]; return (undefined===rc) ? toss3(capi.SQLITE_NOTFOUND, "exec(): unknown result column:",$colName) : rc; }; break; } toss3("Invalid rowMode:",opt.rowMode); } } return out; }; /** Internal impl of the DB.selectValue(), selectArray(), and |
︙ | ︙ | |||
880 881 882 883 884 885 886 887 | we need to delay fetching of the column names until after the first step() (if we step() at all) because a schema change between the prepare() and step(), via another connection, may invalidate the column count and names. */) ? 0 : 1; evalFirstResult = false; if(arg.cbArg || resultRows){ for(; stmt.step(); stmt._lockedByExec = false){ | > > > | > > | | 896 897 898 899 900 901 902 903 904 905 906 907 908 909 910 911 912 913 914 915 916 917 918 | we need to delay fetching of the column names until after the first step() (if we step() at all) because a schema change between the prepare() and step(), via another connection, may invalidate the column count and names. */) ? 0 : 1; evalFirstResult = false; if(arg.cbArg || resultRows){ const cbArgCache = Object.create(null) /* 2nd arg for arg.cbArg, used by (at least) row-to-object converter */; for(; stmt.step(); stmt._lockedByExec = false){ if(0===gotColNames++){ stmt.getColumnNames(cbArgCache.columnNames = (opt.columnNames || [])); } stmt._lockedByExec = true; const row = arg.cbArg(stmt,cbArgCache); if(resultRows) resultRows.push(row); if(callback && false === callback.call(opt, row, stmt)){ break; } } stmt._lockedByExec = false; } |
︙ | ︙ |