Re: [sqlite] SQLite :memory: performance difference between v2 and v3?

[Dennis Cote]

All,

More mysteries. To investigate this low insert performance under WinXP I 
wrote a simple test program that loops writing one character to a file 
and then flushing the file. The strange thing is that it seems to 
alternate between two different run times as shown below. This is for 6 
consecutive runs.

   C:\src\sqlite_speed>win_flush_test.exe
   26 seconds, 38 flushes/sec

   C:\src\sqlite_speed>win_flush_test.exe
   18 seconds, 56 flushes/sec

   C:\src\sqlite_speed>win_flush_test.exe
   26 seconds, 38 flushes/sec

   C:\src\sqlite_speed>win_flush_test.exe
   19 seconds, 53 flushes/sec

   C:\src\sqlite_speed>win_flush_test.exe
   26 seconds, 38 flushes/sec

   C:\src\sqlite_speed>win_flush_test.exe
   18 seconds, 56 flushes/sec

Does anyone have any ideas what might cause this kind of behavior?

My test code is included below. Note in the version using the standard 
library, fflush() seems to be ignored under WinXP because it does about 
1M flushes per second. Hence the Win32 API version which produces the 
results above.

Dennis Cote




#include <stdio.h>
#include <stdlib.h>
#include <time.h>

#ifdef WIN32

#include <windows.h>

int main(int argc, char *argv[])
{
   int i, count = 1000;
   char c;
   time_t bgn, end;
   double t;
   HANDLE f;
   long written;

   f = CreateFile("test.txt", GENERIC_WRITE, 0, NULL, CREATE_ALWAYS, 
FILE_ATTRIBUTE_NORMAL, 0);

   bgn = time(NULL);
   for (i = 0; i < count; i++) {
       c = 'a' + i % 26;
       WriteFile(f, &c, 1, &written, NULL);
       FlushFileBuffers(f);   
   }
   end = time(NULL);
  
   CloseHandle(f);

   t = difftime(end, bgn);
   printf("%0.0f seconds, %0.0f flushes/sec\n", t, count / t);
  
   //system("PAUSE");  //for Dev-Cpp IDE    
   return 0;
}

#else

int main(int argc, char *argv[])
{
   int i, count = 1000000;
   FILE* f;
   char c;
   time_t bgn, end;
   double t;

   f = fopen("test.txt", "w");

   bgn = time(NULL);
   for (i = 0; i < count; i++) {
       c = 'a' + i % 26;
       fputc(c, f);
       fflush(f);   
   }
   end = time(NULL);
  
   fclose(f);

   t = difftime(end, bgn);
   printf("%0.0f seconds, %0.0f flushes/sec\n", t, count / t);
  
   //system("PAUSE");  //for Dev-Cpp IDE   
   return 0;
}

#endif