Re: [sqlite] slow select from table with data blob
@Jay: Thanks for the official information. >You'll see the slow down anytime you access anything "past" the BLOB. >To avoid that, put the BLOBs at the end of the rows and avoid "SELECT *" style >queries. Yes this is exactly what I found.. The problem with putting them at the end of the table is that if you subsequently need to add other columns there is a problem... @Puneet: Thanks for the info.. >That is a known feature (issue). I am sure it is written up somewhere, but >definitely, on this list, Richard Hipp and others have emphasized this many >times -- search the mailing list archives. I did not come across this information so far. It should be included somewhere a bit more obvious... Thanks Rael ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] slow select from table with data blob
On Sat, 29 Jan 2011 15:07:47 -0800 (PST) Rael Bauer wrote: > It seemed strange that a simple "select * from table" that I was > doing was so slow. The table contained about 20 columns (fields) and > 300 rows. The select took about 1.5 seconds. (using SQLite Expert). > > So my questions: > Is this standard behaviour for sql databases? (that I have only found > out now). Is there actually some way to bypass this "problem" (e.g. > database setting..)? Is it generally advisable to separate out blob > fields into their own table to ensure fast select speeds? You should normalize your schema, 20 columns are too many columns. Also, consider to write the blob as file outside the database and put just the path in the database > > Thanks > Rael Bauer HTH ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] slow select from table with data blob
>It seemed strange that a simple "select * from table" that I was doing >was so slow. The table contained about 20 columns (fields) and 300 >rows. The select took about 1.5 seconds. (using SQLite Expert). Does the run time settle at 1.5 s after a few runs or is that a first-run time ? As an aside to what others have answered I'd like to point out another potential source of slowdown. I too am using SQLite Expert (Pro version). This is a truly excellent SQLite DB manager. But don't forget that it will do its best to "display" the BLOB column the best it can. Depending on the options you've set the program may have to perform extra work to produce the result of your query. You may want to test various settings likely to affect blob display (Show images, Thumbs tracking, Cell auto height [?]) to speed up display somehow. It may be that the column order has some impact on the speed of the grid component used for display. Of course Bogdan [SQLite Expert author] is in the best place to comment. Now you should also ascertain the good usage of cache size and either use the CLI or write a simple program (in whatever language) to time the same query and result fetch but without any display. Time several runs to have the caches filled. That will be fairly close to the time SQLite actually uses for processing the statement. Anything significantly departing from that reference time is in the realm of Expert and probably its grid component. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] slow select from table with data blob
On 30 Jan 2011, at 2:16am, Jay A. Kreibich wrote: > You'll see the slow down anytime you access anything "past" the BLOB. > To avoid that, put the BLOBs at the end of the rows and avoid "SELECT *" > style queries. Avoiding 'SELECT *' unless you actually want * is good advice in any case. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] slow select from table with data blob
On Sat, Jan 29, 2011 at 03:07:47PM -0800, Rael Bauer scratched on the wall: > It seemed strange that a simple "select * from table" that I was > doing was so slow. The table contained about 20 columns (fields) > and 300 rows. The select took about 1.5 seconds. (using SQLite Expert). > So my questions: > Is this standard behaviour for sql databases? (that I have only > found out now). No, it is specific to the way SQLite stores data on the disk. If you're interested in the specifics, you can read about the SQLite file format and the on-disk encoding and storing of rows. > Is there actually some way to bypass this "problem" (e.g. database setting..)? You'll see the slow down anytime you access anything "past" the BLOB. To avoid that, put the BLOBs at the end of the rows and avoid "SELECT *" style queries. You can also just put them in a different table. > Is it generally advisable to separate out blob fields into their own > table to ensure fast select speeds? Yes. If the BLOB column is not frequently accessed, and is "auxiliary" data to the rest of the row, it is a common practice to break them off into their own "detail" table (i.e. a one-to-one table). -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable." -- Angela Johnson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] slow select from table with data blob
On Saturday, January 29, 2011 at 5:54 PM, Rael Bauer wrote: > The table contains an id field with a unique index and another field with a > non-unique index. > > > > So you got about 40 Meg of data in 1.5 seconds. Use your OS's copy command > > (or some graphical equivalent) to duplicate that file. How long does it > > take ? > > > > > > You seemed to miss what I was saying: > If the blob field is positioned in the middle of the columns then even If I > don't include the blob field in the query the select is very slow (1.5 > seconds). > If the blob field is positioned at the end of the columns then if I don't > include the blob field in the query the select is very fast (140 ms). If I do > include the blob field, the select is about 400-500 ms. > > > > > That is a known feature (issue). I am sure it is written up somewhere, but definitely, on this list, Richard Hipp and others have emphasized this many times -- search the mailing list archives. Best design -- keep the blob in a separate, dedicated table, and join to that table only when the blob is to be retrieved. That way, sqlite doesn't have to plow through useless pages to find stuff that would fit in a single page but doesn't because of the intervening blob. -- Puneet Kishor Sent with Sparrow ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] slow select from table with data blob
The table contains an id field with a unique index and another field with a non-unique index. >So you got about 40 Meg of data in 1.5 seconds. Use your OS's copy command >(or some graphical equivalent) to duplicate that file. How long does it take >? You seemed to miss what I was saying: If the blob field is positioned in the middle of the columns then even If I don't include the blob field in the query the select is very slow (1.5 seconds). If the blob field is positioned at the end of the columns then if I don't include the blob field in the query the select is very fast (140 ms). If I do include the blob field, the select is about 400-500 ms. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] slow select from table with data blob
On 29 Jan 2011, at 11:07pm, Rael Bauer wrote: > It seemed strange that a simple "select * from table" that I was doing was so > slow. The table contained about 20 columns (fields) and 300 rows. The select > took about 1.5 seconds. (using SQLite Expert). Do you have an indexes or UNIQUE restrictions on that table ? I'm not just talking about the BLOB field, although an index or UNIQUE on that would be particularly interesting. > The table contained a blob field, with a "fair" amount of data spread over > the rows (max was around 6 MB...). The sqlite file was about 40 MB. So you got about 40 Meg of data in 1.5 seconds. Use your OS's copy command (or some graphical equivalent) to duplicate that file. How long does it take ? Please time this carefully the first time you do it because after that the file will be cached and no reading of the file will actually take place. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] slow select from table with data blob
It seemed strange that a simple "select * from table" that I was doing was so slow. The table contained about 20 columns (fields) and 300 rows. The select took about 1.5 seconds. (using SQLite Expert). The table contained a blob field, with a "fair" amount of data spread over the rows (max was around 6 MB...). The sqlite file was about 40 MB. After some testing, I discovered that the problem was caused by the blob field being in the middle of the columns (e.g. column 8). Results were slow even if I didn't include the blob field in the select. If I moved this blob field to the end of the table (i.e. last column), then select was very fast if I didn't include the last field (140ms). If I did include the last field (i.e. the blob field), it was slower (400ms) but still significantly faster that results above, when blob field was in the middle of the table. So my questions: Is this standard behaviour for sql databases? (that I have only found out now). Is there actually some way to bypass this "problem" (e.g. database setting..)? Is it generally advisable to separate out blob fields into their own table to ensure fast select speeds? Thanks Rael Bauer ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
