Re: [sqlite] SQLite performance for 10 Million Records

[Eduardo]

At 03:09 23/06/2006, you wrote:

#include "stdafx.h"

<....>

samples, t, samples / t);
        getch();
//*********************************************************************

Here you should create index for table t. In your previous example, for hvh itm

          // Select Time check
//*********************************************************************
    bgn = time(NULL);
    sqlite3_exec(db, "Select * from t", NULL, NULL, NULL);
    end = time(NULL);
    t = difftime(end, bgn);
    printf("Select in %.0f seconds", t);
    getch();
//*********************************************************************

        sqlite3_close(db);

}

//******************************************************

DISK MODE
3000 000 INSERTS   31 Seconds   96774 INSERTS / Sec
"SELECT * from t"   5 Seconds.

MEMORY MODE
3000 000 INSERTS   53 Seconds   56604 INSERTS / Sec
"SELECT * from t"   5 Seconds.

Can I reduce the TIME of DISK mode or this is the limit.

I'm doing a little patch for mine SQLite implementation, but have not 
checked nor stressed nor benchmark nor even compiled!!!, also if this 
patch corrupt any data, is unkown, i'll do it next monday. In btree.c 
(verison 1.324) change from line 4861 to line 4880, the original text says:

for(i=0; i<k-1; i++){
    int minV = pgnoNew[i];
    int minI = i;
    for(j=i+1; j<k; j++){
      if( pgnoNew[j]<(unsigned)minV ){
        minI = j;
        minV = pgnoNew[j];
      }
    }
    if( minI>i ){
      int t;
      MemPage *pT;
      t = pgnoNew[i];
      pT = apNew[i];
      pgnoNew[i] = pgnoNew[minI];
      apNew[i] = apNew[minI];
      pgnoNew[minI] = t;
      apNew[minI] = pT;
    }
  }

i just changed the insertion sort (O(n^2)) for a modified bubble sort 
(added 5 lines) called comb sort which is O (nlog n). It's used as 
final step for more complex sort algorithm as quick, radix. If you or 
anyone discover a bug or want to benchmark or modifies it, please say 
here or mail me. I expect to boost inserts and deletes. Copyrigth 
under dr. hwaci license unmodified. My code is:

int gap = k;    /*LINE 4861
for (i=0; i<k-1; i++) {
    int swapped = 0;
    gap = gap * 10 / 13;     /* Added line to bubble sort
    if (gap < 1)             /* Added line to bubble sort
        gap = 1;             /* Added line to bubble sort
    if (gap == 9 || gap == 10)/* Added line to bubble sort
        gap = 11;             /* Added line to bubble sort
    minV = pgnoNew[i];
    for (j = 0; j < k - gap; j++) {  /* Changed line to bubble sort
      if (pgnoNew [j] < minV) {
            int t;
          MemPage *pT;
          t = pgnoNew[i];
          pT = apNew[i];
          pgnoNew[i] = pgnoNew[minI];
          apNew[i] = apNew[minI];
          pgnoNew[minI] = t;
          apNew[minI] = pT;
            swapped = 1;
      }
    }
    if (gap == 1 && !swapped)
      break;
  }

If it's not enough for you, perhaps you need a greater rdbms.


Antivirus. Warning: User detected. Please, keep away from computer or 
you will be eliminated. Thanks.