Re: [sqlite] BLOB Support

[Sam Carleton]

On Wed, Jun 16, 2010 at 10:04 AM, Teg  wrote:
> Hello Sam,
>
> I store multiple gigs of image files, some as large as 2-3 megs in
> Sqlite DB's. For pretty much the same reason, the convenience of
> having them in one package. For my requirements, extracting the images
> from the DB, and displaying them isn't a bottleneck. It's fast enough.
>
> Search speed was improved by not storing the meta-data in the same
> table as the blob data. If I wanted to improve performance even more, I'd
> keep the meta-data in a different DB file. Essentially a DB for blobs
> and a DB for meta-data. It seems that seeking over the blobs to get to
> the meta-data in the combined DB is somewhat slow.

Teg,

Thank you for the info!  I had always intended to store the metadata
in a separate DB then the blobs.  There will always only be one
metatdata DB, the only question is whether there is one big blob DB or
multiple blob DB's which are broken into logical groupings.  The
system already has the concept of groups the images, so conceptually
the later is very logical.

Of course the advantage to one big blob DB is not having to figure out
which DB to open to get the images.  A few customers will load upwards
of 100,000 to 150,000 images in one events.  How well does SQLite
handle large blob DB's?  There are two images that I want to store, a
thumbnail (4K~8K) and a large image (80K~160K).  That would result in
one DB between 9Gig and 25Gig.

Now that I do the math, breaking them into logical groups that
normally don't have more then 2000 images and normally only a few
hundred, seems like a much more practical approach;)

Sam
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] BLOB Support

[Teg]

Hello Sam,

I store multiple gigs of image files, some as large as 2-3 megs in
Sqlite DB's. For pretty much the same reason, the convenience of
having them in one package. For my requirements, extracting the images
from the DB, and displaying them isn't a bottleneck. It's fast enough.

Search speed was improved by not storing the meta-data in the same
table as the blob data. If I wanted to improve performance even more, I'd
keep the meta-data in a different DB file. Essentially a DB for blobs
and a DB for meta-data. It seems that seeking over the blobs to get to
the meta-data in the combined DB is somewhat slow.

C

SC> A lot also has to do with the requirements: My software is an event
SC> image viewing system, where each event is seen as a single "document"
SC> and all the data associated with the "document" is contained within
SC> the "event" folder.  Currently only the metadata is stored in the
SC> database, all the images are stored in folders that are within the
SC> "event" folder.  I am guessing, as is others, that storing the large
SC> images in the SQLite DB would be less efficient then how I am storing
SC> it now.  One side effect, though is the requirement to backup the
SC> "event" folder.  It takes a LOT longer to copy 5000 4k~8k files then
SC> it would be to copy one 20M ~ 40M database file.  Because of this, in
SC> time I want to move all the thumbnails into one SQLite file, or maybe
SC> have one SQLite file per current folder holding images.

SC> Sam
SC> ___
SC> sqlite-users mailing list
SC> sqlite-users@sqlite.org
SC> 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] BLOB Support

[Sam Carleton]

On Wed, Jun 16, 2010 at 8:34 AM, P Kishor  wrote:
> On Wed, Jun 16, 2010 at 7:23 AM, Andreas Henningsson
>  wrote:
>> Do some testing to find out if it suits the application you develop.
>> But just in general .. file systems are build to handle files, databases are
>> for handle data.
>>
>
> Well, at the risk of being pedantic, you say files I say data (sung to
> the tune of "you say poe-tay-toe, I say poe-tah-toe").

I am in total agreement ;)

> But yes, my sense is (no firm, scientific tests backing this claim,
> mind you), that storing very large binary objects in a db doesn't seem
> efficient. Storing them on the file system while storing their
> metadata in the db seems a lot more efficient. On the other hand, a
> case could be made for storing blobs in the db when you have many,
> many small binary objects, as in the case of image thumbnails.
> Especially if the blobs are smaller than a page size, the db would
> likely be extremely efficient.

A lot also has to do with the requirements: My software is an event
image viewing system, where each event is seen as a single "document"
and all the data associated with the "document" is contained within
the "event" folder.  Currently only the metadata is stored in the
database, all the images are stored in folders that are within the
"event" folder.  I am guessing, as is others, that storing the large
images in the SQLite DB would be less efficient then how I am storing
it now.  One side effect, though is the requirement to backup the
"event" folder.  It takes a LOT longer to copy 5000 4k~8k files then
it would be to copy one 20M ~ 40M database file.  Because of this, in
time I want to move all the thumbnails into one SQLite file, or maybe
have one SQLite file per current folder holding images.

Sam
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] BLOB Support

[P Kishor]

On Wed, Jun 16, 2010 at 7:23 AM, Andreas Henningsson
 wrote:
> Do some testing to find out if it suits the application you develop.
> But just in general .. file systems are build to handle files, databases are
> for handle data.
>

Well, at the risk of being pedantic, you say files I say data (sung to
the tune of "you say poe-tay-toe, I say poe-tah-toe"). What is within
the files is data after all. When storing a blob of, say, an image,
you are not storing the file; instead, you are storing those
bits-and-bytes that are reconstructed as an image by your image
viewer. To do so, you open your image file, you read in the contents,
and write them to the db blob column. Your file is gone... it is no
longer relevant. Now what you have is the data in your db.

But yes, my sense is (no firm, scientific tests backing this claim,
mind you), that storing very large binary objects in a db doesn't seem
efficient. Storing them on the file system while storing their
metadata in the db seems a lot more efficient. On the other hand, a
case could be made for storing blobs in the db when you have many,
many small binary objects, as in the case of image thumbnails.
Especially if the blobs are smaller than a page size, the db would
likely be extremely efficient.


> I don't think BLOB in SQlite will increasing the performance compared to
> store the files in
> the file system.
>
> Some SQlite APIs do not support BLOB very good. Also something to
> consider if you will store large files.
>
> /Andreas
>
> On Wed, Jun 16, 2010 at 1:31 PM, P Kishor  wrote:
>
>> for some reason, I remember you asking the same question not too long
>> ago, and getting a bunch of answers. I recall chipping in with an
>> answer myself. DIdn't any of those answers help?
>>
>> On Wed, Jun 16, 2010 at 1:58 AM, Navaneeth Sen B
>>  wrote:
>> > Hi All,
>> > I would like to know more about this BLOB support in SQLite. Some of my
>> > queries are:
>> >
>> >   1. One of my colleague suggested that using BLOB support for storing
>> >      images in the DB is a good idea, whereas storing AVCHD data(huge
>> >      size) as blobs is not a good idea. I need a bit more clarification
>> >      on this statement.
>>
>> What is the clarification you need? I remember writing that (in my
>> wisdom), it is better to store large binary objects such as big video,
>> audio or image files in the file system, and store the metadata for
>> them in the db. If you have many, many small items, storing them
>> directly as blobs in the sqlite should be very quick and helpful, but
>> other than that, storing them in the file system may be better. Did
>> you experiment with one or the other?
>>
>> >   2. I just want to know how does this BLOB support help in increasing
>> >      the performance?
>>
>> Don't know. Only you can tell, based on your usage scenario and your
>> performance expectations.
>>
>> >   3. What is the difference produced in storing the file inside DB(not
>> >      in blob format) and storing the same file in BLOB format in the DB?
>> >
>> >
>>
>> What do you mean by "What is the difference produced"? Which
>> difference and produced from what? If stored correctly, you will have
>> the same item in the db as would have been in the file system, so
>> which difference are you talking about? If you are talking about the
>> mechanism itself, well, we went through that earlier and above as
>> well... the db does all the homework for you regarding where to store
>> the files, even what to call them, if you implement that, etc. But, of
>> course, you can't access those files directly if they are in the db.
>> You have to get to them via the db only.
>>
>>
>> > --
>> >
>> > *
>> > Thanks & Regards
>> > SEN*
>> > /
>> > /
>> >
>> >





-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] BLOB Support

[Andreas Henningsson]

Do some testing to find out if it suits the application you develop.
But just in general .. file systems are build to handle files, databases are
for handle data.

I don't think BLOB in SQlite will increasing the performance compared to
store the files in
the file system.

Some SQlite APIs do not support BLOB very good. Also something to
consider if you will store large files.

/Andreas

On Wed, Jun 16, 2010 at 1:31 PM, P Kishor  wrote:

> for some reason, I remember you asking the same question not too long
> ago, and getting a bunch of answers. I recall chipping in with an
> answer myself. DIdn't any of those answers help?
>
> On Wed, Jun 16, 2010 at 1:58 AM, Navaneeth Sen B
>  wrote:
> > Hi All,
> > I would like to know more about this BLOB support in SQLite. Some of my
> > queries are:
> >
> >   1. One of my colleague suggested that using BLOB support for storing
> >  images in the DB is a good idea, whereas storing AVCHD data(huge
> >  size) as blobs is not a good idea. I need a bit more clarification
> >  on this statement.
>
> What is the clarification you need? I remember writing that (in my
> wisdom), it is better to store large binary objects such as big video,
> audio or image files in the file system, and store the metadata for
> them in the db. If you have many, many small items, storing them
> directly as blobs in the sqlite should be very quick and helpful, but
> other than that, storing them in the file system may be better. Did
> you experiment with one or the other?
>
> >   2. I just want to know how does this BLOB support help in increasing
> >  the performance?
>
> Don't know. Only you can tell, based on your usage scenario and your
> performance expectations.
>
> >   3. What is the difference produced in storing the file inside DB(not
> >  in blob format) and storing the same file in BLOB format in the DB?
> >
> >
>
> What do you mean by "What is the difference produced"? Which
> difference and produced from what? If stored correctly, you will have
> the same item in the db as would have been in the file system, so
> which difference are you talking about? If you are talking about the
> mechanism itself, well, we went through that earlier and above as
> well... the db does all the homework for you regarding where to store
> the files, even what to call them, if you implement that, etc. But, of
> course, you can't access those files directly if they are in the db.
> You have to get to them via the db only.
>
>
> > --
> >
> > *
> > Thanks & Regards
> > SEN*
> > /
> > /
> >
> >
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
>
> --
> Puneet Kishor http://www.punkish.org
> Carbon Model http://carbonmodel.org
> Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
> Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
> Nelson Institute, UW-Madison http://www.nelson.wisc.edu
> ---
> Assertions are politics; backing up assertions with evidence is science
> ===
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Andreas Henningsson

"Vanligt sunt förnuft är inte särkilt vanligt." -- Voltaire
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] BLOB Support

[P Kishor]

for some reason, I remember you asking the same question not too long
ago, and getting a bunch of answers. I recall chipping in with an
answer myself. DIdn't any of those answers help?

On Wed, Jun 16, 2010 at 1:58 AM, Navaneeth Sen B
 wrote:
> Hi All,
> I would like to know more about this BLOB support in SQLite. Some of my
> queries are:
>
>   1. One of my colleague suggested that using BLOB support for storing
>      images in the DB is a good idea, whereas storing AVCHD data(huge
>      size) as blobs is not a good idea. I need a bit more clarification
>      on this statement.

What is the clarification you need? I remember writing that (in my
wisdom), it is better to store large binary objects such as big video,
audio or image files in the file system, and store the metadata for
them in the db. If you have many, many small items, storing them
directly as blobs in the sqlite should be very quick and helpful, but
other than that, storing them in the file system may be better. Did
you experiment with one or the other?

>   2. I just want to know how does this BLOB support help in increasing
>      the performance?

Don't know. Only you can tell, based on your usage scenario and your
performance expectations.

>   3. What is the difference produced in storing the file inside DB(not
>      in blob format) and storing the same file in BLOB format in the DB?
>
>

What do you mean by "What is the difference produced"? Which
difference and produced from what? If stored correctly, you will have
the same item in the db as would have been in the file system, so
which difference are you talking about? If you are talking about the
mechanism itself, well, we went through that earlier and above as
well... the db does all the homework for you regarding where to store
the files, even what to call them, if you implement that, etc. But, of
course, you can't access those files directly if they are in the db.
You have to get to them via the db only.


> --
>
> *
> Thanks & Regards
> SEN*
> /
> /
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] BLOB Support

[Simon Slavin]

On 16 Jun 2010, at 8:14am, Navaneeth Sen B wrote:

> I am using SQLite.
> 
> Thanks
> Sen
> 
> **
> 
> On 6/16/2010 12:40 PM, Simon Slavin wrote:
>> On 16 Jun 2010, at 7:58am, Navaneeth Sen B wrote:
>> 
>>>   3. What is the difference produced in storing the file inside DB(not
>>>  in blob format) and storing the same file in BLOB format in the DB?
>> 
>> 
>> What tool are you expecting to use to store the file inside the DB ?

SQLite does not store files inside its database.  Therefore the first part of 
your question never happens.  Your question doesn't mean anything.

Simon.

PS: when quoting someone else's post, please trim it to just the piece you need 
for your own text, and add your own text below the old text.  English is read 
from top to bottom.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] BLOB Support

[Navaneeth Sen B]

Hi Simon,
I am using SQLite.

Thanks
Sen

**

On 6/16/2010 12:40 PM, Simon Slavin wrote:
> On 16 Jun 2010, at 7:58am, Navaneeth Sen B wrote:
>
>
>>3. What is the difference produced in storing the file inside DB(not
>>   in blob format) and storing the same file in BLOB format in the DB?
>>  
> What tool are you expecting to use to store the file inside the DB ?
>
> Simon.
> ___
> 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] BLOB Support

[Simon Slavin]

On 16 Jun 2010, at 7:58am, Navaneeth Sen B wrote:

>   3. What is the difference produced in storing the file inside DB(not
>  in blob format) and storing the same file in BLOB format in the DB?

What tool are you expecting to use to store the file inside the DB ?

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] BLOB support and SQLite 3.0

[Mrs. Brisby]

On Mon, 2003-12-15 at 09:37, D. Richard Hipp wrote:
>(2) The ".dump" command will only show the binary data through
>the first \000 character.  If the binary data contains no
>\000 characters, the ".dump" command might segfault.

Add an SQL function like TOCHAR or something like that. Then make dump's
output look like this:

INSERT INTO foo ('Hello'||TOCHAR(32)||'World!');




-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]