Re: [sqlite] BUG? sqlite not reporting error like "more than one row returned by a subquery used as an expression"

[Petite Abeille]

On Feb 20, 2013, at 9:35 PM, "Jay A. Kreibich"  wrote:

>  Not covert... works as documented:

"Let us be charitable, and call it a misleading feature" -- Larry Wall

> Not random either... at least, not any more random than any other
>  query.  Result order is never meaningful unless there is an
>  ORDER BY.

Q.E.D. 

> As for "productive", I suppose that depends on if you want SQL to
>  find poorly thought out queries on behalf of the developer, or just
>  assume the developer knows what they're doing and do the best it can
>  with what it was given.

That the problem right there:  … " do the best it can with what it was given"… 
That's basically second guessing and is rather harmful. Just the opposite of 
the first assertion ( "assume the developer knows what they're doing" ).  
SQLite shouldn't assume, or guess, anything and just fail-fast instead. 
Everyone would be better off that way.


> For good or bad, SQL is definitely a "shoot
>  yourself in the foot" language.

Nah. No more or less than any other programmatic constructs. On the other hand, 
there is a clear tendency in SQLite for creative second guessing (scalar, group 
by, etc) or ignore issues altogether (constraints violations opacity).

Just my 2¢ though.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UTF-8

[Richard Hipp]

On Wed, Feb 20, 2013 at 3:43 PM, Marco Bambini  wrote:

> Sometimes instead of a query like:
> "INSERT INTO foo (col1) VALUES ('Boxhagener Straße');"
>
> I receive the utf-8 encoded counterpart:
> "INSERT INTO foo (col1) VALUES ('Boxhagener Str\u00c3\u009fe');"
>
> Is there a way to automatically decode this latest query to UTF-8 just
> using sqlite API?
> Please note that since I have access to the sqlite amalgamation source
> code I could also expose an internal function.
>

SQLite assumes that all of its inputs are UTF (either UTF8 or UTF16).  If
you give it an input of MBCS, it will store what you give it, byte for
byte, but for comparison and lookup purposes it always assumes it is UTF.

SQLite never tries to convert between MBCS and UTF (except as required deep
down inside the windows VFS, but that shouldn't be visible to the
application.)



>
> Thanks.
> --
> Marco Bambini
> http://www.sqlabs.com
> http://twitter.com/sqlabs
> http://instagram.com/sqlabs
>
>
>
> ___
> 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

[sqlite] UTF-8

[Marco Bambini]

Sometimes instead of a query like:
"INSERT INTO foo (col1) VALUES ('Boxhagener Straße');"

I receive the utf-8 encoded counterpart:
"INSERT INTO foo (col1) VALUES ('Boxhagener Str\u00c3\u009fe');"

Is there a way to automatically decode this latest query to UTF-8 just using 
sqlite API?
Please note that since I have access to the sqlite amalgamation source code I 
could also expose an internal function.

Thanks.
--
Marco Bambini
http://www.sqlabs.com
http://twitter.com/sqlabs
http://instagram.com/sqlabs



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

Re: [sqlite] BUG? sqlite not reporting error like "more than one row returned by a subquery used as an expression"

[Petite Abeille]

On Feb 20, 2013, at 9:29 PM, Richard Hipp  wrote:

> On Wed, Feb 20, 2013 at 3:25 PM, Petite Abeille 
> wrote:
> 
>> 
>> On Feb 20, 2013, at 2:15 PM, Richard Hipp  wrote:
>> 
>>> SQLite automatically adds a LIMIT 1 to a scalar subquery.
>> 
>> Yeah… that's a bit of a death trap though… would be much more productive
>> if SQLite would raise an exception instead of doing something covert and
>> random...
>> 
> 
> There are over one million applications that use SQLite as it is currently
> implement.  Perhaps you are right that the proposed behavior makes better
> sense.  (Or perhaps not - the case can be argued.)  But how many of those
> one million applications would be busted and need to be fixed?  You want to
> test them all?

Well… considering that such applications are already broken as is… making such 
defect explicit might be beneficial overall… just imagine how many hidden, 
potentially dangerous, bugs SQLite could uncover in one swell swoop... 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] BUG? sqlite not reporting error like "more than one row returned by a subquery used as an expression"

[Jay A. Kreibich]

On Wed, Feb 20, 2013 at 09:25:54PM +0100, Petite Abeille scratched on the wall:
> On Feb 20, 2013, at 2:15 PM, Richard Hipp  wrote:
> 
> > SQLite automatically adds a LIMIT 1 to a scalar subquery.
> 
> Yeah? that's a bit of a death trap though? would be much more productive
> if SQLite would raise an exception instead of doing something covert 
> and random... 

  Not covert... works as documented:

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

Scalar Subqueries

A SELECT statement enclosed in parentheses may appear as a scalar
quantity. A SELECT used as a scalar quantity must return a result
set with a single column. The result of the expression is the value
of the only column in the first row returned by the SELECT
statement. If the SELECT yields more than one result row, all rows
after the first are ignored. If the SELECT yields no rows, then the
value of the expression is NULL. The LIMIT of a scalar subquery is
always 1. Any other LIMIT value given in the SQL text is ignored.

All types of SELECT statement, including aggregate and compound
SELECT queries (queries with keywords like UNION or EXCEPT) are
allowed as scalar subqueries. 


  Not random either... at least, not any more random than any other
  query.  Result order is never meaningful unless there is an
  ORDER BY.


  As for "productive", I suppose that depends on if you want SQL to
  find poorly thought out queries on behalf of the developer, or just
  assume the developer knows what they're doing and do the best it can
  with what it was given.  For good or bad, SQL is definitely a "shoot
  yourself in the foot" language.

   -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] BUG? sqlite not reporting error like "more than one row returned by a subquery used as an expression"

[Richard Hipp]

On Wed, Feb 20, 2013 at 3:25 PM, Petite Abeille wrote:

>
> On Feb 20, 2013, at 2:15 PM, Richard Hipp  wrote:
>
> > SQLite automatically adds a LIMIT 1 to a scalar subquery.
>
> Yeah… that's a bit of a death trap though… would be much more productive
> if SQLite would raise an exception instead of doing something covert and
> random...
>

There are over one million applications that use SQLite as it is currently
implement.  Perhaps you are right that the proposed behavior makes better
sense.  (Or perhaps not - the case can be argued.)  But how many of those
one million applications would be busted and need to be fixed?  You want to
test them all?


-- 
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] BUG? sqlite not reporting error like "more than one row returned by a subquery used as an expression"

[Petite Abeille]

On Feb 20, 2013, at 2:15 PM, Richard Hipp  wrote:

> SQLite automatically adds a LIMIT 1 to a scalar subquery.

Yeah… that's a bit of a death trap though… would be much more productive if 
SQLite would raise an exception instead of doing something covert and random... 

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

Re: [sqlite] Query Plan with FTS Tables

[GB]

Clemens Ladisch schrieb am 20.02.2013 10:54:

GB wrote:

we are facing some Problems with Queries joining several FTS-Tables.

FTS tables cannot do efficient lookups except by docid or by MATCHing.

If you have to do joins with other tables, you typically set the docid
in the FTS table to some primary/foreign key in the other tables, and
then use the docids returned from some MATCH:
Yep, that's exactly what we are doing. But the regular Tables are not 
causing any grief.
We already learned about fts4aux-Tables and are currently investigating 
how we can make use of that info to create some better performing 
statements. Any hints of course are greatly apprechiated.


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

Re: [sqlite] :memory: performance in sqlite 3.6

[Seebs]

On 20 Feb 2013, at 11:47, Simon Slavin wrote:


On 20 Feb 2013, at 5:32pm, Seebs  wrote:


First: The SQL is completely trivial.
Second: I am not having performance problems with sqlite, I am having 
performance problems with :memory:. Performance on files is lovely.


This normally means that your computer has very little actual memory 
to devote to 'memory' uses.  It is using a lot of swap space (or 
paging, or virtual memory, whatever you want to call it).  So when you 
think you are doing calls which reference ':memory:' it actually has 
to fetch and write those parts of memory to disk.


I am pretty sure that's not it.

I know I'm a newbie on the list, but please imagine for the sake of 
argument that I am a basically competent programmer with enough sysadmin 
background to be aware of the obvious problems.


In this case, I tested this pretty carefully over a period of about six 
hours of testing across multiple machines. I was watching CPU load, 
memory load, and all that stuff. And this is a machine with >4GB of 
*free* memory -- that's over and above even the couple GB of disk cache 
being used.


Process memory size is not appreciably different between sqlite 3.6 and 
3.7, or between page size of 1024 or 8192. Runtime is massively 
different. I am pretty sure this is an actual computation-time issue, 
and my intuition is that it's quite possible there's at least some other 
performance issues lurking, because it appears that :memory: *used to 
be* dramatically faster than disk, but something changed in the last 
couple of years.


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

Re: [sqlite] sqlite4 lsm storage engine

[Dan Kennedy]

On 02/21/2013 12:31 AM, Rob Turpin wrote:

Dan,

I'm getting an LSM_OK on lsm_close.  I attached the writer and reader test
case.  If you comment out the lsm_config call that turns off logging, all
the writes get in.


I think the mailing list stripped the attachment. Can you send them
to me directly?

Thanks,
Dan.





Thanks,
Rob

On Wed, Feb 20, 2013 at 9:43 AM, Dan Kennedy  wrote:


On 02/20/2013 11:37 PM, Rob Turpin wrote:


Yes.



Is it succeeding? Returning LSM_OK?

Dan.





On Wed, Feb 20, 2013 at 3:14 AM, Dan Kennedy 
wrote:

  On 02/20/2013 05:07 PM, Rob Turpin wrote:


  I'm running some performance tests on the lsm storage engine, and an

issue
has cropped up for me.  I retrieved the sqlite4 code from the repository
about a week ago.

I'm doing a simple single threaded test to see what kind of performance
I
can get on write transactions.  After completing the write test, I run
another program to read the DB, and all the writes are not there.

Here's are the config options I set before calling lsm_open.

int iVal = 0;
lsm_config(db, LSM_CONFIG_MULTIPLE_PROCESSES, &iVal);
lsm_config(db, LSM_CONFIG_USE_LOG, &iVal);
iVal = 4096;
lsm_config(db, LSM_CONFIG_AUTOFLUSH, &iVal);
iVal = 8192;
lsm_config(db, LSM_CONFIG_AUTOCHECKPOINT, &iVal);


If logging is on, there is no issue, all writes are in the DB.  But
since
I'm trying to increase performance I turn this off.

   From reading the documentation I was assuming the
LSM_CONFIG_AUTOCHECKPOINT
setting should sync the writes in memory to disk at 8MB (per my setting,
default is 2 MB).  I'm performing no explicit commits or checkpoints.
  I'm
simply calling lsm_insert to write data to the DB.  Perhaps this is
where
I'm confused about the expected behavior.

Could anyone correct me where I'm wrong on my assumptions, or could
there
be an issue with this?



Are you calling lsm_close() at the end of the write test?

Dan.




___
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



__**_
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


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

Re: [sqlite] :memory: performance in sqlite 3.6

[Simon Slavin]

On 20 Feb 2013, at 5:32pm, Seebs  wrote:

> First: The SQL is completely trivial.
> Second: I am not having performance problems with sqlite, I am having 
> performance problems with :memory:. Performance on files is lovely.

This normally means that your computer has very little actual memory to devote 
to 'memory' uses.  It is using a lot of swap space (or paging, or virtual 
memory, whatever you want to call it).  So when you think you are doing calls 
which reference ':memory:' it actually has to fetch and write those parts of 
memory to disk.

One way to spot this is to use a utility which shows how much of your memory is 
used for what.  So use nmap or Task Manager or Activity Monitor or whatever to 
show memory usage on your computer, then open a database in ':memory:' and 
write 50 Meg of data to it.  If one of the figures went up by 50 Meg then all 
that data is actually being kept in RAM.  But I bet it's actually being written 
to disk.  Because your OS is devoting lots of your RAM to other tasks and 
doesn't have enough room free.  And since ':memory:' is actually being done by 
writing to disk, it's not faster than opening a database on disk.

Simon.

[Warning: I have omitted many distinctions in the above.  If you want a 
sophisticated discussion of the difference between swapspace and virtual memory 
you know where to find it.]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] :memory: performance in sqlite 3.6

[Seebs]

On 20 Feb 2013, at 5:20, Eduardo Morras wrote:

Execution time doing what?, Waiting for I/O? How do you get execution 
time? What sql are you doing?


I'm using sqlite3_profile and summing the reported times in nanoseconds.

Don't run with synchronous off, it's only calms the symptom, don't 
cure/repair the problem and can mask the real problem.


We cannot afford to have any fsyncs ocurring. Host system limitation; if 
sqlite asks for its database to be flushed to disk, that means two dozen 
other disk-intensive processes suddenly have to wait for all their 
writes to be flushed too.



Are you using a join?


No.

I don't use :memory: db, when need to do so, i use a normal db with 
10-20% more page cache than file size. In rare use cases, i use a ram 
memory disk and backup().


I don't have any way to know in advance what the filesize might be.


The test case I was using was pseudo version 1.4.5, on Linux hosts,
using the pseudo wrapper to untar a 28,000 file tarball.


Surely I'm misinterpreted it but, Is the sqlite db in a directory with 
28000 files? Each time a journal or temporal file is created, modified 
and deleted, and the main db file is modified, the directory entry 
must be updated and with 28000 files it's a very slow process.


There are no journal files, journaling is also off.

"pseudo" is a program in which all file-related syscalls in a client 
program are routed through to a server. The server is using sqlite to 
maintain a database of files. The database stores virtualized 
permissions and ownership. It's used to allow a build system to create 
root filesystems without root privileges. There are no rollbacks, and 
data persistence is mostly short-term; most databases last under ten 
minutes.


Please, post an example of your sql, perhaps it can be tuned for 
sqlite.


First: The SQL is completely trivial.
Second: I am not having performance problems with sqlite, I am having 
performance problems with :memory:. Performance on files is lovely.


My claim is that, at a bare minimum, a :memory: database should not be 
SLOWER than a file on disk. It probably ought to be faster, but slower 
implies that something has gone horribly wrong.


No joins, only one table, only a couple of things used as keys, they 
have indexes. Performance is adequate without :memory:.


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

Re: [sqlite] sqlite4 lsm storage engine

[Rob Turpin]

Dan,

I'm getting an LSM_OK on lsm_close.  I attached the writer and reader test
case.  If you comment out the lsm_config call that turns off logging, all
the writes get in.

Thanks,
Rob

On Wed, Feb 20, 2013 at 9:43 AM, Dan Kennedy  wrote:

> On 02/20/2013 11:37 PM, Rob Turpin wrote:
>
>> Yes.
>>
>
> Is it succeeding? Returning LSM_OK?
>
> Dan.
>
>
>
>
>> On Wed, Feb 20, 2013 at 3:14 AM, Dan Kennedy 
>> wrote:
>>
>>  On 02/20/2013 05:07 PM, Rob Turpin wrote:
>>>
>>>  I'm running some performance tests on the lsm storage engine, and an
 issue
 has cropped up for me.  I retrieved the sqlite4 code from the repository
 about a week ago.

 I'm doing a simple single threaded test to see what kind of performance
 I
 can get on write transactions.  After completing the write test, I run
 another program to read the DB, and all the writes are not there.

 Here's are the config options I set before calling lsm_open.

 int iVal = 0;
 lsm_config(db, LSM_CONFIG_MULTIPLE_PROCESSES, &iVal);
 lsm_config(db, LSM_CONFIG_USE_LOG, &iVal);
 iVal = 4096;
 lsm_config(db, LSM_CONFIG_AUTOFLUSH, &iVal);
 iVal = 8192;
 lsm_config(db, LSM_CONFIG_AUTOCHECKPOINT, &iVal);


 If logging is on, there is no issue, all writes are in the DB.  But
 since
 I'm trying to increase performance I turn this off.

   From reading the documentation I was assuming the
 LSM_CONFIG_AUTOCHECKPOINT
 setting should sync the writes in memory to disk at 8MB (per my setting,
 default is 2 MB).  I'm performing no explicit commits or checkpoints.
  I'm
 simply calling lsm_insert to write data to the DB.  Perhaps this is
 where
 I'm confused about the expected behavior.

 Could anyone correct me where I'm wrong on my assumptions, or could
 there
 be an issue with this?


>>> Are you calling lsm_close() at the end of the write test?
>>>
>>> Dan.
>>>
>>>
>>>
>>>
>>> ___
>>> 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
>>
>>
> __**_
> 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] :memory: performance in sqlite 3.6

[Seebs]

On 20 Feb 2013, at 3:59, Clemens Ladisch wrote:


Seebs wrote:

I tried ... an in-memory database.
What I observed was a very, very, large slowdown.
We're talking 10MB of database



That database would be in the OS's file cache anyway.


It should. Problem is, there's a nearby program that was doing fsync() 
operations on something else, and the filesystem's implementation is 
such that an fsync on ANY file is a complete disk flush for everybody. 
So avoiding any writes to the filesystem is really important even with 
synchronous = OFF.



3. It is dramatically reduced in degree by pragma page_size = 8192.



The default cache size is measured in pages.
So maybe your cache size is too small?


Well, that's sort of the question: Should cache size be relevant for a 
:memory: db?



Could you show some example query?
What is the EXPLAIN QUERY PLAN output?


All the queries are completely trivial. "SELECT * FROM files WHERE path 
= '...';" type stuff. They match indexes. And again, this is all 
performance problems *compared to writing to a disk*.


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

Re: [sqlite] sqlite4 lsm storage engine

[Dan Kennedy]

On 02/20/2013 11:37 PM, Rob Turpin wrote:

Yes.


Is it succeeding? Returning LSM_OK?

Dan.





On Wed, Feb 20, 2013 at 3:14 AM, Dan Kennedy  wrote:


On 02/20/2013 05:07 PM, Rob Turpin wrote:


I'm running some performance tests on the lsm storage engine, and an issue
has cropped up for me.  I retrieved the sqlite4 code from the repository
about a week ago.

I'm doing a simple single threaded test to see what kind of performance I
can get on write transactions.  After completing the write test, I run
another program to read the DB, and all the writes are not there.

Here's are the config options I set before calling lsm_open.

int iVal = 0;
lsm_config(db, LSM_CONFIG_MULTIPLE_PROCESSES, &iVal);
lsm_config(db, LSM_CONFIG_USE_LOG, &iVal);
iVal = 4096;
lsm_config(db, LSM_CONFIG_AUTOFLUSH, &iVal);
iVal = 8192;
lsm_config(db, LSM_CONFIG_AUTOCHECKPOINT, &iVal);


If logging is on, there is no issue, all writes are in the DB.  But since
I'm trying to increase performance I turn this off.

  From reading the documentation I was assuming the
LSM_CONFIG_AUTOCHECKPOINT
setting should sync the writes in memory to disk at 8MB (per my setting,
default is 2 MB).  I'm performing no explicit commits or checkpoints.  I'm
simply calling lsm_insert to write data to the DB.  Perhaps this is where
I'm confused about the expected behavior.

Could anyone correct me where I'm wrong on my assumptions, or could there
be an issue with this?



Are you calling lsm_close() at the end of the write test?

Dan.




__**_
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



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

Re: [sqlite] sqlite4 lsm storage engine

[Rob Turpin]

Yes.

On Wed, Feb 20, 2013 at 3:14 AM, Dan Kennedy  wrote:

> On 02/20/2013 05:07 PM, Rob Turpin wrote:
>
>> I'm running some performance tests on the lsm storage engine, and an issue
>> has cropped up for me.  I retrieved the sqlite4 code from the repository
>> about a week ago.
>>
>> I'm doing a simple single threaded test to see what kind of performance I
>> can get on write transactions.  After completing the write test, I run
>> another program to read the DB, and all the writes are not there.
>>
>> Here's are the config options I set before calling lsm_open.
>>
>> int iVal = 0;
>> lsm_config(db, LSM_CONFIG_MULTIPLE_PROCESSES, &iVal);
>> lsm_config(db, LSM_CONFIG_USE_LOG, &iVal);
>> iVal = 4096;
>> lsm_config(db, LSM_CONFIG_AUTOFLUSH, &iVal);
>> iVal = 8192;
>> lsm_config(db, LSM_CONFIG_AUTOCHECKPOINT, &iVal);
>>
>>
>> If logging is on, there is no issue, all writes are in the DB.  But since
>> I'm trying to increase performance I turn this off.
>>
>>  From reading the documentation I was assuming the
>> LSM_CONFIG_AUTOCHECKPOINT
>> setting should sync the writes in memory to disk at 8MB (per my setting,
>> default is 2 MB).  I'm performing no explicit commits or checkpoints.  I'm
>> simply calling lsm_insert to write data to the DB.  Perhaps this is where
>> I'm confused about the expected behavior.
>>
>> Could anyone correct me where I'm wrong on my assumptions, or could there
>> be an issue with this?
>>
>
> Are you calling lsm_close() at the end of the write test?
>
> Dan.
>
>
>
>
> __**_
> 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] Maximum Number of Open Simultaneous connections in the latest release of SQLite

[Simon Slavin]

On 20 Feb 2013, at 3:28pm, Dominique Devienne  wrote:

> On Wed, Feb 20, 2013 at 2:07 PM, Simon Slavin  wrote:
> 
>> On 20 Feb 2013, at 12:57pm, Frank Chang  wrote:
>>> Good morning, Could someone please tell us the maximum number of open
>>> simultaneous connections in the latest release of SQLite? Thank you.
>> 
>> There is no limit for open connections built into SQLite because there is
>> no part of SQLite which counts open connections.  You can have as many as
>> you have memory available, file handles available, or any other resource
>> the operating system needs.  Whatever it is, it's a high number rather than
>> something like 10.
> 
> Perhaps the poster meant the number of attached databases to a particular
> connection instead? (62 max as of now).

Or it could have been the number of different users/apps/computers connecting 
to the same database at the same time.  Or the number of different uses of 
sqlite3_open() you could have at the same time.  I couldn't work out what the 
OP wanted.

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

Re: [sqlite] Maximum Number of Open Simultaneous connections in the latest release of SQLite

[Dominique Devienne]

On Wed, Feb 20, 2013 at 2:07 PM, Simon Slavin  wrote:

> On 20 Feb 2013, at 12:57pm, Frank Chang  wrote:
> > Good morning, Could someone please tell us the maximum number of open
> > simultaneous connections in the latest release of SQLite? Thank you.
>
> There is no limit for open connections built into SQLite because there is
> no part of SQLite which counts open connections.  You can have as many as
> you have memory available, file handles available, or any other resource
> the operating system needs.  Whatever it is, it's a high number rather than
> something like 10.
>

Perhaps the poster meant the number of attached databases to a particular
connection instead? (62 max as of now).

But as you wrote, there is no SQLite-imposed limit on how many separate
connections can be created. --DD
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] got selected items ok but can't update using them

[e-mail mgbg25171]

Igor, Michael
This syntax "create table copy as select * from input.t1;" is very new to
me but certainly does "the business" in conjuntion with "attach"
SQLIte Manager doesn't know what "dump" is
Irrespective...thank you very much for your help.


On 20 February 2013 13:56, Michael Black  wrote:

> Sqlite3 run without any arguments
> But did I discover a bug too?
> Why doesn't .dump allow input.t1 to be shown?
>
> SQLite version 3.7.14.1 2012-10-04 19:37:12
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> attach database "blah.db" as input;
> sqlite> create table input.t1(n);
> sqlite> insert into input.t1 values(1);
> sqlite> insert into input.t1 values(2);
> sqlite> create table copy as select * from input.t1;
> sqlite> .dump
> PRAGMA foreign_keys=OFF;
> BEGIN TRANSACTION;
> CREATE TABLE copy(n);
> INSERT INTO "copy" VALUES(1);
> INSERT INTO "copy" VALUES(2);
> COMMIT;
> sqlite> .dump input.t1
> PRAGMA foreign_keys=OFF;
> BEGIN TRANSACTION;
> COMMIT;
> sqlite> select * from t1;
> 1
> 2
>
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of e-mail mgbg25171
> Sent: Wednesday, February 20, 2013 7:46 AM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] got selected items ok but can't update using them
>
> Igor, Michael
> Thank you very much for your advice re how to use Update with compound
> queries
>
> I'm currently trying to copy the rows of stmnts with a blank itm field to a
> temporary table.
> I don't think virtual tables are implemented in my programming language's
> interface and so wanted to
> copy the stmnts table from the real database where itm='' to a table in a
> :memory: database.
> I appreciate that the statement is normally
> insert into tbl values (fld1 integer, fld2 text);
> but how do I fill a table in a :memory: db with the contents of another in
> a real database?
> I'll keep looking into it.
>
> ___
> 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] got selected items ok but can't update using them

[Michael Black]

Sqlite3 run without any arguments
But did I discover a bug too?
Why doesn't .dump allow input.t1 to be shown?

SQLite version 3.7.14.1 2012-10-04 19:37:12
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> attach database "blah.db" as input;
sqlite> create table input.t1(n);
sqlite> insert into input.t1 values(1);
sqlite> insert into input.t1 values(2);
sqlite> create table copy as select * from input.t1;
sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE copy(n);
INSERT INTO "copy" VALUES(1);
INSERT INTO "copy" VALUES(2);
COMMIT;
sqlite> .dump input.t1
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
COMMIT;
sqlite> select * from t1;
1
2


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of e-mail mgbg25171
Sent: Wednesday, February 20, 2013 7:46 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] got selected items ok but can't update using them

Igor, Michael
Thank you very much for your advice re how to use Update with compound
queries

I'm currently trying to copy the rows of stmnts with a blank itm field to a
temporary table.
I don't think virtual tables are implemented in my programming language's
interface and so wanted to
copy the stmnts table from the real database where itm='' to a table in a
:memory: database.
I appreciate that the statement is normally
insert into tbl values (fld1 integer, fld2 text);
but how do I fill a table in a :memory: db with the contents of another in
a real database?
I'll keep looking into it.

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

Re: [sqlite] got selected items ok but can't update using them

[Igor Tandetnik]

On 2/20/2013 8:46 AM, e-mail mgbg25171 wrote:

but how do I fill a table in a :memory: db with the contents of another in
a real database?


http://sqlite.org/lang_attach.html

--
Igor Tandetnik

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

Re: [sqlite] got selected items ok but can't update using them

[e-mail mgbg25171]

Igor, Michael
Thank you very much for your advice re how to use Update with compound
queries

I'm currently trying to copy the rows of stmnts with a blank itm field to a
temporary table.
I don't think virtual tables are implemented in my programming language's
interface and so wanted to
copy the stmnts table from the real database where itm='' to a table in a
:memory: database.
I appreciate that the statement is normally
insert into tbl values (fld1 integer, fld2 text);
but how do I fill a table in a :memory: db with the contents of another in
a real database?
I'll keep looking into it.



On 20 February 2013 13:17, Michael Black  wrote:

> You need the selects in each = expression to replace the variables there.
> Something like this if I translated your query correctly.
>
> UPDATE stmnts SET itm=(SELECT itm FROM std_itms where ID = std_id) where
> ID=(SELECT alias_id from alias_itms);
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of e-mail mgbg25171
> Sent: Wednesday, February 20, 2013 6:46 AM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] got selected items ok but can't update using them
>
> Thanks Simon...
> Your's works a treat
> Re your question about my error
>
> Here's my query
>
> UPDATE stmnts SET itm=n where ID=i
> (
> SELECT alias_id i,
> (SELECT orig_itm FROM stmnts where ID = alias_id) o,
> (SELECT itm FROM std_itms where ID = std_id) n
> FROM
> (SELECT std_id, alias_id FROM alias_itms)
> )
>
>
>
> ___
> 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] BUG? sqlite not reporting error like "more than one row returned by a subquery used as an expression"

[Simon Slavin]

On 20 Feb 2013, at 12:17pm, Marcin Kałuża  wrote:

> sqlite> select * from t where v = (select v from t);
> 1

There is no definition for what this means under SQL.  Any SQL implementation 
might consider it an error, or always evaluate (item = list) as false, or do 
anything else.  I don't think you can point to either postgres or SQLite as 
definitely being buggy on this one.

By the way it is an error to use postgres as a model of correct behaviour about 
SQL.  It has just as many weird characteristics as the other implementations of 
SQL I've seen.  Since SQL is not procedurally specified there are many muddy 
areas in its definition.

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

Re: [sqlite] got selected items ok but can't update using them

[Michael Black]

You need the selects in each = expression to replace the variables there.
Something like this if I translated your query correctly.

UPDATE stmnts SET itm=(SELECT itm FROM std_itms where ID = std_id) where
ID=(SELECT alias_id from alias_itms);

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of e-mail mgbg25171
Sent: Wednesday, February 20, 2013 6:46 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] got selected items ok but can't update using them

Thanks Simon...
Your's works a treat
Re your question about my error

Here's my query

UPDATE stmnts SET itm=n where ID=i
(
SELECT alias_id i,
(SELECT orig_itm FROM stmnts where ID = alias_id) o,
(SELECT itm FROM std_itms where ID = std_id) n
FROM
(SELECT std_id, alias_id FROM alias_itms)
)



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

Re: [sqlite] BUG? sqlite not reporting error like "more than one row returned by a subquery used as an expression"

[Richard Hipp]

On Wed, Feb 20, 2013 at 7:17 AM, Marcin Kałuża wrote:

> We've encountered strange sqlite behavior:
>
> SQLite version 3.7.15.2 2013-01-09 11:53:05
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> create table t(v int4);
> sqlite> insert into t values (1),(2),(3);
> sqlite> select * from t where v = (select v from t);
> 1
> sqlite> select * from t where v in (select v from t);
> v
> --
> 1
> 2
> 3
>
> While on postgres it works like this (as far as I remember oracle does
> this as well, and that's the way it should work imho):
> mail=> create table t (v int4);
> CREATE TABLE
> mail=> insert into t values (1),(2),(3);
> INSERT 0 3
> mail=> select * from t where v = (select v from t);
> ERROR:  more than one row returned by a subquery used as an expression
> mail=> select * from t where v in (select v from t);
>  v
> ---
>  1
>  2
>  3
> (3 rows)
>
> Is this a bug, or a feature?
>

SQLite automatically adds a LIMIT 1 to a scalar subquery.



-- 
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] got selected items ok but can't update using them

[Igor Tandetnik]

On 2/20/2013 7:45 AM, e-mail mgbg25171 wrote:

Here's my query

UPDATE stmnts SET itm=n where ID=i
(
SELECT alias_id i,
(SELECT orig_itm FROM stmnts where ID = alias_id) o,
(SELECT itm FROM std_itms where ID = std_id) n
FROM
(SELECT std_id, alias_id FROM alias_itms)
)


This doesn't even remotely resemble a syntactically valid UPDATE statement.
--
Igor Tandetnik

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

Re: [sqlite] SQLite error near "16": syntax error

[Michael Black]

You're inserting a lot of stringsso you have to put single quotes around
all the string fields on your insert.

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of mikkelzuuu
Sent: Tuesday, February 19, 2013 7:16 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] SQLite error near "16": syntax error

1-2-2013 16:58|H2|NL-Radio 2|2013-01-03T00:00:00.000Z|172806528

An example of the output that I have to do. I see the 16 s on the first cell
and the time, but I wouldn't know why its giving me an error there. Would I
have to change the setup of the Cell in my SQLite Database?



--
View this message in context:
http://sqlite.1065341.n5.nabble.com/SQLite-error-near-16-syntax-error-tp6708
6p67091.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

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

Re: [sqlite] got selected items ok but can't update using them

[Igor Tandetnik]

On 2/20/2013 6:36 AM, e-mail mgbg25171 wrote:

SELECT i,o,n FROM
(
SELECT alias_id i,
(SELECT orig_itm FROM stmnts where ID = alias_id) o,
(SELECT itm FROM std_itms where ID = std_id) n
FROM
(SELECT std_id, alias_id FROM alias_itms)
)


This can be written simpler as

select alias_id i, orig_itm o, std_itms.itm n
from alias_itms join stmnts on (alias_id = stmnts.ID)
  join std_itms on (std_id = std_itms.ID);

--
Igor Tandetnik

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

[sqlite] BUG? sqlite not reporting error like "more than one row returned by a subquery used as an expression"

[Marcin Kałuża]

We've encountered strange sqlite behavior:

SQLite version 3.7.15.2 2013-01-09 11:53:05
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table t(v int4);
sqlite> insert into t values (1),(2),(3);
sqlite> select * from t where v = (select v from t);
1
sqlite> select * from t where v in (select v from t);
v
--
1
2
3

While on postgres it works like this (as far as I remember oracle does 
this as well, and that's the way it should work imho):

mail=> create table t (v int4);
CREATE TABLE
mail=> insert into t values (1),(2),(3);
INSERT 0 3
mail=> select * from t where v = (select v from t);
ERROR:  more than one row returned by a subquery used as an expression
mail=> select * from t where v in (select v from t);
 v
---
 1
 2
 3
(3 rows)

Is this a bug, or a feature?

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

Re: [sqlite] SQLite error near "16": syntax error

[Michael Lindemans]

Well the database isn't going online, its a local database for myself to keep
track of recordings etc.
But I'll look into that so I can maybe fix it by using a different query.



--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/SQLite-error-near-16-syntax-error-tp67086p67129.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] SQLite error near "16": syntax error

[mikkelzuuu]

1-2-2013 16:58|H2|NL-Radio 2|2013-01-03T00:00:00.000Z|172806528

An example of the output that I have to do. I see the 16 s on the first cell
and the time, but I wouldn't know why its giving me an error there. Would I
have to change the setup of the Cell in my SQLite Database?



--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/SQLite-error-near-16-syntax-error-tp67086p67091.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] Maximum Number of Open Simultaneous connections in the latest release of SQLite

[Simon Slavin]

On 20 Feb 2013, at 12:57pm, Frank Chang  wrote:

> Good morning, Could someone please tell us the maximum number of open
> simultaneous connections in the latest release of SQLite? Thank you.

All the maxima are here:



There is no limit for open connections built into SQLite because there is no 
part of SQLite which counts open connections.  You can have as many as you have 
memory available, file handles available, or any other resource the operating 
system needs.  Whatever it is, it's a high number rather than something like 10.

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

Re: [sqlite] got selected items ok but can't update using them

[e-mail mgbg25171]

Thanks Simon...
Your's works a treat
Re your question about my error

Here's my query

UPDATE stmnts SET itm=n where ID=i
(
SELECT alias_id i,
(SELECT orig_itm FROM stmnts where ID = alias_id) o,
(SELECT itm FROM std_itms where ID = std_id) n
FROM
(SELECT std_id, alias_id FROM alias_itms)
)

and here's my error

SQLiteManager: Likely SQL syntax error: UPDATE stmnts SET itm=n where ID=i
(
SELECT alias_id i,
(SELECT orig_itm FROM stmnts where ID = alias_id) o,
(SELECT itm FROM std_itms where ID = std_id) n
FROM
(SELECT std_id, alias_id FROM alias_itms)
)
 [ near "SELECT": syntax error ]
Exception Name: NS_ERROR_FAILURE
Exception Message: Component returned failure code: 0x80004005
(NS_ERROR_FAILURE) [mozIStorageConnection.createStatement]

Thanks for your help

On 20 February 2013 12:01, Simon Davies wrote:

> On 20 February 2013 11:36, e-mail mgbg25171 
> wrote:
> > After a bit of a struggle I've got the fields I want but I'm failing to
> use
> > them in an update operation.
> > This
> >
> > SELECT i,o,n FROM
> > (
> > SELECT alias_id i,
> > (SELECT orig_itm FROM stmnts where ID = alias_id) o,
> > (SELECT itm FROM std_itms where ID = std_id) n
> > FROM
> > (SELECT std_id, alias_id FROM alias_itms)
> > )
> >
> > works fine and gives me
> > i, o, n  where i, o, n equals id, old, new
> > 
> > 0, turnover, sales
> > 1, cost of sales, cogs
> >
> > now I want to say
> > update stmnts set itm = n where id = i
> > but I'm getting errors
>
> It would help to be clear what the errors are.
>
> >
> > Table fields
> > stmnts__id, orig_itm, itm
> > alias_itmsid, std_id, alias_id
> > std_itms_id, itm
> >
> > Any help much appreciated
>
> Maybe:
> update stmnts set itm=(select itm from std_itms s, alias_itms a on
> s.id=a.std_id where a.alias_id=stmnts.id);
>
> Regards,
> 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] got selected items ok but can't update using them

[Simon Davies]

On 20 February 2013 11:36, e-mail mgbg25171  wrote:
> After a bit of a struggle I've got the fields I want but I'm failing to use
> them in an update operation.
> This
>
> SELECT i,o,n FROM
> (
> SELECT alias_id i,
> (SELECT orig_itm FROM stmnts where ID = alias_id) o,
> (SELECT itm FROM std_itms where ID = std_id) n
> FROM
> (SELECT std_id, alias_id FROM alias_itms)
> )
>
> works fine and gives me
> i, o, n  where i, o, n equals id, old, new
> 
> 0, turnover, sales
> 1, cost of sales, cogs
>
> now I want to say
> update stmnts set itm = n where id = i
> but I'm getting errors

It would help to be clear what the errors are.

>
> Table fields
> stmnts__id, orig_itm, itm
> alias_itmsid, std_id, alias_id
> std_itms_id, itm
>
> Any help much appreciated

Maybe:
update stmnts set itm=(select itm from std_itms s, alias_itms a on
s.id=a.std_id where a.alias_id=stmnts.id);

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

[sqlite] got selected items ok but can't update using them

[e-mail mgbg25171]

After a bit of a struggle I've got the fields I want but I'm failing to use
them in an update operation.
This

SELECT i,o,n FROM
(
SELECT alias_id i,
(SELECT orig_itm FROM stmnts where ID = alias_id) o,
(SELECT itm FROM std_itms where ID = std_id) n
FROM
(SELECT std_id, alias_id FROM alias_itms)
)

works fine and gives me
i, o, n  where i, o, n equals id, old, new

0, turnover, sales
1, cost of sales, cogs

now I want to say
update stmnts set itm = n where id = i
but I'm getting errors

Table fields
stmnts__id, orig_itm, itm
alias_itmsid, std_id, alias_id
std_itms_id, itm

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

Re: [sqlite] :memory: performance in sqlite 3.6

[Eduardo Morras]

On Tue, 19 Feb 2013 16:05:14 -0600
Seebs  wrote:

> I'm afraid I haven't got a nicely isolated reproducer for this.
> 
> I maintain a package, called pseudo, which includes a server built 
> around an sqlite database. In the process of debugging some performance 
> issues, I did some casual benchmarking. One of the first things I tried 
> was an in-memory database. Now, as has been observed, this is not 
> necessarily expected to be dramatically faster than an on-disk database.
> 
> What I observed was a very, very, large slowdown. Time for an overall 
> task relying on the database was increased by a factor of 2-3 -- and the 
> database code is not that significant a part of the runtime, usually. I 
> used the sqlite3_profile() and observed that the sum of reported 
> processing time from that was within a few percent of the total increase 
> in execution time, which is at least suspicious.

Execution time doing what?, Waiting for I/O? How do you get execution time? 
What sql are you doing?

> I did a bunch of testing trying to figure out more about this (and many 
> thanks to the friendly folks in #sqlite IRC who helped suggest some).
> 
> First: No, not swapping or paging. We're talking 10MB of database 
> against 12GB of RAM with several GB free. The database on disk was 
> running synchronous = OFF, so I wasn't necessarily expecting huge 
> improvements.

Don't run with synchronous off, it's only calms the symptom, don't cure/repair 
the problem and can mask the real problem.

> In all cases, I was running against brand-new freshly created databases, 
> whether in memory or on disk.
> 
> What I found:

> 4. It scales roughly with database size; at 28,000 rows, it's quite 
> noticeable, and at 84,000 it's painful.

Are you using a join? 

> I did find one thing that made me at least a little suspicious even in 
> 3.7 (specifically 3.7.15.2). In sqlite3PagerMovepage, there's a comment 
> right up at the top about journaling the page we're moving from, so 
> there's a call to sqlite3PagerWrite() if MEMDB. There's no check for 
> journaling mode, and it seems to me that if journaling is off, this 
> shouldn't be needed.

I remember a previous discussion about this topic.

> But that's not nearly enough to explain this.
> 
> Admittedly, a performance issue which seems mostly fixed in 3.7 is 
> probably a lowish priority. What concerns me is that it seems to me that 
> the performance of :memory: may have taken a severe hit at some point, 
> leading to a flood of internet forum posts, stackoverflow questions, and 
> the like about poor performance of :memory:. Since sqlite is so fast to 
> begin with, this may not have gotten noticed.

I don't use :memory: db, when need to do so, i use a normal db with 10-20% more 
page cache than file size. In rare use cases, i use a ram memory disk and 
backup().

> The test case I was using was pseudo version 1.4.5, on Linux hosts, 
> using the pseudo wrapper to untar a 28,000 file tarball. 

Surely I'm misinterpreted it but, Is the sqlite db in a directory with 28000 
files? Each time a journal or temporal file is created, modified and deleted, 
and the main db file is modified, the directory entry must be updated and with 
28000 files it's a very slow process.

Please, post an example of your sql, perhaps it can be tuned for sqlite.

> I am not sure 
> how easy or hard it would be to duplicate this with a simpler test case, 
> and won't have time to look more closely for a while, if ever. I'm 
> passing this on in case this rings a bell for someone, and to have it in 
> the archives if someone else comes looking.
> 
> -s


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

Re: [sqlite] sqlite4 lsm storage engine

[Dan Kennedy]

On 02/20/2013 05:07 PM, Rob Turpin wrote:

I'm running some performance tests on the lsm storage engine, and an issue
has cropped up for me.  I retrieved the sqlite4 code from the repository
about a week ago.

I'm doing a simple single threaded test to see what kind of performance I
can get on write transactions.  After completing the write test, I run
another program to read the DB, and all the writes are not there.

Here's are the config options I set before calling lsm_open.

int iVal = 0;
lsm_config(db, LSM_CONFIG_MULTIPLE_PROCESSES, &iVal);
lsm_config(db, LSM_CONFIG_USE_LOG, &iVal);
iVal = 4096;
lsm_config(db, LSM_CONFIG_AUTOFLUSH, &iVal);
iVal = 8192;
lsm_config(db, LSM_CONFIG_AUTOCHECKPOINT, &iVal);


If logging is on, there is no issue, all writes are in the DB.  But since
I'm trying to increase performance I turn this off.

 From reading the documentation I was assuming the LSM_CONFIG_AUTOCHECKPOINT
setting should sync the writes in memory to disk at 8MB (per my setting,
default is 2 MB).  I'm performing no explicit commits or checkpoints.  I'm
simply calling lsm_insert to write data to the DB.  Perhaps this is where
I'm confused about the expected behavior.

Could anyone correct me where I'm wrong on my assumptions, or could there
be an issue with this?


Are you calling lsm_close() at the end of the write test?

Dan.




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

[sqlite] sqlite4 lsm storage engine

[Rob Turpin]

I'm running some performance tests on the lsm storage engine, and an issue
has cropped up for me.  I retrieved the sqlite4 code from the repository
about a week ago.

I'm doing a simple single threaded test to see what kind of performance I
can get on write transactions.  After completing the write test, I run
another program to read the DB, and all the writes are not there.

Here's are the config options I set before calling lsm_open.

int iVal = 0;
lsm_config(db, LSM_CONFIG_MULTIPLE_PROCESSES, &iVal);
lsm_config(db, LSM_CONFIG_USE_LOG, &iVal);
iVal = 4096;
lsm_config(db, LSM_CONFIG_AUTOFLUSH, &iVal);
iVal = 8192;
lsm_config(db, LSM_CONFIG_AUTOCHECKPOINT, &iVal);


If logging is on, there is no issue, all writes are in the DB.  But since
I'm trying to increase performance I turn this off.

>From reading the documentation I was assuming the LSM_CONFIG_AUTOCHECKPOINT
setting should sync the writes in memory to disk at 8MB (per my setting,
default is 2 MB).  I'm performing no explicit commits or checkpoints.  I'm
simply calling lsm_insert to write data to the DB.  Perhaps this is where
I'm confused about the expected behavior.

Could anyone correct me where I'm wrong on my assumptions, or could there
be an issue with this?

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

Re: [sqlite] :memory: performance in sqlite 3.6

[Clemens Ladisch]

Seebs wrote:
> I tried ... an in-memory database.
> What I observed was a very, very, large slowdown.
> We're talking 10MB of database

That database would be in the OS's file cache anyway.

> 3. It is dramatically reduced in degree by pragma page_size = 8192.

The default cache size is measured in pages.
So maybe your cache size is too small?

> 4. It scales roughly with database size; at 28,000 rows, it's quite
> noticeable, and at 84,000 it's painful.

Sounds like some inefficent O(n²) algorithm.

Could you show some example query?
What is the EXPLAIN QUERY PLAN output?


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

Re: [sqlite] Query Plan with FTS Tables

[Clemens Ladisch]

GB wrote:
> we are facing some Problems with Queries joining several FTS-Tables.

FTS tables cannot do efficient lookups except by docid or by MATCHing.

If you have to do joins with other tables, you typically set the docid
in the FTS table to some primary/foreign key in the other tables, and
then use the docids returned from some MATCH:

SELECT ... FROM ...
WHERE ... MyTable.SomeID IN (SELECT docid
 FROM FtsTable
 WHERE FtsTable MATCH 'something')


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

Re: [sqlite] query optimization with "order by" in a view

[Gabriel Corneanu]

I admit I didn't check what the standards say about "select", I just 
wanted to make sure the potential users (which are by no means 
"developers") get the data properly.
But you misread my example, I had "order by id" everywhere (no mixed 
sorting).
I expected that the optimizer would "see" it's the same order and avoid 
doing it twice.
Otherwise I can also admit that multiple/mixed "order by" would be 
problematic.


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

Re: [sqlite] SQLite 4

[Gabriel Corneanu]

The problem is, it is not ported to Windows as all...
I made a quick (and dirty) port of lsm_unix, but other things (e.g. the 
environment) are also missing.


Gabriel

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