Re: [sqlite] SQLite issues - iPad development

[BareFeetWare]

On 22/09/2011, at 4:30 AM, ecky wrote:

> I have a SQLITE database I created using the firefox plugin (SQLITE manager). 
>   I'm using that database in my IPAD app and I can read and write to it.

What error are you getting?

> However... I'm trying to debug some issue with my app so I copy the database 
> file off my IPAD back to my MAC and try to read the database in the firefox 
> plugin.   I get no errors but the database has no tables in it :-(
> 
> Maybe compatibility issue between SQLITE IOS and Firefox maybe?

For what it's worth, I copy SQLite database files back and forth all the time, 
between my Mac, iPad, iPhone and DropBox, no problem.

Here's an (old) example of an SQLite database running fine on my iPad app, 
showing data:
http://www.barefeetware.com/databare/data_choices.html

Tom

Tom Brodhurst-Hill
BareFeetWare

--
iPhone/iPad/iPod and Mac software development, specialising in databases
develo...@barefeetware.com
 --
Comparison of SQLite GUI tools:
http://www.barefeetware.com/sqlite/compare/?ml

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

Re: [sqlite] Could someone explain why this query is so slow

[Paul Sanderson]

Ahh

I was sure that this was being created :(
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] c-api document suggestion

[Sean Pieper]

a. This is what confused me-- the documentation for the prepare statement 
mentions this perf boost being quite explicit about including the null, then 
also makes the ambiguous statement that "the zSQL string ends at either the 
first '\000' or '\u' character or the nbyteth byte, whichever comes first." 
Since the null gets ignored, I interpreted this as meaning that the nth byte 
was ignored, and that providing the proper data size was the source of the 
performance boost (possibly by eliminating a search for null termination). 

Thank you for the clarification :-).


-sean

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Richard Hipp
Sent: Wednesday, September 21, 2011 12:23 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] c-api document suggestion

On Wed, Sep 21, 2011 at 3:05 PM, Sean Pieper  wrote:

> There's an apparent inconsistency in the behavior of  sqlite3_bind_text and
> sqlite3_prepare_v2.
> If the user supplies the length of the argument rather than using -1,
> bind_text expects that this length exclude the null termination, whereas
> prepare apparently expects it to include the null termination.
>

No, sqlite3_prepare_v2() does not *expect* N to be size+1.
sqlite3_prepare_v2() with length N will process all text up to the first
zero-character, or the first N bytes, whichever come first.  So you are
perfectly free to set N equal to the length of the string exclusive of the
zero-terminator, just as in bind_text().

However, if you are willing to make N equal to the length of the string plus
one, then SQLite can avoid a memcpy() and thus give a very small performance
advantage.  But that is purely an optimization, and a minor one at that.  It
will work correctly regardless of whether N is strlen() or strlen()+1.

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
---
This email message is for the sole use of the intended recipient(s) and may 
contain
confidential information.  Any unauthorized review, use, disclosure or 
distribution
is prohibited.  If you are not the intended recipient, please contact the 
sender by
reply email and destroy all copies of the original message.
---
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] MC/DC coverage explained wrong in the home page?

[Sami Liedes]

Hi!

Looking at

   http://www.sqlite.org/testing.html

it seems to me that MC/DC coverage is explained wrong there. This in
turn makes me wonder if SQLite tests really have 100% MC/DC coverage
or if this claim is just based on mistaken understanding of MC/DC.

The page explains:


Wikipedia defines MC/DC as follows:

* Each decision tries every possible outcome.
* Each condition in a decision takes on every possible outcome.
* Each entry and exit point is invoked.
* Each condition in a decision is shown to independently affect the
  outcome of the decision.

In the C programming language where && and || are "short-circuit"
operators, MC/DC and branch coverage are very nearly the same thing.
The primary difference is in boolean vector tests. One can test for
any of several bits in bit-vector and still obtain 100% branch test
coverage even though the second element of MC/DC - the requirement
that each condition in a decision take on every possible outcome -
might not be satisfied.

SQLite uses testcase() macros as described in the previous subsection
to make sure that every condition in a bit-vector decision takes on
every possible outcome. In this way, SQLite also achieves 100% MC/DC
in addition to 100% branch coverage.


I don't think the operative, clever thing in MC/DC is the second
requirement, but the fourth one, and in it the important word is
"independently". Contrary to what seems to be claimed in the last
paragraph, "mak[ing] sure that every condition in a bit-vector
decision takes on every possible outcome" (the second requirement)
expressly is *not* sufficient for MC/DC. In addition to that, you need
to show that "each condition in a decision [...] independently
affect[s] the outcome of the decision" (the fourth requirement).

That is, if you have arbitrary boolean conditions A, B and C (not
necessarily independent of each other), and you have a branch like

  if (A op B op C)

the second condition means that you have to give test cases for the
positive and negative of each A, B and C, and this is what seems to be
explained by the page. That is, if you could provide only two test
cases, one with A && B && C and the other with !A && !B && !C, that
would satisfy the second condition.

But the fourth condition requires something more: That each decision
is shown to *independently* affect the outcome of the decision. That
is, for each condition of A, B and C, you have to give two test cases
where the difference in the test case both flips that *and only that*
condition of these three while at the same time changing the branch
taken.

This is a kind of dead code test; you prove that none of the
conditions (A, B or C) are superfluous, in that each of them
*independently* can affect the outcome of the branch operation. So
given the branch condition of (A op B op C), you need to give at least
six test cases to satisfy the fourth condition:

1. A
2. !A && (B and C take the same value as in (1)) && (the branch taken
   differs from (1))
3. B
4. !B && (A and C take the same value as in (3)) && (the branch taken
   differs from (3))
5. C
6. !C && (A and B take the same value as in (5)) && (the branch taken
   differs from (5))

So, is SQLite really tested up to this standard? If so, perhaps the
description of MC/DC in the above page should be improved; I think
it's really quite misleading now.

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

Re: [sqlite] sqlite3_analyzer

[Keith Christian]

Richard, thank you for the details of the compilation instructions.
I'll give that a try.

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

Re: [sqlite] c-api document suggestion

[Richard Hipp]

On Wed, Sep 21, 2011 at 3:05 PM, Sean Pieper  wrote:

> There's an apparent inconsistency in the behavior of  sqlite3_bind_text and
> sqlite3_prepare_v2.
> If the user supplies the length of the argument rather than using -1,
> bind_text expects that this length exclude the null termination, whereas
> prepare apparently expects it to include the null termination.
>

No, sqlite3_prepare_v2() does not *expect* N to be size+1.
sqlite3_prepare_v2() with length N will process all text up to the first
zero-character, or the first N bytes, whichever come first.  So you are
perfectly free to set N equal to the length of the string exclusive of the
zero-terminator, just as in bind_text().

However, if you are willing to make N equal to the length of the string plus
one, then SQLite can avoid a memcpy() and thus give a very small performance
advantage.  But that is purely an optimization, and a minor one at that.  It
will work correctly regardless of whether N is strlen() or strlen()+1.

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] c-api document suggestion

[Pavel Ivanov]

> If the user supplies the length of the argument rather than using -1, 
> bind_text expects that this length exclude the null termination, whereas 
> prepare apparently expects it to include the null termination.
Can I challenge you in that this conclusion is wrong? Everywhere in
the development world length of string always means "null termination
is not included" because explicitly providing length means there can
be no null termination at all.

Please show examples of code that led you to your conclusion and show
how it fails without null termination.


> leaving the reader to look through the description of how auto-detection of 
> length works in each case.

You are a little inconsistent here. Auto-detection of length doesn't
work when you explicitly provide string length, so you don't have to
look through it.


Pavel


On Wed, Sep 21, 2011 at 3:05 PM, Sean Pieper  wrote:
> There's an apparent inconsistency in the behavior of  sqlite3_bind_text and 
> sqlite3_prepare_v2.
> If the user supplies the length of the argument rather than using -1, 
> bind_text expects that this length exclude the null termination, whereas 
> prepare apparently expects it to include the null termination.
>
> Since this is kind of surprising, it would be helpful if the documentation 
> for each function directly stated the desired behavior for explicit 
> declaration of length, rather than leaving the reader to look through the 
> description of how auto-detection of length works in each case.
>
> -sean
>
> ---
> This email message is for the sole use of the intended recipient(s) and may 
> contain
> confidential information.  Any unauthorized review, use, disclosure or 
> distribution
> is prohibited.  If you are not the intended recipient, please contact the 
> sender by
> reply email and destroy all copies of the original message.
> ---
> ___
> 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] c-api document suggestion

[Igor Tandetnik]

On 9/21/2011 3:05 PM, Sean Pieper wrote:

There's an apparent inconsistency in the behavior of  sqlite3_bind_text and 
sqlite3_prepare_v2.
If the user supplies the length of the argument rather than using -1,
bind_text expects that this length exclude the null termination,


You can include the NUL terminator, if you want it to actually be stored 
in the database.



whereas prepare apparently expects it to include the null
termination.


What makes you believe that? As far as I know, it should still work if 
you don't include it.

--
Igor Tandetnik

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

Re: [sqlite] SQLITE issues - IPAD development

[Dan Kennedy]

On 09/22/2011 01:30 AM, ecky wrote:


Howdy

I have a SQLITE database I created using the firefox plugin (SQLITE
manager).   I'm using that database in my IPAD app and I can read and write
to it.

However... I'm trying to debug some issue with my app so I copy the database
file off my IPAD back to my MAC and try to read the database in the firefox
plugin.   I get no errors but the database has no tables in it :-(


This often means that, for whatever reason, the user is not
opening the database file that they think they are opening.




Maybe compatibility issue between SQLITE IOS and Firefox maybe?
Thanks in advance


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

[sqlite] c-api document suggestion

[Sean Pieper]

There's an apparent inconsistency in the behavior of  sqlite3_bind_text and 
sqlite3_prepare_v2.
If the user supplies the length of the argument rather than using -1, bind_text 
expects that this length exclude the null termination, whereas prepare 
apparently expects it to include the null termination.

Since this is kind of surprising, it would be helpful if the documentation for 
each function directly stated the desired behavior for explicit declaration of 
length, rather than leaving the reader to look through the description of how 
auto-detection of length works in each case.

-sean

---
This email message is for the sole use of the intended recipient(s) and may 
contain
confidential information.  Any unauthorized review, use, disclosure or 
distribution
is prohibited.  If you are not the intended recipient, please contact the 
sender by
reply email and destroy all copies of the original message.
---
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLITE issues - IPAD development

[Simon Slavin]

On 21 Sep 2011, at 7:30pm, ecky wrote:

> I have a SQLITE database I created using the firefox plugin (SQLITE
> manager).   I'm using that database in my IPAD app and I can read and write
> to it.
> 
> However... I'm trying to debug some issue with my app so I copy the database
> file off my IPAD back to my MAC and try to read the database in the firefox
> plugin.   I get no errors but the database has no tables in it :-(

Instead of the FireFox plugin on your Mac, use the sqlite3 command-line tool.  
It should already be installed in /usr/bin .  Does that tool show any data ?

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

Re: [sqlite] Question about FTS

[Simon Slavin]

On 21 Sep 2011, at 6:32pm, Sebastian Bermudez wrote:

> How can i know if my shared hosting PHP (sqlite 2 v2.8.17 "2.0-dev $Id: 
> sqlite.c 298697 2010-04-28 12:10:10Z iliaa $" ) or my PDO (sqlite3 library 
> v.3.3.7 "(bundled) 1.0.1 $Id: pdo_sqlite.c 293036 2010-01-03 09:23:27Z") 
> support FTS ???

Try the query

PRAGMA compile_options;

which returns a table like a SELECT query does.

Note: I recommend you use PHP's 'sqlite3' object-oriented interface to SQLite, 
not the PDO interface.

http://www.php.net/manual/en/class.sqlite3.php

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

[sqlite] SQLITE issues - IPAD development

[ecky]

Howdy

I have a SQLITE database I created using the firefox plugin (SQLITE
manager).   I'm using that database in my IPAD app and I can read and write
to it.

However... I'm trying to debug some issue with my app so I copy the database
file off my IPAD back to my MAC and try to read the database in the firefox
plugin.   I get no errors but the database has no tables in it :-(

Maybe compatibility issue between SQLITE IOS and Firefox maybe?
Thanks in advance
-- 
View this message in context: 
http://old.nabble.com/SQLITE-issues---IPAD-development-tp32503767p32503767.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] sqlite3_analyzer

[Richard Hipp]

The way you build sqlite3_analyzer is to download the source tarball and do

 ./configure
 make sqlite3_analyzer

We are in the middle of changing how sqlite3_analyzer gets built.  You are
welcomed to try to use the new procedures if you want.  But it is currently
experimental and subject to breakage.  Use of the ./configure; make method
above is preferred.

On Wed, Sep 21, 2011 at 1:53 PM, Keith Christian
wrote:

> I found the links mentioned above at the bottom of the Downloads page,
> and went to this site:
>
> http://www2.sqlite.org/cgi/src
>
> I logged in to the SQLite timeline using anonymous and a random
> password as instructed.  Once there, I downloaded
> SQLite-256cdbdc810cae23.tar.gz, 3.8 mb in size.
>
> After extracting SQLite-256cdbdc810cae23.tar.gz, I could find no
> source code for a "sqlite analyzer."
>
> All I found was this reference:
>
> grep -i analyzer SQLite-256cdbdc810cae23/Make*
> SQLite-256cdbdc810cae23/Makefile.in:sqlite3_analyzer.c: sqlite3.c
> $(TOP)/src/test_stat.c $(TOP)/src/tclsqlite.c
> $(TOP)/tool/spaceanal.tcl
>
> Should the referenced "sqlite3_analyzer.c" file exist in the .tar.gz
> file?  Or is it not included in the source?  Should I look elsewhere
> for it?
>
> Thanks.
>
> ==Keith
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3_analyzer

[Keith Christian]

I found the links mentioned above at the bottom of the Downloads page,
and went to this site:

http://www2.sqlite.org/cgi/src

I logged in to the SQLite timeline using anonymous and a random
password as instructed.  Once there, I downloaded
SQLite-256cdbdc810cae23.tar.gz, 3.8 mb in size.

After extracting SQLite-256cdbdc810cae23.tar.gz, I could find no
source code for a "sqlite analyzer."

All I found was this reference:

grep -i analyzer SQLite-256cdbdc810cae23/Make*
SQLite-256cdbdc810cae23/Makefile.in:sqlite3_analyzer.c: sqlite3.c
$(TOP)/src/test_stat.c $(TOP)/src/tclsqlite.c
$(TOP)/tool/spaceanal.tcl

Should the referenced "sqlite3_analyzer.c" file exist in the .tar.gz
file?  Or is it not included in the source?  Should I look elsewhere
for it?

Thanks.

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

[sqlite] Question about FTS

[Sebastian Bermudez]

How can i know if my shared hosting PHP (sqlite 2 v2.8.17 "2.0-dev $Id: 
sqlite.c 298697 2010-04-28 12:10:10Z iliaa $" ) or my PDO (sqlite3 library 
v.3.3.7 "(bundled) 1.0.1 $Id: pdo_sqlite.c 293036 2010-01-03 09:23:27Z") 
support FTS ???

There are some chance to get FTS running without recompile sqlite ? (i have 
sqlite with php on shared hosting).

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

Re: [sqlite] Could someone explain why this query is so slow

[Igor Tandetnik]

On 9/21/2011 12:19 PM, Paul Sanderson wrote:

Thanks Igor

That makes sense but if I drop MD5 from the query (the vast majority
of MD5 values would be null anyway) and use
select ID FROM rtable WHERE search>  0 and isf = 0 ORDER BY afo
The result from explain query plan is
0|0|0 SCAN TABLE rtable (~3 rows)

0|0|0 USE TEMP B-TREE FOR ORDER BY


Which seems to indicate that the b-tree is still being created (I'll
test shortly, but running another long test at the moment)


Double-check that you indeed have an index on afo. Show the output of 
this statement:


select * from sqlite_master where tbl_name='rtable';

--
Igor Tandetnik

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

Re: [sqlite] Could someone explain why this query is so slow

[Paul Sanderson]

Thanks Igor

That makes sense but if I drop MD5 from the query (the vast majority
of MD5 values would be null anyway) and use
select ID FROM rtable WHERE search > 0 and isf = 0 ORDER BY afo
The result from explain query plan is
0|0|0 SCAN TABLE rtable (~3 rows)
>> 0|0|0 USE TEMP B-TREE FOR ORDER BY

Which seems to indicate that the b-tree is still being created (I'll
test shortly, but running another long test at the moment)



On 21 September 2011 14:33, Igor Tandetnik  wrote:
> Paul Sanderson  wrote:
>> select ID FROM rtable WHERE search > 0 and MD5 is NULL and isf = 0 ORDER BY 
>> afo
>>
>> explain query plan gives the following for the initial query
>> 0|0|0 SEARCH TABLE rtable USING INDEX md5_a (md5=?) (~2 rows)
>> 0|0|0 USE TEMP B-TREE FOR ORDER BY
>>
>> it seems that the extra time is taken creating a b-tree for the order
>> by but if correct why is the existing index not used?
>
> Only one index per table can be used. Imagine you have two lists of (the 
> same) people, one sorted by first name and one by last name. You need to list 
> all Peters alphabetically by their last name. You can either use the first 
> list to find all Peters, then sort them by hand. Or you can use the second 
> list to enumerate everyone in the order of last name, and select only Peters. 
> But you can't use both lists.
>
>> is there anyway of speeding this up?
>
> A single index on (md5, afo) may help.
> --
> Igor Tandetnik
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Paul Sanderson
Sanderson Forensics
+44 (0)1326 572786
www.sandersonforensics.com
http://www.twitter.com/sandersonforens
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Could someone explain why this query is so slow

[Igor Tandetnik]

Paul Sanderson  wrote:
> select ID FROM rtable WHERE search > 0 and MD5 is NULL and isf = 0 ORDER BY 
> afo
> 
> explain query plan gives the following for the initial query
> 0|0|0 SEARCH TABLE rtable USING INDEX md5_a (md5=?) (~2 rows)
> 0|0|0 USE TEMP B-TREE FOR ORDER BY
> 
> it seems that the extra time is taken creating a b-tree for the order
> by but if correct why is the existing index not used?

Only one index per table can be used. Imagine you have two lists of (the same) 
people, one sorted by first name and one by last name. You need to list all 
Peters alphabetically by their last name. You can either use the first list to 
find all Peters, then sort them by hand. Or you can use the second list to 
enumerate everyone in the order of last name, and select only Peters. But you 
can't use both lists.

> is there anyway of speeding this up?

A single index on (md5, afo) may help.
-- 
Igor Tandetnik

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

[sqlite] Could someone explain why this query is so slow

[Paul Sanderson]

The query below takes about 10 mins to run, any idea why this would be?

select ID FROM rtable WHERE search > 0 and MD5 is NULL and isf = 0 ORDER BY afo

The same query without the ORDER BY takes a few seconds.

select ID FROM rtable WHERE search > 0 and MD5 is NULL and isf = 0

There are approximately 800K rows in the table and all columns are indexed

explain query plan gives the following for the initial query
0|0|0 SEARCH TABLE rtable USING INDEX md5_a (md5=?) (~2 rows)
0|0|0 USE TEMP B-TREE FOR ORDER BY

without the ORDER BY I get just
0|0|0 SEARCH TABLE rtable USING INDEX md5_a (md5=?) (~2 rows)

it seems that the extra time is taken creating a b-tree for the order
by but if correct why is the existing index not used?

is there anyway of speeding this up?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Internal v. External BLOBs

[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 tables that describe the blob contents though because of
this.

I have some 30 GB blob containing files. I use these DB's just for
organization of the blobs. Performance wasn't that important.



Wednesday, September 21, 2011, 5:29:16 AM, you wrote:

AP> There is a problem with access to file in directory with big amount of 
files.
AP> FS directory indicies are not really good. I did test 100 millions of 1k 
files
AP> in SQLite and results were better than reading from set of directories in 
FS.
AP> But for files about 1 Mb and more the SQLIte performance is not good.
AP> Is any reason why SQLite big blobs reading may be slowly? The
AP> performance of the BLOBS may limit performance of FTS and
AP> other custom storage/index realizations (Spatialite, etc). And it's more
AP> important I think. Especially when we need FTS index as fast hash index.

AP> 2011/9/21 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, and the results
>> seemed interesting enough to share with the community at large.  Bottom
>> line:  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.  This is on Ubuntu with
>> EXT4 and a fast SATA disk - your mileage may vary with different operating
>> systems, filesystems, and hardware.
>>
>> The complete report is here:
>> http://www.sqlite.org/intern-v-extern-blob.html
>>
>> --
>> D. Richard Hipp
>> d...@sqlite.org
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>






-- 
Best regards,
 Tegmailto:t...@djii.com

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

Re: [sqlite] Internal v. External BLOBs

[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 good.
Is any reason why SQLite big blobs reading may be slowly? The
performance of the BLOBS may limit performance of FTS and
other custom storage/index realizations (Spatialite, etc). And it's more
important I think. Especially when we need FTS index as fast hash index.

2011/9/21 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, and the results
> seemed interesting enough to share with the community at large.  Bottom
> line:  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.  This is on Ubuntu with
> EXT4 and a fast SATA disk - your mileage may vary with different operating
> systems, filesystems, and hardware.
>
> The complete report is here:
> http://www.sqlite.org/intern-v-extern-blob.html
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Internal v. External BLOBs

[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
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users