Re: [sqlite] Internal v. External BLOBs

2011-10-01 Thread Petite Abeille
On Oct 1, 2011, at 2:09 AM, Simon Slavin wrote: >> Thanks for sharing. What about insert time (i.e. insert blob vs. write file)? > > This would be far more dependent on your combination of operating system and > file system. Reading a file is pretty-much the same on everything. Creating > a

Re: [sqlite] Internal v. External BLOBs

2011-09-30 Thread Simon Slavin
On 1 Oct 2011, at 12:37am, Petite Abeille wrote: > On Sep 21, 2011, at 2:19 AM, Richard Hipp wrote: > >> If you are storing large BLOBs in SQLite, can you read them faster if they >> are stored directly in the database file, or can you get to them quicker if >> you store just a filename in the

Re: [sqlite] Internal v. External BLOBs

2011-09-30 Thread Petite Abeille
On Sep 21, 2011, at 2:19 AM, Richard Hipp wrote: > If you are storing large BLOBs in SQLite, can you read them faster if they > are stored directly in the database file, or can you get to them quicker if > you store just a filename in the database and read the BLOB content from a > separate

Re: [sqlite] Internal v. External BLOBs

2011-09-21 Thread Teg
My experience is that having larger blobs impacts performance too. It seems like SQlite struggles to seek over the blobs when traversing non-blob containing tables. I haven't characterized it beyond that. It might even be disk caching. I tend to keep my blobs in a different DB file than the

Re: [sqlite] Internal v. External BLOBs

2011-09-21 Thread Alexey Pechnikov
There is a problem with access to file in directory with big amount of files. FS directory indicies are not really good. I did test 100 millions of 1k files in SQLite and results were better than reading from set of directories in FS. But for files about 1 Mb and more the SQLIte performance is not

Re: [sqlite] Internal v. External BLOBs

2011-09-21 Thread Filip Navara
> We did some experiments to try to answer this question, and the results > seemed interesting enough to share with the community at large. Are the test cases available somewhere? I'd be interested in running them on Windows setup with/out SSD. Thanks, Filip Navara

Re: [sqlite] Internal v. External BLOBs

2011-09-20 Thread Simon Slavin
On 21 Sep 2011, at 1:19am, Richard Hipp wrote: > On Linux workstations, it is faster to store BLOBs in the database if > they are less than about 100KB in size, and faster to store them in a > separate file if they are larger than about 100KB. Looking at the '10k' column in your graph, do you

[sqlite] Internal v. External BLOBs

2011-09-20 Thread Richard Hipp
If you are storing large BLOBs in SQLite, can you read them faster if they are stored directly in the database file, or can you get to them quicker if you store just a filename in the database and read the BLOB content from a separate file? We did some experiments to try to answer this question,