Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Reduce memory requirements for ORDER BY combined with LIMIT. Ticket #1586. (CVS 2887) |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
55e703ecac6e03d7364c2d919ba18d72 |
User & Date: | drh 2006-01-08 05:02:55.000 |
Context
2006-01-08
| ||
05:26 | Remove some cruft from the VDBE. Bring comments up to date. (CVS 2888) (check-in: 41aef6496a user: drh tags: trunk) | |
05:02 | Reduce memory requirements for ORDER BY combined with LIMIT. Ticket #1586. (CVS 2887) (check-in: 55e703ecac user: drh tags: trunk) | |
2006-01-07
| ||
18:48 | Invalidate all VDBE cursor row caches in between calls to sqlite3_step() since the emphemeral content that those caches point to might change if the statement is READ UNCOMMITTED. (CVS 2886) (check-in: 0ae461313c user: drh tags: trunk) | |
Changes
Changes to src/select.c.
︙ | ︙ | |||
8 9 10 11 12 13 14 | ** May you find forgiveness for yourself and forgive others. ** May you share freely, never taking more than you give. ** ************************************************************************* ** This file contains C code routines that are called by the parser ** to handle SELECT statements in SQLite. ** | | | 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | ** May you find forgiveness for yourself and forgive others. ** May you share freely, never taking more than you give. ** ************************************************************************* ** This file contains C code routines that are called by the parser ** to handle SELECT statements in SQLite. ** ** $Id: select.c,v 1.287 2006/01/08 05:02:55 drh Exp $ */ #include "sqliteInt.h" /* ** Allocate a new Select structure and return a pointer to that ** structure. |
︙ | ︙ | |||
347 348 349 350 351 352 353 | sqliteFree(p); } /* ** Insert code into "v" that will push the record on the top of the ** stack into the sorter. */ | | > > > > > > > > > > > > > > > > | 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 | sqliteFree(p); } /* ** Insert code into "v" that will push the record on the top of the ** stack into the sorter. */ static void pushOntoSorter( Parse *pParse, /* Parser context */ ExprList *pOrderBy, /* The ORDER BY clause */ Select *pSelect /* The whole SELECT statement */ ){ Vdbe *v = pParse->pVdbe; sqlite3ExprCodeExprList(pParse, pOrderBy); sqlite3VdbeAddOp(v, OP_Sequence, pOrderBy->iECursor, 0); sqlite3VdbeAddOp(v, OP_Pull, pOrderBy->nExpr + 1, 0); sqlite3VdbeAddOp(v, OP_MakeRecord, pOrderBy->nExpr + 2, 0); sqlite3VdbeAddOp(v, OP_IdxInsert, pOrderBy->iECursor, 0); if( pSelect->iLimit>=0 ){ int addr1; sqlite3VdbeAddOp(v, OP_MemIncr, pSelect->iLimit+1, 0); addr1 = sqlite3VdbeAddOp(v, OP_IfMemPos, pSelect->iLimit+1, 0); sqlite3VdbeAddOp(v, OP_Goto, 0, 0); sqlite3VdbeJumpHere(v, addr1); sqlite3VdbeAddOp(v, OP_Last, pOrderBy->iECursor, 0); sqlite3VdbeAddOp(v, OP_Delete, pOrderBy->iECursor, 0); sqlite3VdbeJumpHere(v, addr1+1); pSelect->iLimit = -1; } } /* ** Add code to implement the OFFSET */ static void codeOffset( Vdbe *v, /* Generate code into this VM */ |
︙ | ︙ | |||
500 501 502 503 504 505 506 | /* Store the result as data using a unique key. */ case SRT_Table: case SRT_VirtualTab: { sqlite3VdbeAddOp(v, OP_MakeRecord, nColumn, 0); if( pOrderBy ){ | | | 516 517 518 519 520 521 522 523 524 525 526 527 528 529 530 | /* Store the result as data using a unique key. */ case SRT_Table: case SRT_VirtualTab: { sqlite3VdbeAddOp(v, OP_MakeRecord, nColumn, 0); if( pOrderBy ){ pushOntoSorter(pParse, pOrderBy, p); }else{ sqlite3VdbeAddOp(v, OP_NewRowid, iParm, 0); sqlite3VdbeAddOp(v, OP_Pull, 1, 0); sqlite3VdbeAddOp(v, OP_Insert, iParm, 0); } break; } |
︙ | ︙ | |||
527 528 529 530 531 532 533 | sqlite3VdbeAddOp(v, OP_Pop, 1, 0); addr2 = sqlite3VdbeAddOp(v, OP_Goto, 0, 0); if( pOrderBy ){ /* At first glance you would think we could optimize out the ** ORDER BY in this case since the order of entries in the set ** does not matter. But there might be a LIMIT clause, in which ** case the order does matter */ | | | 543 544 545 546 547 548 549 550 551 552 553 554 555 556 557 | sqlite3VdbeAddOp(v, OP_Pop, 1, 0); addr2 = sqlite3VdbeAddOp(v, OP_Goto, 0, 0); if( pOrderBy ){ /* At first glance you would think we could optimize out the ** ORDER BY in this case since the order of entries in the set ** does not matter. But there might be a LIMIT clause, in which ** case the order does matter */ pushOntoSorter(pParse, pOrderBy, p); }else{ char aff = (iParm>>16)&0xFF; aff = sqlite3CompareAffinity(pEList->a[0].pExpr, aff); sqlite3VdbeOp3(v, OP_MakeRecord, 1, 0, &aff, 1); sqlite3VdbeAddOp(v, OP_IdxInsert, (iParm&0x0000FFFF), 0); } sqlite3VdbeJumpHere(v, addr2); |
︙ | ︙ | |||
554 555 556 557 558 559 560 | /* If this is a scalar select that is part of an expression, then ** store the results in the appropriate memory cell and break out ** of the scan loop. */ case SRT_Mem: { assert( nColumn==1 ); if( pOrderBy ){ | | | | 570 571 572 573 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 | /* If this is a scalar select that is part of an expression, then ** store the results in the appropriate memory cell and break out ** of the scan loop. */ case SRT_Mem: { assert( nColumn==1 ); if( pOrderBy ){ pushOntoSorter(pParse, pOrderBy, p); }else{ sqlite3VdbeAddOp(v, OP_MemStore, iParm, 1); /* The LIMIT clause will jump out of the loop for us */ } break; } #endif /* #ifndef SQLITE_OMIT_SUBQUERY */ /* Send the data to the callback function or to a subroutine. In the ** case of a subroutine, the subroutine itself is responsible for ** popping the data from the stack. */ case SRT_Subroutine: case SRT_Callback: { if( pOrderBy ){ sqlite3VdbeAddOp(v, OP_MakeRecord, nColumn, 0); pushOntoSorter(pParse, pOrderBy, p); }else if( eDest==SRT_Subroutine ){ sqlite3VdbeAddOp(v, OP_Gosub, 0, iParm); }else{ sqlite3VdbeAddOp(v, OP_Callback, nColumn, 0); } break; } |
︙ | ︙ | |||
1354 1355 1356 1357 1358 1359 1360 | ** pLimit and pOffset expressions. pLimit and pOffset hold the expressions ** that appear in the original SQL statement after the LIMIT and OFFSET ** keywords. Or NULL if those keywords are omitted. iLimit and iOffset ** are the integer memory register numbers for counters used to compute ** the limit and offset. If there is no limit and/or offset, then ** iLimit and iOffset are negative. ** | | | > | | > > > > > > > > | 1370 1371 1372 1373 1374 1375 1376 1377 1378 1379 1380 1381 1382 1383 1384 1385 1386 1387 1388 1389 1390 1391 1392 1393 1394 1395 1396 1397 1398 1399 1400 1401 1402 1403 1404 1405 1406 1407 1408 1409 1410 1411 1412 1413 1414 1415 1416 1417 1418 1419 1420 1421 1422 1423 1424 1425 1426 1427 1428 1429 1430 1431 | ** pLimit and pOffset expressions. pLimit and pOffset hold the expressions ** that appear in the original SQL statement after the LIMIT and OFFSET ** keywords. Or NULL if those keywords are omitted. iLimit and iOffset ** are the integer memory register numbers for counters used to compute ** the limit and offset. If there is no limit and/or offset, then ** iLimit and iOffset are negative. ** ** This routine changes the values of iLimit and iOffset only if ** a limit or offset is defined by pLimit and pOffset. iLimit and ** iOffset should have been preset to appropriate default values ** (usually but not always -1) prior to calling this routine. ** Only if pLimit!=0 or pOffset!=0 do the limit registers get ** redefined. The UNION ALL operator uses this property to force ** the reuse of the same limit and offset registers across multiple ** SELECT statements. */ static void computeLimitRegisters(Parse *pParse, Select *p, int iBreak){ /* ** "LIMIT -1" always shows all rows. There is some ** contraversy about what the correct behavior should be. ** The current implementation interprets "LIMIT 0" to mean ** no rows. */ if( p->pLimit ){ int iMem = pParse->nMem; pParse->nMem += 2; Vdbe *v = sqlite3GetVdbe(pParse); if( v==0 ) return; sqlite3ExprCode(pParse, p->pLimit); sqlite3VdbeAddOp(v, OP_MustBeInt, 0, 0); sqlite3VdbeAddOp(v, OP_Negative, 0, 0); sqlite3VdbeAddOp(v, OP_MemStore, iMem, 0); VdbeComment((v, "# LIMIT counter")); sqlite3VdbeAddOp(v, OP_IfMemZero, iMem, iBreak); p->iLimit = iMem; } if( p->pOffset ){ int iMem = pParse->nMem++; Vdbe *v = sqlite3GetVdbe(pParse); if( v==0 ) return; sqlite3ExprCode(pParse, p->pOffset); sqlite3VdbeAddOp(v, OP_MustBeInt, 0, 0); sqlite3VdbeAddOp(v, OP_Negative, 0, 0); sqlite3VdbeAddOp(v, OP_MemStore, iMem, p->pLimit==0); VdbeComment((v, "# OFFSET counter")); if( p->pLimit ){ sqlite3VdbeAddOp(v, OP_Add, 0, 0); } p->iOffset = iMem; } if( p->pLimit ){ Vdbe *v = pParse->pVdbe; sqlite3VdbeAddOp(v, OP_MemStore, p->iLimit+1, 1); VdbeComment((v, "# LIMIT+OFFSET")); } } /* ** Allocate a virtual index to use for sorting. */ static void createSortingIndex(Parse *pParse, Select *p, ExprList *pOrderBy){ if( pOrderBy ){ |
︙ | ︙ |