none
Sqlite RRS feed

  • Question

  • Sorry if the question seems dumb but I last used MS compilers and linkers back in the early 90's - they seem to have changed somewhat in complexity!

    OK here's the problem:-

    I decided I needed a little app to manage some data in 3rd party binary files, specifically, I needed to store the user generated data from the files in a database so that I could retrieve the data in a massaged form. As others have also decided that they need to do the same thing I decided to generate a simple Windows native API app to do the task ( I have a command line Linux task that does the same thing but that sort of thing is not to everybody's taste!) . I downloaded the VC++ 2008 express edition, read up how Windows API worked and wrote the little app using sqlite as the serverless db engine. No problems so far, the app works as intended BUT

    the following block of code executes 2 or 3 orders of magnitude slower than it should

            RecOffset=sizeof(rec);
            fseek(infile,126,SEEK_SET);            // data starts at byte 126
            while (fread(&rec,sizeof(rec),1,infile))
            {
                iSeq++;
                GridY= (long) rec.lat*LATGRIDFACTOR + 0.5;
                GridX= (long) rec.longt*LongGridFactor(rec.lat) + 0.5;
                iGS =GridX*10000000 + GridY;
                sprintf_s(SqlStr,200,"INSERT into soundings VALUES(%lf,%lf,%f,%s,%d,%ld);",rec.lat,
                    rec.longt,rec.depth,DateStr,iSeq,iGS);
                rc= sqlite3_exec(db,SqlStr,NULL,0,&zErrMsg);
                if(rc!=SQLITE_OK)
                {
                    MessageBox(hwnd,zErrMsg,"SQL Error adding soundings data",MB_OK);
                    exit(1);
                }   

            }

    The whole program will read in 10,000 records /sec if

                rc= sqlite3_exec(db,SqlStr,NULL,0,&zErrMsg);
                if(rc!=SQLITE_OK)
                {
                    MessageBox(hwnd,zErrMsg,"SQL Error adding soundings data",MB_OK);
                    exit(1);
                }   

    is commented out, with the code in place, it reads around 10 records/sec ( but does it correctly) with masses of disk i/o but very little cpu activity.

    Nasty slow db engine I hear you say but if you compile and execute the same code on the same hardware under Ubuntu Linux, the 7000 records in the test file are processed in under a couple of seconds (cf 10 minutes under Vista)

    I have tried using the sqlite subroutines as both static libraries, DLL's and on the last attempt, I just added the sqlite3 sources and headers to the project and compiled the lot together. All work but slowly!


    I suspect that I've somehow done something wrong with the compiler/ linker settings  - anyone any idea where I should look next.

    ( I wish to stick with sqlite if possible as I use the code across platforms)

    Many thanks in advance.


    Wednesday, December 31, 2008 1:09 PM

Answers

  • Apologies all - I needed to enclose the code into an sql transaction - inserts now proceed at a normal pace (several thousand/sec)
    • Marked as answer by bouncingwilf Wednesday, December 31, 2008 8:35 PM
    Wednesday, December 31, 2008 8:35 PM