[prev in list] [next in list] [prev in thread] [next in thread]
List: sqlite-users
Subject: [sqlite] Transaction?
From: John Hawley <johnhawley () blueyonder ! co ! uk>
Date: 2008-12-31 16:36:46
Message-ID: 495B9F9E.6000605 () blueyonder ! co ! uk
[Download RAW message or body]
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 1 think it should ( the db was opened in earlier code)
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.
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!
initially I suspected that I've somehow done something wrong with the
compiler/ linker settings but now I think its probable that I should be
writing the records using a transaction.
I'm not sure how I would code this to speed things up - any tips would
be greatly appreciated.
Many thanks
Regards
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic