[sqlite] BLOBs' affect on DB performance

Hi there, I'm wondering how larger BLOBs in a database affect performance of accessing the non-blob data. We've got a database with potentially a few million records in the main tables (of just strings and ints), but joined to that data set we want to store up to thousands (maybe 75000 max)

Re: [sqlite] Is there an optimization for "IS NULL" operator in a SELECT query ?

On Mon, Mar 01, 2010 at 11:44:51PM +0100, Jean-Christophe Deschamps scratched on the wall: > >> So indexes are not used for NOT conditions, as NOT conditions >> generally require a full scan, regardless. Yes, it is a simple >> reverse of a binary test, but the reverse of a specific indexed

Re: [sqlite] Is there an optimization for "IS NULL" operator in a SELECT query ?

>I haven't been able to think of how it would preclude using the index, >but I suspect it's more a matter of needing a similar-but-different >codepath to optimize for the NOT case, rather than a simple "invert >this" codepath relying on the existing case. Which is really just >another way of

[sqlite] FTS & Doc Compression

Hi all, I've been playing around with the FTS3 (via the amalgamation src) on a mobile device and it's working well. But my db file size is getting pretty big and I was looking for a way to compress it. I've seen some earlier posts from Alexey for his compression modifications to the FTS3

Re: [sqlite] Is there an optimization for "IS NULL" operator in a SELECT query ?

On Mon, Mar 1, 2010 at 3:12 PM, Jean-Christophe Deschamps wrote: >>NULL = 12345 is NULL, NOT NULL is NULL, so subset N is not part of NOT >>(col = 12345). > > You're right of course!  (and I was even saying about nulls treated apart) > > But, in your view, that the set can be

Re: [sqlite] Is there an optimization for "IS NULL" operator in a SELECT query ?

>NULL = 12345 is NULL, NOT NULL is NULL, so subset N is not part of NOT >(col = 12345). You're right of course! (and I was even saying about nulls treated apart) But, in your view, that the set can be non-contiguous for negative/negated conditions would it explain that current code can't

Re: [sqlite] Is there an optimization for "IS NULL" operator in a SELECT query ?

On Mon, Mar 1, 2010 at 2:44 PM, Jean-Christophe Deschamps wrote: > The actual reason for the way NOT works as for now may be due to the > fact that negating a condition may cause the resulting set to be in > fact itself the union of two subsets. > Say the "where" condition K is

Re: [sqlite] Is there an optimization for "IS NULL" operator in a SELECT query ?

> So indexes are not used for NOT conditions, as NOT conditions > generally require a full scan, regardless. Yes, it is a simple > reverse of a binary test, but the reverse of a specific indexed > lookup of a known value is a table scan to gather all the unknown > values. Jay, I

Re: [sqlite] dynamic typing misunderstanding

Hello! On Monday 01 March 2010 14:48:46 Dan Kennedy wrote: > > On Mar 1, 2010, at 6:08 PM, Alexey Pechnikov wrote: > > >> It would seem to me that asking [1='1'] *should* return false > >> because the > >> numeric and character string value domains are logically disjoint, > >> so no value >

Re: [sqlite] Fwd: Maybe just a Question

What happens if you change double quote marks to single quote marks? On Mon, 2010-03-01 at 08:52 -0800, Ray Irvine wrote: > > I did not get an e-mail response and the status check URL has expired. > > Thank you > > > > I have found the following: > > > > BEGIN; UPDATE entry SET Spinnaker="No"

Re: [sqlite] Check constraint problem

On Mon, Mar 01, 2010 at 02:06:05PM -0600, Jay A. Kreibich scratched on the wall: > Also, it is highly unlikely the expression "parent == NULL" does what > you want, given that it will *always* return NULL. Try "parent IS > NOT NULL". Er... I mean "parent IS NULL". -j -- Jay A.

Re: [sqlite] Check constraint problem

On Mon, Mar 01, 2010 at 06:47:41PM +0300, ?? ?? scratched on the wall: > Hello! > > I need to check if parent of group is exist or not. > Parent stored in "parent" column and may be NULL or will be existing > "idgroup" column value. > It does not works because subqueries

Re: [sqlite] Check constraint problem

Did you consider using foreign keys (assuming you have the latest version of SQLite)? Pavel On Mon, Mar 1, 2010 at 10:47 AM, Артемий Васюков wrote: > Hello! > > I need to check if parent of group is exist or not. > Parent stored in "parent" column and may be NULL or will be

[sqlite] savepoint error

I have been experiencing a strange issue with savepoint error for the library version 3.6.22. I have the following code: char* sql = "SAVEPOINT sp;"; char* err; slim_int ret = sqlite3_exec(sqlitehandle, sql, NULL, NULL, ); The ret is 1 which is SQLITE_ERROR and err = "near \"sp\": syntax error"

[sqlite] Fwd: Maybe just a Question

> I did not get an e-mail response and the status check URL has expired. Thank > you > > I have found the following: > > BEGIN; UPDATE entry SET Spinnaker="No" ,Singlehand="Doublehand" > ,Doublehand="Third" ,Multi="Multihull" ,Furler=" " WHERE ROWID="45"; COMMIT > > Results in the value of

[sqlite] Check constraint problem

Hello! I need to check if parent of group is exist or not. Parent stored in "parent" column and may be NULL or will be existing "idgroup" column value. It does not works because subqueries prohibited in CHECK constraints. My table showed below (incorrect constraint commented).

Re: [sqlite] Is there an optimization for "IS NULL" operator in a SELECT query ?

On Mon, Mar 01, 2010 at 06:03:49PM +0100, Jean-Christophe Deschamps scratched on the wall: > > >I totally disagree with you. Let's say you have 1,000,000 rows and 100 > >of them contain NULL. In this situation selecting NOT NULL will select > >almost all rows which means that using index in this

Re: [sqlite] Is there an optimization for "IS NULL" operator in a SELECT query ?

>maybe NOT is implemented the same way as any other >function and so it cannot be optimized using index. That's possible, but other logical operators don't exhibit the same bahavior and will not prevent the use of indexes. That NOT is not being handled at the same _logical_ level than AND and

Re: [sqlite] Is there an optimization for "IS NULL" operator in a SELECT query ?

> The point was that NOT is > simply reversing the issue of a binary test, in fine, and that seems > essentially independant of the use of an index for determining . I agree with the point that NOT prevents using an index for some reason. I'm not sure but probably that's because of the same

Re: [sqlite] Minor odd behaviour in sql parsing (maybe a bug?)

>> The following sql is supposedly valid: >> >> select 1 in (1)'hello'; > > Apparently this gets interpreted as > > select (1 in (1)) as "hello"; > >("as" is optional), under SQLite's long-standing policy of > accepting single and double quotes more or less > interchangeably. Hmm, I'd not have

Re: [sqlite] Is there an optimization for "IS NULL" operator in a SELECT query ?

>I totally disagree with you. Let's say you have 1,000,000 rows and 100 >of them contain NULL. In this situation selecting NOT NULL will select >almost all rows which means that using index in this case doesn't give >any performance boost. So here using full scan for NOT NULL condition >is better

Re: [sqlite] Is there an optimization for "IS NULL" operator in a SELECT query ?

> Anyway, it seems the OP has a point in saying that it would be nice > --and I would say 'natural'-- to have the optimizer enhanced to handle > "NOT " as efficiently as it handles ", provided > such enhancement can be done with only little changes. I totally disagree with you. Let's say you have

Re: [sqlite] Is there an optimization for "IS NULL" operator in a SELECT query ?

> > "SELECT count(*) WHERE NOT text IS NULL" > > > > requires that the complete text column is loaded. With a stored LOB > > this results in crazy performance. > >How did you find that? What do you mean by "requires loading of the >whole text column"? It pretty much can require even loading of

Re: [sqlite] Is there an optimization for "IS NULL" operator in a SELECT query ?

> "SELECT count(*) WHERE NOT text IS NULL" > > requires that the complete text column is loaded. With a stored LOB > this results in crazy performance. How did you find that? What do you mean by "requires loading of the whole text column"? It pretty much can require even loading of text columns

Re: [sqlite] A question about sqlite : How could i get the rows affected by current sql statement ?

sqlite3_changes() is exactly what you should use in this case. And I didn't understand why did you find it unsuitable for you? Pavel On Mon, Mar 1, 2010 at 8:22 AM, yangchen wrote: > HI, > > How could i get the rows affected by current sql statement ? > >

[sqlite] A question about sqlite : How could i get the rows affected by current sql statement ?

HI, How could i get the rows affected by current sql statement ? "sqlite3_changes" can only get the rows affected by the most recently sql statement which really affect rows. But i don't know whether current sql statement can affect rows. For example: 1) First ,i open the database connection.

[sqlite] "suitable UI" -- RE: What's the problem with my INSERT clause?

Regarding: BTW, can you recommend me suitable UI to work with SQLite? Maybe you've already tried out some of the GUI tools listed at: http://www.sqlite.org/cvstrac/wiki?p=ManagementTools I'm not sure what your requirements and preferences are, or what operating system you're using, or

Re: [sqlite] Minor odd behaviour in sql parsing (maybe a bug?)

wcl...@gfs-hofheim.de wrote: > The following sql is supposedly valid: > > select 1 in (1)'hello'; Apparently this gets interpreted as select (1 in (1)) as "hello"; ("as" is optional), under SQLite's long-standing policy of accepting single and double quotes more or less interchangeably. --

Re: [sqlite] dynamic typing misunderstanding

On Mar 1, 2010, at 6:08 PM, Alexey Pechnikov wrote: >> It would seem to me that asking [1='1'] *should* return false >> because the >> numeric and character string value domains are logically disjoint, >> so no value >> from one could ever equal a value from another, and so SQLite's >>

Re: [sqlite] dynamic typing misunderstanding

Hello! On Monday 01 March 2010 08:05:06 Darren Duncan wrote: > Alexey Pechnikov wrote: > > Hello! > > > > On Thursday 25 February 2010 02:53:32 Igor Tandetnik wrote: > >> http://www.sqlite.org/datatype3.html > >> For conversions between TEXT and REAL storage classes, SQLite considers > >> the

[sqlite] Minor odd behaviour in sql parsing (maybe a bug?)

Hi, I spotted this while looking into something else for Alexey: The following sql is supposedly valid: select 1 in (1)'hello'; where as select * from t where 1 in (1)'hello'; returns "Error: near "'hello'": syntax error" (which I would expect!) This occurs on v3.6.22, but I haven't

Re: [sqlite] The SQLite datatyping problem

> Now, it is probable that the core can be patched as you request by > assigning affinity to constants during parsing, but I haven't had an > opportunity to look into it and I would have to check very > thoroughly that it will not brake something else if I do patch it > for you. At this

[sqlite] Antwort: The SQLite datatyping problem

Alexey, I can certainly tell you what is causing this behaviour: When sqlite does a comparison, it checks the *column* affinity from both sides. In your example, neither sides come from a column, but are constants. Therefore, sqlite uses the NONE affinity on comparison (I think, but I