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
