/ Check-in [8e57c313]
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 | SQL 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
Context
2017-04-20
17:03
Avoid creating a temp table in the user database in the sqlite3_expert code. check-in: 4e366996 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: 8e57c313 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: c62e3582 user: dan tags: schemalint-failure
Changes
Hide Diffs Unified Diffs 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
...
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
...
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
....
1699
1700
1701
1702
1703
1704
1705
1706
1707
1708
1709
1710
1711
1712
1713
1714
1715
  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]=='\'' );
................................................................................

/* 
** 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){
................................................................................
  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;
................................................................................
      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);








<
<
<
<
<







 







<
<

<
>
|
|
|
|
<
|
|
<
<
<
<
<
|
|
<
<
<
<







 







<
<
<
<
<


|







 







|
|
|







345
346
347
348
349
350
351





352
353
354
355
356
357
358
...
536
537
538
539
540
541
542


543

544
545
546
547
548

549
550





551
552




553
554
555
556
557
558
559
...
585
586
587
588
589
590
591





592
593
594
595
596
597
598
599
600
601
....
1677
1678
1679
1680
1681
1682
1683
1684
1685
1686
1687
1688
1689
1690
1691
1692
1693
  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]=='\'' );
................................................................................

/* 
** 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){
................................................................................
  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;
................................................................................
      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);