Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Optimize CREATE INDEX, REINDEX and VACUUM statements by taking better advantage of the fact that index keys are being inserted into b-trees in sorted order. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | mistake |
Files: | files | file ages | folders |
SHA1: |
763d2bc74b560c86e362d5577dd5625c |
User & Date: | dan 2015-03-30 19:56:18.658 |
Context
2015-03-30
| ||
19:56 | Optimize CREATE INDEX, REINDEX and VACUUM statements by taking better advantage of the fact that index keys are being inserted into b-trees in sorted order. (Leaf check-in: 763d2bc74b user: dan tags: mistake) | |
12:06 | Improve performance of multi-field sorts where the first field has a low cardinality. (check-in: 601e7b6b8e user: dan tags: sorter-opt) | |
Changes
Changes to src/btree.c.
︙ | ︙ | |||
7665 7666 7667 7668 7669 7670 7671 | ** the b-tree if possible. If the cursor is left pointing to the last ** entry in the table, and the next row inserted has an integer key ** larger than the largest existing key, it is possible to insert the ** row without seeking the cursor. This can be a big performance boost. */ pCur->info.nSize = 0; if( rc==SQLITE_OK && pPage->nOverflow ){ | | | 7665 7666 7667 7668 7669 7670 7671 7672 7673 7674 7675 7676 7677 7678 7679 | ** the b-tree if possible. If the cursor is left pointing to the last ** entry in the table, and the next row inserted has an integer key ** larger than the largest existing key, it is possible to insert the ** row without seeking the cursor. This can be a big performance boost. */ pCur->info.nSize = 0; if( rc==SQLITE_OK && pPage->nOverflow ){ pCur->curFlags &= ~(BTCF_ValidNKey|BTCF_AtLast); rc = balance(pCur); /* Must make sure nOverflow is reset to zero even if the balance() ** fails. Internal data structure corruption will result otherwise. ** Also, set the cursor state to invalid. This stops saveCursorPosition() ** from trying to save the current position of the cursor. */ pCur->apPage[pCur->iPage]->nOverflow = 0; |
︙ | ︙ |
Changes to src/build.c.
︙ | ︙ | |||
2760 2761 2762 2763 2764 2765 2766 | pIndex->nKeyCol); VdbeCoverage(v); sqlite3UniqueConstraint(pParse, OE_Abort, pIndex); }else{ addr2 = sqlite3VdbeCurrentAddr(v); } sqlite3VdbeAddOp3(v, OP_SorterData, iSorter, regRecord, iIdx); sqlite3VdbeAddOp3(v, OP_IdxInsert, iIdx, regRecord, 1); | < | 2760 2761 2762 2763 2764 2765 2766 2767 2768 2769 2770 2771 2772 2773 | pIndex->nKeyCol); VdbeCoverage(v); sqlite3UniqueConstraint(pParse, OE_Abort, pIndex); }else{ addr2 = sqlite3VdbeCurrentAddr(v); } sqlite3VdbeAddOp3(v, OP_SorterData, iSorter, regRecord, iIdx); sqlite3VdbeAddOp3(v, OP_IdxInsert, iIdx, regRecord, 1); sqlite3ReleaseTempReg(pParse, regRecord); sqlite3VdbeAddOp2(v, OP_SorterNext, iSorter, addr2); VdbeCoverage(v); sqlite3VdbeJumpHere(v, addr1); sqlite3VdbeAddOp1(v, OP_Close, iTab); sqlite3VdbeAddOp1(v, OP_Close, iIdx); sqlite3VdbeAddOp1(v, OP_Close, iSorter); |
︙ | ︙ |
Changes to src/vdbe.c.
︙ | ︙ | |||
4704 4705 4706 4707 4708 4709 4710 | ** Synopsis: key=r[P2] ** ** Register P2 holds an SQL index key made using the ** MakeRecord instructions. This opcode writes that key ** into the index P1. Data for the entry is nil. ** ** P3 is a flag that provides a hint to the b-tree layer that this | | > > > > > > > > > | | | 4704 4705 4706 4707 4708 4709 4710 4711 4712 4713 4714 4715 4716 4717 4718 4719 4720 4721 4722 4723 4724 4725 4726 4727 4728 4729 4730 4731 4732 4733 4734 4735 4736 4737 4738 4739 4740 4741 4742 4743 4744 4745 4746 4747 4748 4749 4750 4751 4752 4753 4754 4755 4756 4757 4758 4759 4760 4761 4762 4763 4764 4765 4766 | ** Synopsis: key=r[P2] ** ** Register P2 holds an SQL index key made using the ** MakeRecord instructions. This opcode writes that key ** into the index P1. Data for the entry is nil. ** ** P3 is a flag that provides a hint to the b-tree layer that this ** insert is an append. In other words, if P3 is set then it is guaranteed ** that key P2 is larger than all the keys already present in index ** btree P1. ** ** If P5 has the OPFLAG_NCHANGE bit set, then the change counter is ** incremented by this instruction. If the OPFLAG_NCHANGE bit is clear, ** then the change counter is unchanged. ** ** If P5 has the OPFLAG_USESEEKRESULT bit set, then the cursor must have ** just done a seek to the spot where the new entry is to be inserted. ** This flag avoids doing an extra seek. ** ** This instruction only works for indices. The equivalent instruction ** for tables is OP_Insert. */ case OP_SorterInsert: /* in2 */ case OP_IdxInsert: { /* in2 */ VdbeCursor *pC; BtCursor *pCrsr; int nKey; const char *zKey; int bEmpty; assert( pOp->p1>=0 && pOp->p1<p->nCursor ); pC = p->apCsr[pOp->p1]; assert( pC!=0 ); assert( isSorter(pC)==(pOp->opcode==OP_SorterInsert) ); pIn2 = &aMem[pOp->p2]; assert( pIn2->flags & MEM_Blob ); pCrsr = pC->pCursor; if( pOp->p5 & OPFLAG_NCHANGE ) p->nChange++; assert( pCrsr!=0 ); assert( pC->isTable==0 ); rc = ExpandBlob(pIn2); if( rc==SQLITE_OK ){ if( isSorter(pC) ){ rc = sqlite3VdbeSorterWrite(pC, pIn2); }else if( pOp->p3 ){ assert( pC->seekResult==0 && (pOp->p5 & OPFLAG_USESEEKRESULT)==0 ); rc = sqlite3BtreeLast(pCrsr, &bEmpty); if( rc==SQLITE_OK ){ rc = sqlite3BtreeInsert(pCrsr, pIn2->z, pIn2->n,"",0,0,1,(bEmpty?0:-1)); } }else{ nKey = pIn2->n; zKey = pIn2->z; rc = sqlite3BtreeInsert(pCrsr, zKey, nKey, "", 0, 0, 0, ((pOp->p5 & OPFLAG_USESEEKRESULT) ? pC->seekResult : 0) ); assert( pC->deferredMoveto==0 ); pC->cacheStatus = CACHE_STALE; } } break; } |
︙ | ︙ |