SQLite needs to guarantee transaction integrity and concurrent accesses (more than one process could be writing and reading a SQLite database at the same time). It has to go to greath lengths to ensure that the data actually gets written to disk instead of staying in memory cache. All this has overhead. If your application doesn't require these extra features, then the flat-file approach you describe may work better for you.
g -----Original Message----- From: Robert Simpson [mailto:[EMAIL PROTECTED] Sent: Monday, September 29, 2008 3:30 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Does sqlite caches data to speed up transaction This isn't unexpected at all. The overhead of using a database (even SQLite) is much much higher than seeking to an area of an open file and reading some bytes out of it. ---------------------------------------- From: devesh tiwari <[EMAIL PROTECTED]> Sent: Monday, September 29, 2008 12:15 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Does sqlite caches data to speed up transaction I am not seeing this performance difference in write but in reading database. Following is the sequence i am performing for reading: 1. sqlite3_open() 2. sqlite3_prepare() 3. sqlite3_bind_int() 4. sqlite3_step() 5. sqlite3_column() 6. sqlite3_finalize() 7. sqlite3_close() steps 1 , 2,6& 7 are done only once and i am also doing sqlite_reset() before i use sqlite3_bind_int() again to create a query. the query is always of the form: select column from table where index='index_no'; so i need to change table name and index_no only for the next query. If i dont use sqlite than sequence of reading is: 1) open file 2) seek in the file(seek value is simply calculated as index*size_of_structure) 3) read 4)close file In this case file is opened once and only steps 2 & 3 need to perform for reading data and this is 10 times faster than sqlite. Thanks & Regards Devesh Kumar Tewari --- On Mon, 9/29/08, [EMAIL PROTECTED] wrote: > From: [EMAIL PROTECTED] > Subject: sqlite-users Digest, Vol 9, Issue 88 > To: sqlite-users@sqlite.org > Date: Monday, September 29, 2008, 9:30 PM > Send sqlite-users mailing list submissions to > sqlite-users@sqlite.org > > To subscribe or unsubscribe via the World Wide Web, visit > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > or, via email, send a message with subject or body > 'help' to > [EMAIL PROTECTED] > > You can reach the person managing the list at > [EMAIL PROTECTED] > > When replying, please edit your Subject line so it is more > specific > than "Re: Contents of sqlite-users digest..." > > > Today's Topics: > > 1. Re: Duplicated primary key error (Mariano Martinez > Peck) > 2. Re: Duplicated primary key error (P Kishor) > 3. Re: Duplicated primary key error (Mariano Martinez > Peck) > 4. Does sqlite caches data to speed up transaction time? > (devesh tiwari) > 5. Re: Does sqlite caches data to speed up transaction > time? > (Jay A. Kreibich) > 6. Re: Duplicated primary key error (Dennis Cote) > > > ---------------------------------------------------------------------- > > Message: 1 > Date: Sun, 28 Sep 2008 21:03:39 -0300 > From: "Mariano Martinez Peck" > > Subject: Re: [sqlite] Duplicated primary key error > To: sqlite-users@sqlite.org > Message-ID: > > Content-Type: text/plain; charset=ISO-8859-1 > > Please I need help with this :( > > I am getting BUSY or IOERR_BLOCKED when inserting a > duplicated pk through C > interface. > > If I do it through sqlite.exe I get: SQL error: PRIMARY KEY > must be unique > > Any ideas? > > many thanks, > > mariano > > > > On Sun, Sep 21, 2008 at 9:05 PM, Mariano Martinez Peck < > [EMAIL PROTECTED]> wrote: > > > Hi everybody! > > > > This is my first post in this list! I am very newbie > with Sqlite. This is > > the first time I am trying to use it. I am using > Sqlite3 trough C interface. > > The problem I have is this: I have a table created, > just like this: > > > > CREATE TABLE materia( > > codigo INTEGER PRIMARY KEY, > > nombre CHARACTER VARYING(50), > > observaciones CHARACTER VARYING(255), > > ) > > > > Then I do 2 inserts one after the other, with the same > data. For example: > > > > INSERT INTO materia(codigo, nombre, observaciones) > VALUES (55, ''TADP'', > > ''Nothing") > > > > After doing this, I thought the second query ( i am > using sqlite3_step() > > function ) will returns me a > SQLITE_ERROR. > > However, it SQLITE_IOERR_BLOCKED. > > > > Is this correct? what should sqlite3_step returns me > in this case? > > > > Many thanks for the help, > > > > Mariano > > . > > > > > > > ------------------------------ > > Message: 2 > Date: Sun, 28 Sep 2008 19:15:47 -0500 > From: "P Kishor" > Subject: Re: [sqlite] Duplicated primary key error > To: "General Discussion of SQLite Database" > > Message-ID: > > Content-Type: text/plain; charset=ISO-8859-1 > > On 9/28/08, Mariano Martinez Peck > wrote: > > Please I need help with this :( > > > > I am getting BUSY or IOERR_BLOCKED when inserting a > duplicated pk through C > > interface. > > > > If I do it through sqlite.exe I get: SQL error: > PRIMARY KEY must be unique > > > > Any ideas? > > Yes. The PRIMARY KEY must be unique. In other words, it > should not be > a duplicate of one that already exists. That is the whole > idea behind > a PRIMARY KEY. Which part of that is causing confusion? > > > > > many thanks, > > > > > > mariano > > > > > > > > > > On Sun, Sep 21, 2008 at 9:05 PM, Mariano Martinez > Peck < > > [EMAIL PROTECTED]> wrote: > > > > > Hi everybody! > > > > > > This is my first post in this list! I am very > newbie with Sqlite. This is > > > the first time I am trying to use it. I am using > Sqlite3 trough C interface. > > > The problem I have is this: I have a table > created, just like this: > > > > > > CREATE TABLE materia( > > > codigo INTEGER PRIMARY KEY, > > > nombre CHARACTER VARYING(50), > > > observaciones CHARACTER VARYING(255), > > > ) > > > > > > Then I do 2 inserts one after the other, with > the same data. For example: > > > > > > INSERT INTO materia(codigo, nombre, > observaciones) VALUES (55, ''TADP'', > > > ''Nothing") > > > > > > After doing this, I thought the second query ( i > am using sqlite3_step() > > > function ) will returns me a > SQLITE_ERROR. > > > However, it SQLITE_IOERR_BLOCKED. > > > > > > Is this correct? what should sqlite3_step > returns me in this case? > > > > > > Many thanks for the help, > > > > > > Mariano > > > . > > > > > > > > _______________________________________________ > > sqlite-users mailing list > > sqlite-users@sqlite.org > > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > > -- > Puneet Kishor http://punkish.eidesis.org/ > Nelson Institute for Environmental Studies > http://www.nelson.wisc.edu/ > Open Source Geospatial Foundation (OSGeo) > http://www.osgeo.org/ > > > ------------------------------ > > Message: 3 > Date: Sun, 28 Sep 2008 21:28:46 -0300 > From: "Mariano Martinez Peck" > > Subject: Re: [sqlite] Duplicated primary key error > To: [EMAIL PROTECTED], "General Discussion of > SQLite Database" > > Message-ID: > > Content-Type: text/plain; charset=ISO-8859-1 > > Sorry for my English. I cannot express well my problem. > > The thing is: I know how primary key works, but I don't > understand the error > I am getting from sqlite3. I mean, If I tried to execute a > query which is an > insert with an existing pk, is it ok to receive BUSY or > IOERR_BLOCKED ? > That is what I am getting. But I think this error has > nothing to do with > duplicated pk. Shouldn't I get an SQLITE_ERROR or > something like that ? > > could I explain it well my problem now? > > many thanks, > > mariano > > > On Sun, Sep 28, 2008 at 9:15 PM, P Kishor > wrote: > > > On 9/28/08, Mariano Martinez Peck > wrote: > > > Please I need help with this :( > > > > > > I am getting BUSY or IOERR_BLOCKED when > inserting a duplicated pk > > through C > > > interface. > > > > > > If I do it through sqlite.exe I get: SQL error: > PRIMARY KEY must be > > unique > > > > > > Any ideas? > > > > Yes. The PRIMARY KEY must be unique. In other words, > it should not be > > a duplicate of one that already exists. That is the > whole idea behind > > a PRIMARY KEY. Which part of that is causing > confusion? > > > > > > > > many thanks, > > > > > > > > > mariano > > > > > > > > > > > > > > > On Sun, Sep 21, 2008 at 9:05 PM, Mariano > Martinez Peck < > > > [EMAIL PROTECTED]> wrote: > > > > > > > Hi everybody! > > > > > > > > This is my first post in this list! I am > very newbie with Sqlite. This > > is > > > > the first time I am trying to use it. I am > using Sqlite3 trough C > > interface. > > > > The problem I have is this: I have a table > created, just like this: > > > > > > > > CREATE TABLE materia( > > > > codigo INTEGER PRIMARY KEY, > > > > nombre CHARACTER VARYING(50), > > > > observaciones CHARACTER VARYING(255), > > > > ) > > > > > > > > Then I do 2 inserts one after the other, > with the same data. For > > example: > > > > > > > > INSERT INTO materia(codigo, nombre, > observaciones) VALUES (55, > > ''TADP'', > > > > ''Nothing") > > > > > > > > After doing this, I thought the second > query ( i am using > > sqlite3_step() > > > > function ) will returns me a > SQLITE_ERROR< > > http://www.sqlite.org/c3ref/c_abort.html>. > > > > However, it SQLITE_IOERR_BLOCKED. > > > > > > > > Is this correct? what should sqlite3_step > returns me in this case? > > > > > > > > Many thanks for the help, > > > > > > > > Mariano > > > > . > > > > > > > > > > > _______________________________________________ > > > sqlite-users mailing list > > > sqlite-users@sqlite.org > > > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > > > > > > -- > > Puneet Kishor http://punkish.eidesis.org/ > > Nelson Institute for Environmental Studies > http://www.nelson.wisc.edu/ > > Open Source Geospatial Foundation (OSGeo) > http://www.osgeo.org/ > > _______________________________________________ > > sqlite-users mailing list > > sqlite-users@sqlite.org > > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > > ------------------------------ > > Message: 4 > Date: Mon, 29 Sep 2008 03:44:46 -0700 (PDT) > From: devesh tiwari > Subject: [sqlite] Does sqlite caches data to speed up > transaction > time? > To: sqlite-users@sqlite.org > Message-ID: > <[EMAIL PROTECTED]> > Content-Type: text/plain; charset=us-ascii > > Hi all, > I am working on an application that frequently uses > multiple files for storing and retrieving data.Now the > number of files to store data as reached around 150 and it > is becoming difficult to handle multiple files so i decided > to move to a database system. When I heard of sqlite, I > thought this can solve my purpose as no configuration is > required. > When I used sqlite to store data, I discovered that > writing/reading data using sqlite is vary slow as compared > to direct reading/writing file(in my case 10 times slower). > I wonder if sqlite is really slow or i am missing something > at my end. > Does sqlite does any caching of frequently used data or > only relies on OS caching? > > > Thanks & Regards > Devesh Kumar Tewari > Mo. 09810774572 > > > > > > ------------------------------ > > Message: 5 > Date: Mon, 29 Sep 2008 06:52:46 -0500 > From: "Jay A. Kreibich" > Subject: Re: [sqlite] Does sqlite caches data to speed up > transaction > time? > To: [EMAIL PROTECTED], General Discussion of SQLite > Database > > Message-ID: <[EMAIL PROTECTED]> > Content-Type: text/plain; charset=us-ascii > > On Mon, Sep 29, 2008 at 03:44:46AM -0700, devesh tiwari > scratched on the wall: > > Hi all, > > When I used sqlite to store data, I discovered that > writing/reading > > data using sqlite is vary slow as compared to direct > reading/writing > > file(in my case 10 times slower). > > > > I wonder if sqlite is really slow or i am missing > something at my end. > > > > Does sqlite does any caching of frequently used data > or only relies > > on OS caching? > > SQLite does have an internal page-cache. The default > cache size is > 2000 pages, the default page size is 1K. You can > increase the size > of the cache (or page size) using PRAGMA commands: > > http://www.sqlite.org/pragma.html > > The most likely reason you are seeing a performance > difference vs. > flat files is not read/writes, but writes in specific. > SQLite does > not cache "dirty" pages and blocks on writes > until (as best as it can > tell) the data has been fully written to disk. This > by-passes the OS > write buffers and can make the write process much slower. > You can turn > this off, so that writes are not blocked, but you risk > transaction and > database corruption in the case of a process or systems > failure. > > > The end result is that SQLite will be slower compared to > simple, > small, flat files, but SQLite will provide a *much* > higher level of > data and transaction integrity. > > -j > > -- > Jay A. Kreibich < J A Y @ K R E I B I.C H > > > "Our opponent is an alien starship packed with atomic > bombs. We have > a protractor." "I'll go home and see if I > can scrounge up a ruler > and a piece of string." --from Anathem by Neal > Stephenson > > > ------------------------------ > > Message: 6 > Date: Mon, 29 Sep 2008 07:41:00 -0600 > From: Dennis Cote > Subject: Re: [sqlite] Duplicated primary key error > To: General Discussion of SQLite Database > > Message-ID: <[EMAIL PROTECTED]> > Content-Type: text/plain; charset=ISO-8859-1; format=flowed > > Mariano Martinez Peck wrote: > > > > This is my first post in this list! I am very newbie > with Sqlite. This is > > the first time I am trying to use it. I am using > Sqlite3 trough C interface. > > The problem I have is this: I have a table created, > just like this: > > > > CREATE TABLE materia( > > codigo INTEGER PRIMARY KEY, > > nombre CHARACTER VARYING(50), > > observaciones CHARACTER VARYING(255), > > ) > > > > Then I do 2 inserts one after the other, with the same > data. For example: > > > > INSERT INTO materia(codigo, nombre, observaciones) > VALUES (55, ''TADP'', > > ''Nothing") > > > > After doing this, I thought the second query ( i am > using sqlite3_step() > > function ) will returns me a > > > SQLITE_ERROR. > > However, it SQLITE_IOERR_BLOCKED. > > > > Is this correct? what should sqlite3_step returns me > in this case? > > > > Are you resetting the query with sqlite3_reset before you > execute the > sqlite3_step function the second time? This should also > generate an > different error (possibly SQLITE_MISUSE) but I just want to > be sure what > you are doing when you get the IOERR return. It would be > best if you > could post the code you are using to prepare and execute > the query. > > Dennis Cote > > > ------------------------------ > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > End of sqlite-users Digest, Vol 9, Issue 88 > ******************************************* _______________________________________________ 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
