On Thu, 15 Sep 2005, dan greene wrote:
>I wrote a little program to test SQLITE3, essentially, a program to add 200
>records to a table called notes with one column, subject.
>The loop I used to enter the rows is shown below:
>
>// time the additions
>t1=clock();
>// add some rows
>for(i=1;i<201;i++)
>{
>char *statement;
>char buffer[50];
>sprintf(buffer,"my subject%d",i);
>statement = sqlite3_mprintf("insert into notes(subject) values('%q');",buffer);
>stat = sqlite3_exec(db,statement,0,0,&errmsg);
>if(stat != SQLITE_OK)
>{
>printf("insert error at i=%1d: %s\n",i,errmsg);
>sqlite3_free(errmsg);
>break;
>}
>
>sqlite3_free(statement);
>}// for
>t2 = clock();
>printf("added %d records to notes\n",i-1);
>printf("elapsed time: %d\n",(t2-t1));
>
>
>When I ran this program on the win2000 machine with NTFS, this loop took on
>the order of 25 seconds as reported in the second printf!
>When I ran the same program on my win98 machine, it took 1.1 seconds.
>putting a begin; and end;commit; SQL around the entire loop, dropped the
>execution on both machines into the tens of milliseconds.
>
>Any thoughts as to what is happening on the WIN2000 NTFS machine to slow
>down the single inserts so drastically?
Basically, Windows 2000 is making sure your data is safe, whereas Win98
flies fast and lose. Try the same test, pulling the power cord out half
way through, and see which box has an intact database.
SQLite will sync the journal to disk before modifying the database file on
commit, so that it can recover in the above instance. This is a
synchronous operation on Win2000, but appears to be asynchronous on Win98.
Thus, Win2000 is waiting on the slow mechanical hard disk to actually
write data. Win98 is simply caching the write and not waiting on the hard
disk. Win2000 is working correctly. Win98 is not.
That both have around the same execution speed in a single transaction is
expected, as both do a single sync at the end, and thus the sync time
becomes less significant in the running time of the whole program.
>Cheers
>Dan Greene
>
Christian
--
/"\
\ / ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
X - AGAINST MS ATTACHMENTS
/ \
