I would think maybe one performance demo that could be used from the
command line to demonstrate performance and then secondarily show how it is
achieved.
The TRANSACTIONS did do the trick. I saw 3,000 inserts per
second. Could most likely get more performance from it if I want away from
prepping the query each time like explained elsewhere, etc.
Now on the multi threaded demo, I ran into this result. I queried
the first 500 records in my database. And for a single thread all
queries completed below
1 ms. For 500 threads with SQlite serializing the access I would
expect the longest queries to take no more than maybe 500ms. But
alas within 6 queries
I was into queries that took so long I gave up waiting for them.
But if I put my own mutex around the query operation the longest
queries in a 500 thread demo where no longer than 500ms. Which makes
since. So
that is the way I will do things there.
Ok now for a offering. I did speed up performance on both inserts
and queries by a whopping 10x using this function replacement:
/*
** Make sure all writes to a particular file are committed to disk.
*/
static int winSync(sqlite3_file *id, int flags){
winFile *pFile = (winFile*)id;
HANDLE dup_handle;
int retval=SQLITE_IOERR; // assume failure
HANDLE prochandle=GetCurrentProcess(); // get the process handle
OSTRACE3("SYNC %d lock=%d\n", pFile->h, pFile->locktype);
#ifdef SQLITE_TEST
if( flags & SQLITE_SYNC_FULL ){
sqlite3_fullsync_count++;
}
sqlite3_sync_count++;
#endif
if (DuplicateHandle(prochandle, pFile->h, prochandle, &dup_handle, 0, TRUE,
DUPLICATE_SAME_ACCESS) == TRUE) {
if (CloseHandle(dup_handle)!=0)
return SQLITE_OK; // ok the file buffers have been flushed.
}
return(retval);
}
Something that has worked for (11) years for me in application
logging functionality...let me know if anyone finds issue or knows of
issue with it. I have
only tried this on windows NT/2000/XP x64.
David Clark
At 03:31 PM 10/15/2008, Doug wrote:
>I don't mean to pick on the OP, but this is such a commonly asked question
>for people new to SQLite (including me not so long ago) that maybe putting
>something like what you just said many places on the website (besides just
>the Wiki) would help. Perhaps on the INSERT doc page? And maybe make it
>bold?
>
>Doug
>
> > -----Original Message-----
> > From: [EMAIL PROTECTED]
> [<mailto:sqlite-users->mailto:sqlite-users-
> > [EMAIL PROTECTED] On Behalf Of D. Richard Hipp
> > Sent: Wednesday, October 15, 2008 3:16 PM
> > To: [EMAIL PROTECTED]; General Discussion of SQLite Database
> > Subject: Re: [sqlite] basic insert questions...
> >
> >
> > On Oct 15, 2008, at 4:01 PM, Jay A. Kreibich wrote:
> > >
> > > Issue the command "BEGIN", do 100 to 10000 INSERTs, issue a
> > "COMMIT".
> > > You should see a very noticeable difference in speed.
> > >>
> >
> > Just to amplify Jay's words: On a workstation, SQLite should do at
> > least 50,000 INSERTs per second. But due to limitations of spinning
> > disk drives, you can get at most 60 transactions per second. If you
> > do not use BEGIN...COMMIT, then each INSERT is a separate transaction,
> > regardless of whether or not they are in the same string. By doing
> > the BEGIN...COMMIT with the INSERT statements in between, you can do
> > thousands and thousands of fast INSERTs for each relatively slow
> > COMMIT.
> >
> >
> > D. Richard Hipp
> > [EMAIL PROTECTED]
> >
> >
> >
> > _______________________________________________
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
>_______________________________________________
>sqlite-users mailing list
>sqlite-users@sqlite.org
>http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
