Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Enhance the code generator for INSERT INTO ... SELECT so that the SELECT generates output directly in the registers that INSERT INTO will be using, in many cases, and OP_SCopy operations can thus be avoided. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | insert-optimization |
Files: | files | file ages | folders |
SHA1: |
aa2d8b0e8154dd2f5e2c837dc11ab362 |
User & Date: | drh 2014-02-16 01:55:49.753 |
Context
2014-02-17
| ||
14:59 | Avoid unnecessary calls to applyAffinity() during INSERT and UPDATE operations, especially for table that have indices and tables for which all columns have affinity "NONE". (check-in: 35b4d6e938 user: drh tags: insert-optimization) | |
2014-02-16
| ||
01:55 | Enhance the code generator for INSERT INTO ... SELECT so that the SELECT generates output directly in the registers that INSERT INTO will be using, in many cases, and OP_SCopy operations can thus be avoided. (check-in: aa2d8b0e81 user: drh tags: insert-optimization) | |
2014-02-14
| ||
23:49 | Seek past NULLs in a top-constrained search. Avoid checking for NULLs in the body of the search. (check-in: e07a32f308 user: drh tags: trunk) | |
Changes
Changes to src/expr.c.
︙ | |||
3108 3109 3110 3111 3112 3113 3114 | 3108 3109 3110 3111 3112 3113 3114 3115 3116 3117 3118 3119 3120 3121 3122 3123 3124 3125 3126 3127 3128 3129 3130 3131 3132 3133 3134 3135 3136 3137 3138 3139 3140 3141 3142 3143 3144 3145 3146 3147 3148 3149 3150 3151 3152 3153 3154 3155 3156 3157 3158 3159 3160 3161 3162 3163 3164 3165 3166 3167 3168 3169 3170 3171 3172 | - + + - + + + + + + + + + + + + + - + - - + + - - - - - - - - + - - - - + + + + - - | } /* ** Generate code that will evaluate expression pExpr and store the ** results in register target. The results are guaranteed to appear ** in register target. */ |
︙ |
Changes to src/insert.c.
︙ | |||
144 145 146 147 148 149 150 | 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 | - + - + | /* ** Return non-zero if the table pTab in database iDb or any of its indices ** have been opened at any point in the VDBE program beginning at location ** iStartAddr throught the end of the program. This is used to see if ** a statement of the form "INSERT INTO <iDb, pTab> SELECT ..." can ** run without using temporary table for the results of the SELECT. */ |
︙ | |||
330 331 332 333 334 335 336 | 330 331 332 333 334 335 336 337 338 339 340 341 342 343 | - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - | ** If SQLITE_OMIT_AUTOINCREMENT is defined, then the three routines ** above are all no-ops */ # define autoIncBegin(A,B,C) (0) # define autoIncStep(A,B,C) #endif /* SQLITE_OMIT_AUTOINCREMENT */ |
︙ | |||
527 528 529 530 531 532 533 | 454 455 456 457 458 459 460 461 462 463 464 465 466 467 468 469 470 471 472 473 474 475 476 477 | - - + - - + + + | Index *pIdx; /* For looping over indices of the table */ int nColumn; /* Number of columns in the data */ int nHidden = 0; /* Number of hidden columns if TABLE is virtual */ int iDataCur = 0; /* VDBE cursor that is the main data repository */ int iIdxCur = 0; /* First index cursor */ int ipkColumn = -1; /* Column that is the INTEGER PRIMARY KEY */ int endOfLoop; /* Label for the end of the insertion loop */ |
︙ | |||
647 648 649 650 651 652 653 654 655 656 657 658 659 660 661 | 574 575 576 577 578 579 580 581 582 583 584 585 586 587 588 589 590 591 592 593 594 595 596 597 598 599 600 601 602 603 604 605 606 607 608 609 610 611 612 613 614 615 616 617 618 619 620 621 622 623 624 625 626 627 628 629 630 631 632 633 634 635 636 637 638 639 640 641 642 643 644 645 646 647 648 649 650 651 652 653 654 655 656 657 658 659 660 661 662 663 664 665 666 667 668 669 670 671 672 673 674 | + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + - - + + + + + + + + + + + + + + - - + | } #endif /* SQLITE_OMIT_XFER_OPT */ /* If this is an AUTOINCREMENT table, look up the sequence number in the ** sqlite_sequence table and store it in memory cell regAutoinc. */ regAutoinc = autoIncBegin(pParse, iDb, pTab); /* Allocate registers for holding the rowid of the new row, ** the content of the new row, and the assemblied row record. */ regRowid = regIns = pParse->nMem+1; pParse->nMem += pTab->nCol + 1; if( IsVirtual(pTab) ){ regRowid++; pParse->nMem++; } regData = regRowid+1; /* If the INSERT statement included an IDLIST term, then make sure ** all elements of the IDLIST really are columns of the table and ** remember the column indices. ** ** If the table has an INTEGER PRIMARY KEY column and that column ** is named in the IDLIST, then record in the ipkColumn variable ** the index into IDLIST of the primary key column. ipkColumn is ** the index of the primary key as it appears in IDLIST, not as ** is appears in the original table. (The index of the INTEGER ** PRIMARY KEY in the original table is pTab->iPKey.) */ if( pColumn ){ for(i=0; i<pColumn->nId; i++){ pColumn->a[i].idx = -1; } for(i=0; i<pColumn->nId; i++){ for(j=0; j<pTab->nCol; j++){ if( sqlite3StrICmp(pColumn->a[i].zName, pTab->aCol[j].zName)==0 ){ pColumn->a[i].idx = j; if( i!=j ) bIdListInOrder = 0; if( j==pTab->iPKey ){ ipkColumn = i; assert( !withoutRowid ); } break; } } if( j>=pTab->nCol ){ if( sqlite3IsRowid(pColumn->a[i].zName) && !withoutRowid ){ ipkColumn = i; }else{ sqlite3ErrorMsg(pParse, "table %S has no column named %s", pTabList, 0, pColumn->a[i].zName); pParse->checkSchema = 1; goto insert_cleanup; } } } } /* Figure out how many columns of data are supplied. If the data ** is coming from a SELECT statement, then generate a co-routine that ** produces a single row of the SELECT on each invocation. The ** co-routine is the common header to the 3rd and 4th templates. */ if( pSelect ){ /* Data is coming from a SELECT. Generate a co-routine to run the SELECT */ |
︙ | |||
720 721 722 723 724 725 726 727 728 729 730 731 732 733 734 735 736 737 738 739 740 741 742 743 744 745 | 708 709 710 711 712 713 714 715 716 717 718 719 720 721 722 723 724 725 726 727 728 729 730 731 732 733 734 735 736 737 738 739 740 741 742 743 744 745 746 747 748 | + + + + + + + + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - | nColumn = pList ? pList->nExpr : 0; for(i=0; i<nColumn; i++){ if( sqlite3ResolveExprNames(&sNC, pList->a[i].pExpr) ){ goto insert_cleanup; } } } /* If there is no IDLIST term but the table has an integer primary ** key, the set the ipkColumn variable to the integer primary key ** column index in the original table definition. */ if( pColumn==0 && nColumn>0 ){ ipkColumn = pTab->iPKey; } /* Make sure the number of columns in the source data matches the number ** of columns to be inserted into the table. */ if( IsVirtual(pTab) ){ for(i=0; i<pTab->nCol; i++){ nHidden += (IsHiddenColumn(&pTab->aCol[i]) ? 1 : 0); } } if( pColumn==0 && nColumn && nColumn!=(pTab->nCol-nHidden) ){ sqlite3ErrorMsg(pParse, "table %S has %d columns but %d values were supplied", pTabList, 0, pTab->nCol-nHidden, nColumn); goto insert_cleanup; } if( pColumn!=0 && nColumn!=pColumn->nId ){ sqlite3ErrorMsg(pParse, "%d values for %d columns", nColumn, pColumn->nId); goto insert_cleanup; } |
︙ | |||
832 833 834 835 836 837 838 | 782 783 784 785 786 787 788 789 790 791 792 793 794 795 | - - - - - - - - - - - | ** insert the select result into <table> from R..R+n ** goto C ** D: ... */ addrInsTop = addrCont = sqlite3VdbeAddOp1(v, OP_Yield, dest.iSDParm); } |
︙ | |||
926 927 928 929 930 931 932 | 865 866 867 868 869 870 871 872 873 874 875 876 877 878 879 | - + | /* The row that the VUpdate opcode will delete: none */ sqlite3VdbeAddOp2(v, OP_Null, 0, regIns); } if( ipkColumn>=0 ){ if( useTempTable ){ sqlite3VdbeAddOp3(v, OP_Column, srcTab, ipkColumn, regRowid); }else if( pSelect ){ |
︙ | |||
972 973 974 975 976 977 978 | 911 912 913 914 915 916 917 918 919 920 921 922 923 924 925 926 927 928 929 930 931 932 933 934 935 936 937 938 939 940 941 942 943 944 945 946 947 948 949 950 | - - + + + - + + - + + | nHidden = 0; for(i=0; i<pTab->nCol; i++){ int iRegStore = regRowid+1+i; if( i==pTab->iPKey ){ /* The value of the INTEGER PRIMARY KEY column is always a NULL. ** Whenever this column is read, the rowid will be substituted ** in its place. Hence, fill this column with a NULL to avoid |
︙ |
Changes to src/select.c.
︙ | |||
581 582 583 584 585 586 587 588 589 | 581 582 583 584 585 586 587 588 589 590 591 592 593 594 595 596 597 598 599 600 601 602 603 604 605 606 607 | + - + + + + + + + - - + | if( pOrderBy==0 && !hasDistinct ){ codeOffset(v, p->iOffset, iContinue); } /* Pull the requested columns. */ nResultCol = pEList->nExpr; if( pDest->iSdst==0 ){ pDest->iSdst = pParse->nMem+1; |
︙ |
Changes to src/sqliteInt.h.
︙ | |||
2986 2987 2988 2989 2990 2991 2992 | 2986 2987 2988 2989 2990 2991 2992 2993 2994 2995 2996 2997 2998 2999 | - | #ifndef SQLITE_OMIT_AUTOINCREMENT void sqlite3AutoincrementBegin(Parse *pParse); void sqlite3AutoincrementEnd(Parse *pParse); #else # define sqlite3AutoincrementBegin(X) # define sqlite3AutoincrementEnd(X) #endif |
︙ | |||
3034 3035 3036 3037 3038 3039 3040 | 3033 3034 3035 3036 3037 3038 3039 3040 3041 3042 3043 3044 3045 3046 3047 3048 3049 3050 3051 3052 | - + + - + | void sqlite3ExprCodeMove(Parse*, int, int, int); void sqlite3ExprCacheStore(Parse*, int, int, int); void sqlite3ExprCachePush(Parse*); void sqlite3ExprCachePop(Parse*, int); void sqlite3ExprCacheRemove(Parse*, int, int); void sqlite3ExprCacheClear(Parse*); void sqlite3ExprCacheAffinityChange(Parse*, int, int); |
︙ |
Changes to src/vdbe.c.
︙ | |||
990 991 992 993 994 995 996 997 998 999 1000 1001 1002 1003 | 990 991 992 993 994 995 996 997 998 999 1000 1001 1002 1003 1004 1005 1006 1007 1008 1009 1010 1011 1012 1013 1014 1015 1016 1017 | + + + + + + + + + + + + + + | VdbeMemRelease(pOut); pOut->flags = nullFlag; cnt--; } break; } /* Opcode: SoftNull P1 * * * * ** Synopsis: r[P1]=NULL ** ** Set register P1 to have the value NULL as seen by the OP_MakeRecord ** instruction, but do not free any string or blob memory associated with ** the register, so that if the value was a string or blob that was ** previously copied using OP_SCopy, the copies will continue to be valid. */ case OP_SoftNull: { assert( pOp->p1>0 && pOp->p1<=(p->nMem-p->nCursor) ); pOut = &aMem[pOp->p1]; pOut->flags = (pOut->flags|MEM_Null)&~MEM_Undefined; break; } /* Opcode: Blob P1 P2 * P4 * ** Synopsis: r[P2]=P4 (len=P1) ** ** P4 points to a blob of data P1 bytes long. Store this ** blob in register P2. */ |
︙ | |||
2905 2906 2907 2908 2909 2910 2911 | 2919 2920 2921 2922 2923 2924 2925 2926 2927 2928 2929 2930 2931 2932 2933 2934 2935 2936 2937 2938 2939 2940 2941 2942 2943 2944 2945 2946 2947 2948 2949 2950 2951 2952 2953 2954 2955 2956 2957 2958 2959 2960 2961 2962 2963 | - - - + + + + + - - - - - - - - - - - + + + + + + | rc = SQLITE_ERROR; } break; } /* Opcode: Transaction P1 P2 P3 P4 P5 ** |
︙ |
Changes to test/insert.test.
︙ | |||
394 395 396 397 398 399 400 | 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 | - + | CREATE TABLE t10(a,b,c); INSERT INTO t10 VALUES(1,2,3), (4,5,6), (7,8,9); SELECT * FROM t10; } } {1 2 3 4 5 6 7 8 9} do_test insert-10.2 { catchsql { |
Changes to test/insert4.test.
︙ | |||
249 250 251 252 253 254 255 | 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 | - + | } {} } # Check some error conditions: # do_test insert4-5.1 { # Table does not exist. |
︙ |