And don't forget /D_CRT_DISABLE_PERFCRIT_LOCKS
And make sure you link with the static library (/MT) not the dynamic runtime.

Visual Studio no longer has a single threaded library.  You have to define 
_CRT_DISABLE_PERFCRIT_LOCKS in order to get the compiler to generate code and 
use library routines devoid of extra spinlocks, critical sections, and mutexes 
(ie, to get the same performance as you would get linking with the standard 
LIBC.LIB single-threaded library).

Generated code is still of "Microsoft Quality" though (if you want proper 
instruction scheduling and optimization that actual optimizes anything, then 
use a real compiler, such as gcc or watcom).  I only use MSVC in order to 
integrate the runtimes with other things which use the MS compilers, not 
because they are of any worth.

Also, depending on what the data looks like that you are averaging the 
sum/count average builtin to sqlite may not be very accurate.  You may want to 
do use a running approximation as it is computationally insensitive to the data 
values, ordering, and magnitudes.


typedef struct meanCtx meanCtx;
struct meanCtx
{
    long double m;
    sqlite_int64 n;
};

static void meanStep(sqlite3_context *context, int argc, sqlite3_value **argv)
{
    meanCtx* p = sqlite3_aggregate_context(context, sizeof(meanCtx));
    if (p && sqlite3_value_numeric_type(argv[0]) != SQLITE_NULL)
    {
        p->n++;
        if (p->n == 1)
            p->m = (long double)sqlite3_value_double(argv[0]);
        else
            p->m += ((long double)sqlite3_value_double(argv[0]) - p->m) / (long 
double)p->n;
    }
}

static void meanFinal(sqlite3_context *context)
{
    meanCtx* p = sqlite3_aggregate_context(context, 0);
    if (p && p->n > 0)
        sqlite3_result_double(context, p->m);
}

You can look at http://www.dessus.com/files/sqlite.zip which is a zip of my 
build directory that has an autoload extension (SQLFunc.c) compiled in that 
adds various running statistical aggregates (I make no warranty as to 
correctness by the way -- if you find there is an error please let me know -- 
my brother is the mathematician, not me).  

All aggregates use successive approximation series computation methods.   Also 
includes some useful Windoze Security APIs (get username, check security 
groups, etc) and a hack-in of the test_spellfix1.c module which is also an 
autoloaded extension if you leave the /DINCL_SPELLFIX in the compiler options.  
I'm going to replace that with unifuzz.c recently mentioned since I like that 
better, and extend the selection process and autoinit process.  Eventually I'll 
make a proper makefile.  It has all the options recognized in the amalgamation 
code included.

No warranties expressed or implied.  If it breaks or you break it then you own 
both halves.

---
()  ascii ribbon campaign against html e-mail
/\  www.asciiribbon.org


> -----Original Message-----
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Richard Hipp
> Sent: Thursday, 24 May, 2012 14:10
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] SQLite SELECT performance problem
> 
> On Thu, May 24, 2012 at 3:59 PM, Nick <eveningn...@gmail.com> wrote:
> 
> >
> > Any way I could make my C program execute this query as fast as the
> > prebuilt command line tool does it?
> >
> 
> 
> Have you tried compiling with the -DSQLITE_THREADSAFE=0 option?
> 
> --
> D. Richard Hipp
> d...@sqlite.org
> _______________________________________________
> 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

Reply via email to