SQLite

Check-in [8e57c31340]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Speed this branch up a bit by filtering before the virtual table layer when sampling user data.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | schemalint-failure
Files: files | file ages | folders
SHA3-256: 8e57c31340dd9ffc457da63c5996fb1b573f8154f864ec2b52c15f399906ac8b
User & Date: dan 2017-04-20 16:43:32.927
Context
2017-04-20
17:03
Avoid creating a temp table in the user database in the sqlite3_expert code. (check-in: 4e36699643 user: dan tags: schemalint)
16:43
Speed this branch up a bit by filtering before the virtual table layer when sampling user data. (Closed-Leaf check-in: 8e57c31340 user: dan tags: schemalint-failure)
16:08
Avoid creating a temp table in the user database in the sqlite3_expert code. Trouble is, this makes sampling for stat1 data much slower. (check-in: c62e358243 user: dan tags: schemalint-failure)
Changes
Unified Diff Ignore Whitespace Patch
Changes to ext/expert/sqlite3expert.c.
︙︙
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363

  sqlite3expert *pExpert;
};

typedef struct ExpertCsr ExpertCsr;
struct ExpertCsr {
  sqlite3_vtab_cursor base;
  sqlite3_stmt *pData;

  int iTarget;                    /* Target as a percentage */
  double target;                  /* Target nRet/nRow value */
  double nRow;                    /* Rows seen */
  double nRet;                    /* Rows returned */
};

static char *expertDequote(const char *zIn){
  int n = strlen(zIn);
  char *zRet = sqlite3_malloc(n);

  assert( zIn[0]=='\'' );








<
<
<
<
<








345
346
347
348
349
350
351





352
353
354
355
356
357
358

  sqlite3expert *pExpert;
};

typedef struct ExpertCsr ExpertCsr;
struct ExpertCsr {
  sqlite3_vtab_cursor base;
  sqlite3_stmt *pData;





};

static char *expertDequote(const char *zIn){
  int n = strlen(zIn);
  char *zRet = sqlite3_malloc(n);

  assert( zIn[0]=='\'' );
︙︙
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576


/* 
** Virtual table module xNext method.
*/
static int expertNext(sqlite3_vtab_cursor *cur){
  ExpertCsr *pCsr = (ExpertCsr*)cur;
  int rc = SQLITE_OK;
  int bRet;
  assert( pCsr->pData );

  do {
    rc = sqlite3_step(pCsr->pData);
    if( rc!=SQLITE_ROW ){
      rc = sqlite3_finalize(pCsr->pData);
      pCsr->pData = 0;
      bRet = 1;
    }else{
      rc = SQLITE_OK;
      bRet = (pCsr->nRow==0.0 || pCsr->nRow/pCsr->nRet < pCsr->target);
      if( bRet==0 ){
        unsigned short rnd;
        sqlite3_randomness(2, (void*)&rnd);
        bRet = ((int)rnd % 100) <= pCsr->iTarget;
      }
    }
    pCsr->nRow += 1.0;
  }while( bRet==0 );

  pCsr->nRet += 1.0;
  return rc;
}

/* 
** Virtual table module xRowid method.
*/
static int expertRowid(sqlite3_vtab_cursor *cur, sqlite_int64 *pRowid){








|

<
<
|
|
|
|
<
|
|
<
<
<
<
<
|
|
<
<
<
<








536
537
538
539
540
541
542
543
544


545
546
547
548

549
550





551
552




553
554
555
556
557
558
559


/* 
** Virtual table module xNext method.
*/
static int expertNext(sqlite3_vtab_cursor *cur){
  ExpertCsr *pCsr = (ExpertCsr*)cur;
  int rc = SQLITE_OK;

  assert( pCsr->pData );


  rc = sqlite3_step(pCsr->pData);
  if( rc!=SQLITE_ROW ){
    rc = sqlite3_finalize(pCsr->pData);
    pCsr->pData = 0;

  }else{
    rc = SQLITE_OK;





  }





  return rc;
}

/* 
** Virtual table module xRowid method.
*/
static int expertRowid(sqlite3_vtab_cursor *cur, sqlite_int64 *pRowid){
︙︙
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623

  ExpertCsr *pCsr = (ExpertCsr*)cur;
  ExpertVtab *pVtab = (ExpertVtab*)(cur->pVtab);
  sqlite3expert *pExpert = pVtab->pExpert;
  int rc;

  rc = sqlite3_finalize(pCsr->pData);
  pCsr->pData = 0;
  pCsr->nRow = 0.0;
  pCsr->nRet = 0.0;
  pCsr->iTarget = pExpert->iSample;
  pCsr->target = (double)pExpert->iSample / 100.0;

  if( rc==SQLITE_OK ){
    rc = idxPrintfPrepareStmt(pExpert->db, &pCsr->pData, &pVtab->base.zErrMsg,
        "SELECT * FROM main.%Q", pVtab->pTab->zName
    );
  }

  if( rc==SQLITE_OK ){
    rc = expertNext(cur);
  }
  return rc;








<
<
<
<
<


|








585
586
587
588
589
590
591





592
593
594
595
596
597
598
599
600
601

  ExpertCsr *pCsr = (ExpertCsr*)cur;
  ExpertVtab *pVtab = (ExpertVtab*)(cur->pVtab);
  sqlite3expert *pExpert = pVtab->pExpert;
  int rc;

  rc = sqlite3_finalize(pCsr->pData);
  pCsr->pData = 0;





  if( rc==SQLITE_OK ){
    rc = idxPrintfPrepareStmt(pExpert->db, &pCsr->pData, &pVtab->base.zErrMsg,
        "SELECT * FROM main.%Q WHERE sample()", pVtab->pTab->zName
    );
  }

  if( rc==SQLITE_OK ){
    rc = expertNext(cur);
  }
  return rc;
︙︙
1699
1700
1701
1702
1703
1704
1705
1706
1707
1708
1709
1710
1711
1712
1713
1714
1715

      samplectx.nRet = 0.0;
      rc = idxBuildSampleTable(p, zTab);
      if( rc!=SQLITE_OK ) break;
    }
    rc = idxPopulateOneStat1(p, pIndexXInfo, pWrite, zTab, zIdx, pzErr);
    iPrev = iRowid;
  }
  if( p->iSample<100 ){
    rc = sqlite3_exec(p->db, "DROP TABLE IF EXISTS temp." UNIQUE_TABLE_NAME,
        0,0,0
    );
  }

  idxFinalize(&rc, pAllIndex);
  idxFinalize(&rc, pIndexXInfo);
  idxFinalize(&rc, pWrite);









|
|
|








1677
1678
1679
1680
1681
1682
1683
1684
1685
1686
1687
1688
1689
1690
1691
1692
1693

      samplectx.nRet = 0.0;
      rc = idxBuildSampleTable(p, zTab);
      if( rc!=SQLITE_OK ) break;
    }
    rc = idxPopulateOneStat1(p, pIndexXInfo, pWrite, zTab, zIdx, pzErr);
    iPrev = iRowid;
  }
  if( rc==SQLITE_OK && p->iSample<100 ){
    rc = sqlite3_exec(p->dbv, 
        "DROP TABLE IF EXISTS temp." UNIQUE_TABLE_NAME, 0,0,0
    );
  }

  idxFinalize(&rc, pAllIndex);
  idxFinalize(&rc, pIndexXInfo);
  idxFinalize(&rc, pWrite);
︙︙