Re: [sqlite] Sqlite reading all column data on selects.
In the relational model, the unit of access is a tuple so you would expect a DBMS to process all columns. Typically they also access in units of a page (although a tuple of course might extend across more than one page) The discussion seems to have moved onto selecting pages. Surely if you need to do this, stick an index on the column concerned. The index's purpose is to take you directly to the target page. -- View this message in context: http://www.nabble.com/Sqlite-reading-all-column-data-on-selects.-tp25594620p25641328.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sqlite reading all column data on selects.
Am Thu, 24 Sep 2009 14:18:33 -0400 schrieb D. Richard Hipp: > When autovacuum=FULL or autovacuum=INCREMENTAL and SQLite needs to > seek to the end of a long chain of overflow pages, it attempts to use > the "pointer map pages" that are available in these modes to locate > the pages without actually reading the prior pages from the disk. Key > word: "attempts". There is no guarantee of success. But if the > database is not too badly fragmented, it will usually succeed. > > When autovacuum=OFF, then pointer map pages are not availble and > SQLite is compelled to read all prior pages when seeking to the end of > an overflow chain. That's interesting. So that means that if I create and fill an SQLite database that is intended for later read-only access, I get an additional performance boost with autovacuum=OFF as long as the database is created and filled with data, and lateron I switch to autovacuum=FULL (or INCREMENTAL) when I'm done? I.e. the latest after an explicit VACUUM when I'm finished writing to the database, there would be no fragmentation present, and read access would be perfectly optimized with any autovacuum pragma other than OFF? Thanks, Wolfgang ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sqlite reading all column data on selects.
On Sep 24, 2009, at 2:09 PM, Martin Pfeifle wrote: > Hi Richard, > > assume I have a table mytable (id, blob1,blob2,blob3,blob4) > where each blob extends over several pages. > > Then I do the following SQL command: > select blob4 from mytable where id = 4711 > > Do I understand you correctly that in the case autuvacuum =true, > the pages covered by blob1 to blob3 are not read from disk, > whereas in the case autovaccum=false they are also read from disk > into main memory? > When autovacuum=FULL or autovacuum=INCREMENTAL and SQLite needs to seek to the end of a long chain of overflow pages, it attempts to use the "pointer map pages" that are available in these modes to locate the pages without actually reading the prior pages from the disk. Key word: "attempts". There is no guarantee of success. But if the database is not too badly fragmented, it will usually succeed. When autovacuum=OFF, then pointer map pages are not availble and SQLite is compelled to read all prior pages when seeking to the end of an overflow chain. D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sqlite reading all column data on selects.
Hi Richard, assume I have a table mytable (id, blob1,blob2,blob3,blob4) where each blob extends over several pages. Then I do the following SQL command: select blob4 from mytable where id = 4711 Do I understand you correctly that in the case autuvacuum =true, the pages covered by blob1 to blob3 are not read from disk, whereas in the case autovaccum=false they are also read from disk into main memory? Martin Von: D. Richard Hipp An: General Discussion of SQLite Database Gesendet: Donnerstag, den 24. September 2009, 14:58:16 Uhr Betreff: Re: [sqlite] Sqlite reading all column data on selects. On Sep 24, 2009, at 8:42 AM, Adam Panayis wrote: > Hi, are there plans to stop sqlite3 from reading in all column data on > specific select queries? i.e I would like sqlite to ONLY read column > data for columns which are specified in the select and where clauses. Short answer: Already does that. Long answer: SQLite pulls content from the disk one page at a time. If all the data is on the same page (the common case) then all the data will always be read from disk. There is no getting around that. If the row is large and does not fit on a single page, then SQLite strives to avoid reading any pages that are not actually needed. That is already implemented. Because of the file format, sometimes earlier pages must be read in order to find the location of later pages. In other cases (when you have autovacuum set) SQLite is able to guess the location of later pages without having to read earlier pages. SQLite never reads pages that follow what is needed. Once the necessary pages are in memory, SQLite only looks at the specific parts of a row that are requested. Unrequested columns are never extracted or decoded from the raw row data. D. Richard Hipp d...@hwaci.com ___ 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
Re: [sqlite] Sqlite reading all column data on selects.
On Sep 24, 2009, at 8:42 AM, Adam Panayis wrote: > Hi, are there plans to stop sqlite3 from reading in all column data on > specific select queries? i.e I would like sqlite to ONLY read column > data for columns which are specified in the select and where clauses. Short answer: Already does that. Long answer: SQLite pulls content from the disk one page at a time. If all the data is on the same page (the common case) then all the data will always be read from disk. There is no getting around that. If the row is large and does not fit on a single page, then SQLite strives to avoid reading any pages that are not actually needed. That is already implemented. Because of the file format, sometimes earlier pages must be read in order to find the location of later pages. In other cases (when you have autovacuum set) SQLite is able to guess the location of later pages without having to read earlier pages. SQLite never reads pages that follow what is needed. Once the necessary pages are in memory, SQLite only looks at the specific parts of a row that are requested. Unrequested columns are never extracted or decoded from the raw row data. D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sqlite reading all column data on selects.
Adam Panayis wrote: > Hi, are there plans to stop sqlite3 from reading in all column data on > specific select queries? i.e I would like sqlite to ONLY read column > data for columns which are specified in the select and where clauses. What do you mean by "reading in" here? I'm not sure I understand precisely what behavior you find objectionable. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sqlite reading all column data on selects.
Hello, Adam, Regarding: "... are there plans to stop sqlite3 from reading in all column data on specific select queries? i.e I would like sqlite to ONLY read column data for columns which are specified in the select and where clauses." I'm no sqlite expert, and moreover I'm not sure I understand your question properly. Are you wanting to prevent sqlite from transferring data columns from disk when those columns are not needed? If so, I don't think that's generally feasible. You might want to look at sqlite's (and other database system's) architechture. Sqlite will instruct the operating system to read *pages* that it needs to complete your requests. That being said, you *can* sometimes improve performance by locating less-used columns later in your table definition (i.e., "to the right of" the more-used columns). Even better, you can place large blob-ish fields into separate tables, along with an indexing identifier, in the hope that these pages will be read only when required. What problem are you attempting to solve? Regards, Donald ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Sqlite reading all column data on selects.
Hi, are there plans to stop sqlite3 from reading in all column data on specific select queries? i.e I would like sqlite to ONLY read column data for columns which are specified in the select and where clauses. Thanks. Adam. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
