Re: [sqlite] Using cache stats to balance transaction size for optimal performance

[Dan Kennedy]

On May 27, 2010, at 8:07 PM, Pavel Ivanov wrote:

>> That's true, except for the case when exclusive lock fails; at  
>> least that's
>> what Dan Kennedy says to my question from a couple of years ago:
>> http://old.nabble.com/changes-in-cache-spill-locking-since-3.5.9--td20564357.html#a20564357
>
> It's interesting feature, I didn't know about that. It would be also
> useful to know will acquiring of EXCLUSIVE lock be re-attempted after
> failure at some point during this transaction with indefinite cache
> growing? If yes then how often SQLite will retry?

It will retry the EXCLUSIVE lock each time a page that is not
in the cache is required by SQLite (a "cache-miss").

Since at this point SQLite has a PENDING lock on the database,
no new readers can connect. So once all existing readers have
finished, the writer will be able to upgrade to an EXCLUSIVE
and free memory by writing dirty pages to the db file. In other
words, the cache will not continue growing after the readers
have unlocked the database.

Dan.




> On Thu, May 27, 2010 at 8:48 AM, Igor Sereda  wrote:
>>
>> Pavel,
>>
>> Thanks for the reply! I was afraid using pcache would be the only  
>> way :)
>>
>> As for this:
>>
>> Pavel Ivanov-2 wrote:
>>>
>>> No way. Cache won't ever grow just because you have large  
>>> transaction.
>>> It will only be spilled to disk and exclusive lock will be taken but
>>> never trigger unbound growth.
>>>
>>
>> That's true, except for the case when exclusive lock fails; at  
>> least that's
>> what Dan Kennedy says to my question from a couple of years ago:
>> http://old.nabble.com/changes-in-cache-spill-locking-since-3.5.9--td20564357.html#a20564357
>>
>> I guess that's an undocumented feature.
>>
>> Cheers,
>> Igor
>> --
>> View this message in context: 
>> http://old.nabble.com/Using-cache-stats-to-balance-transaction-size-for-optimal-performance-tp28690967p28693594.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

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

Re: [sqlite] round documentation

[Matt Young]

OK, got it.  I was referring to the number of decimal points, but yes
round(x,0) does do something

On 5/27/10, Igor Tandetnik  wrote:
> Matt Young wrote:
>> Round(x,0) really doesn't exist, it simply does  round(x,1)
>
> select round(4.1, 0), round(4.1, 1);
> 4.04.1
>
> --
> Igor Tandetnik
>
> ___
> 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] round documentation

[Igor Tandetnik]

Matt Young wrote:
> Round(x,0) really doesn't exist, it simply does  round(x,1)

select round(4.1, 0), round(4.1, 1);
4.04.1

-- 
Igor Tandetnik

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

Re: [sqlite] round documentation

[Matt Young]

sqlite> select round(4.-.5);
4.0
sqlite> select round(4.-0);
4.0
sqlite> select round(4);
4.0
sqlite> select round(4,0);
4.0
sqlite> select round(4,1);
4.0
sqlite> select round(4,2);
4.0
sqlite> select round(4.666,2);
4.67
sqlite>


Round(x,0) really doesn't exist, it simply does  round(x,1)

On 5/27/10, Igor Tandetnik  wrote:
> Matt Young  wrote:
>> I second that documentation confusion.  There is no truncate to
>> integer, though I wish it would.
>
> Somewhat off-topic, but if you want truncation, this would do it: round(x -
> 0.5) . Well, it's more like floor(), it goes down rather than towards zero
> (which makes a difference when x is negative).
> --
> Igor Tandetnik
>
>
> ___
> 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] round documentation

[Igor Tandetnik]

Matt Young  wrote:
> I second that documentation confusion.  There is no truncate to
> integer, though I wish it would.

Somewhat off-topic, but if you want truncation, this would do it: round(x - 
0.5) . Well, it's more like floor(), it goes down rather than towards zero 
(which makes a difference when x is negative).
-- 
Igor Tandetnik


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

Re: [sqlite] round documentation

[Matt Young]

I second that documentation confusion.  There is no truncate to
integer, though I wish it would.

On 5/27/10, Wilson, Ronald  wrote:
> From http://www.sqlite.org/lang_corefunc.html
>
> "The round(X,Y) function returns a string representation of the
> floating-point value X rounded to Y digits to the right of the decimal
> point. If the Y argument is omitted, the X value is truncated to an
> integer."
>
> The documentation above is incorrect in the last clause.  The X value is not
> truncated it is rounded to an integer.  Also, it returns a real, not an
> integer or a string.
>
> SQLite version 3.6.22
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> select round(1.6);
> 2.0
> sqlite> select a, typeof(a) from (select round(1.6) as a);
> 2.0|real
> sqlite> select a, typeof(a) from (select round("1.6") as a);
> 2.0|real
> sqlite> select a, typeof(a) from (select round('1.6') as a);
> 2.0|real
> sqlite>
>
> Ron Wilson, Engineering Project Lead
> (o) 434.455.6453, (m) 434.851.1612, www.harris.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

[sqlite] UPDATE VIEW ... LIMIT 1; -- apparently no effect?

[Ralf Junker]

This is SQLite 3.6.23.1 compiled with SQLITE_ENABLE_UPDATE_DELETE_LIMIT 
(plus a few others, which should not matter to the problem).

The UPDATE ... LIMIT clause works fine when applied to tables, but 
suppresses any updates when applied to a view with an update trigger.

Here is some example SQL:

create table test (data,rownum integer);
insert into test values ('one',1);
insert into test values ('two',2);
create view vtest as select * from test;
create trigger Trig1 instead of update of data on vtest
   begin
 update test set data = new.data where rownum = new.rownum ;
   end;

-- No LIMIT - this works.
update vtest set data = 'yyy'; --works

-- LIMIT clause present - nothing is updated.
update vtest set data = 'zzz' limit 1;

It is unfortunately not possible to reproduce this with the reference 
binaries from sqlite.org since they are compiled without 
SQLITE_ENABLE_UPDATE_DELETE_LIMIT. Searching the timeline and previous 
list messages did not turn up any applicable results.

So here are my questions:

Can anybody confirm my findings?

If so, is this the expected behavior? Or should not UPDATE ... LIMIT on 
views work just like on tables?

Could this be a bug worth creating a ticket for?

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

Re: [sqlite] Problem with SQLite library.

[Pavel Ivanov]

> Error is " error C3861: 'sqlite_open': identifier not found".

Indeed there's no sqlite_open function. There's sqlite3_open. Does it
fixes the problem?


Pavel

On Thu, May 27, 2010 at 11:31 AM, lukasz aaa  wrote:
> Hello. Sorry for my English.
> I have a problem with the SQLite library reloaded correctly (use in
> project). I'm using VC++ 2010 and Dev.
> I add to project sqlite3.h, copy to folder with source sqlite3.dll and
> sqlite3.lib. I add sqlite3.lib to linker - i search information on
> forums, but can't compile program.
> I make file sqlite3.lib, with program lib.exe, after it not work do it
> with VC++ Create Library, don't work too.
> Error is " error C3861: 'sqlite_open': identifier not found".
> If you have time pleas help me.
>
>
> ___
> 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] network access problem

[Pavel Ivanov]

> a)  There are no other processes or connections accessing this db. There are 
> no journals or writing being done to any db across this or any connection.  
> Both in the virtual world and the host pc.

You didn't get Roger's words correctly. Although you don't have any
other processes or journals SQLite itself doesn't know that. So it
should always obtain a SHARED lock on database. Also while opening
database it should check if database has RESERVED lock on it (to
understand if it needs to rollback journal). As you can see in SQLite
sources all these operations result in some sequence of read and write
locks on some ranges of bytes inside database file. So apparently your
virtual box reacts on those locks differently than normal Windows
system would have and SQLite doesn't understand how to deal with that.


Pavel

On Thu, May 27, 2010 at 9:29 AM, Art  wrote:
> a)  There are no other processes or connections accessing this db. There are 
> no journals or writing being done to any db across this or any connection.  
> Both in the virtual world and the host pc.
> b) Running windows xp pro 32bit in VB.  Parent PC is windows 7 ultimate 64 
> bit.
> c) Yes, when the folder's permission in the vb settings were set to r/w for 
> the shared folder, it worked.  When set to read only it fails.
>
> Thank you,
> Art,
> ==
> From: "Black,
> Michael (IS)" 
>
> One thing to be clear on.
> What OS are you running in your Virtualbox?  I hope it's not
> unix-flavored as that could/would be your problem.
>
> Also...you
> didn't say that read/write worked over the UNC path...only that
> read-only failed.
> Michael D. Black
> ---
> From: Roger Binns 
> Just because
> you are opening the database read only does not mean that other
> connections in the same or different processes are.  Consequently it is
> still
> necessary to do locking.  Additionally if a journal exists then it may
> need to be rolled back which requires writing to the database and deleting
> the journal, the latter also requiring write permissions to the directory.
>
> Roger
> --
> From: Art 
> Thanks for the suggestions:
>
> Sqlite Manager also
> fails.
>
> This is a permission issue with the sql open api.  This
> is a "Shared Folder" under VirtualBox with "read only permission".  If
> the folder is tagged as full access then sql open works.
>
> There
> should be no reason that the openv2 statement with read_only specified
> should  fail under these circumstances,
>
> If this was a problem
> with virtualbox then you see their message board light up with complaints.  
> Its not, no other apps have problems
> accessing files through vb shared folders!
>
> Art.
>
> 
>
> Running in sqlite
> application in Virtual Box, attempt to open a database with sqlite from a 
> shared network folder \\Vboxsvr\testdata  however the open16 and openv2 (with 
> read only)  both fail --- rc = 14.  App opens file if moved locally to hard 
> drive.
>
> using latest version of sqlite3 3.6.23.1
>
> this seems to be a bug in
> the open code of sqlite when accessing a file across a network.
>
> Thank you,
> Art Zerger
> azer...@yahoo.com
> ===
> From: "Griggs, Donald" 
> Hi Art,
> I just succeeded in opening a db with a UNC path, under Windows XP Pro, using
> the same version (3.6.23.1) with the command-line utility -- but I do
> NOT use virtual box.
> Does the command-line utility fail for you under virtual box?
> Might you attempt it without virtualbox?
>
> Donald
> --
> From: Kees Nuyt 
> A VBox shared folder is not exactly the best example of
> "sharing over a network". The purpose of shared folders is
> to copy files to and from virtual machines in an easy way.
> I think the locking primitives of
> VBox shared folders are
> not 100%.  You could try again with NFSv4.
> --
>  (  Kees Nuyt
>  )
> c[_]
> --
> From: Jean-Christophe Deschamps 
> I don't believe so: I use several bases which I open with MS short UNC
> syntax 200 times a day without any problem ever (using vanilla 3.6.23.1).
>
> Your
> issue is elsewhere.  Make sure the account you use has enough
> rights to the destination folder and check you can
> access it with, for
> instance, a innocent application like a hex
> editor or a third-party
> SQLite manager.
> ===
>
>
>
> ___
> 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] Problem with SQLite library.

[lukasz aaa]

Hello. Sorry for my English.
I have a problem with the SQLite library reloaded correctly (use in 
project). I'm using VC++ 2010 and Dev.
I add to project sqlite3.h, copy to folder with source sqlite3.dll and 
sqlite3.lib. I add sqlite3.lib to linker - i search information on 
forums, but can't compile program.
I make file sqlite3.lib, with program lib.exe, after it not work do it 
with VC++ Create Library, don't work too.
Error is " error C3861: 'sqlite_open': identifier not found".
If you have time pleas help me.


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

[sqlite] small bug using like/glob without wildcard?

[ge...@iol.it]

Dear all,
I'm looking to resolve a small problem using like/glob function with indexes 
starting from version 3.6.23.1 (It worked with 3.6.13)

Using a where condition such as  "WHERE MY_INDEXED_COLUMN LIKE 'X' " ,sqlite 
doesn't use the index on that column while it uses the index if I use a 
wildcard, for example "WHERE MY_INDEXED_COLUMN LIKE 'X%'".

This causes some problems in my application which uses prepared statements 
that accept string input by users, and it's expected to work with or without 
wildcard.
Looking into sqlite source code, in file "where.c", function "static int 
isLikeOrGlob", if I replace the following lines:

1)  line number 690: 

if( cnt!=0 && c!=0 && 255!=(u8)z[cnt-1] ){

with this:

if( cnt!=0 && 255!=(u8)z[cnt-1] ){

2) line number 692:

*pisComplete = z[cnt]==wc[0] && z[cnt+1]==0;

with this:

*pisComplete = z[cnt]==wc[0] && c==0;


It seems to work without problems in both cases.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] round documentation

[Wilson, Ronald]

>From http://www.sqlite.org/lang_corefunc.html

"The round(X,Y) function returns a string representation of the floating-point 
value X rounded to Y digits to the right of the decimal point. If the Y 
argument is omitted, the X value is truncated to an integer."

The documentation above is incorrect in the last clause.  The X value is not 
truncated it is rounded to an integer.  Also, it returns a real, not an integer 
or a string.

SQLite version 3.6.22
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> select round(1.6);
2.0
sqlite> select a, typeof(a) from (select round(1.6) as a);
2.0|real
sqlite> select a, typeof(a) from (select round("1.6") as a);
2.0|real
sqlite> select a, typeof(a) from (select round('1.6') as a);
2.0|real
sqlite>

Ron Wilson, Engineering Project Lead
(o) 434.455.6453, (m) 434.851.1612, www.harris.com

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

Re: [sqlite] network access problem

[Art]

a)  There are no other processes or connections accessing this db. There are no 
journals or writing being done to any db across this or any connection.  Both 
in the virtual world and the host pc.
b) Running windows xp pro 32bit in VB.  Parent PC is windows 7 ultimate 64 bit. 
 
c) Yes, when the folder's permission in the vb settings were set to r/w for the 
shared folder, it worked.  When set to read only it fails.

Thank you,
Art,
==
From: "Black, 
Michael (IS)" 

One thing to be clear on.   
What OS are you running in your Virtualbox?  I hope it's not 
unix-flavored as that could/would be your problem.

Also...you 
didn't say that read/write worked over the UNC path...only that 
read-only failed.
Michael D. Black
---
From: Roger Binns 
Just because 
you are opening the database read only does not mean that other
connections in the same or different processes are.  Consequently it is
still 
necessary to do locking.  Additionally if a journal exists then it may
need to be rolled back which requires writing to the database and deleting
the journal, the latter also requiring write permissions to the directory.

Roger
--
From: Art 
Thanks for the suggestions:

Sqlite Manager also 
fails.  

This is a permission issue with the sql open api.  This 
is a "Shared Folder" under VirtualBox with "read only permission".  If 
the folder is tagged as full access then sql open works.  

There 
should be no reason that the openv2 statement with read_only specified 
should  fail under these circumstances,  

If this was a problem 
with virtualbox then you see their message board light up with complaints.  Its 
not, no other apps have problems 
accessing files through vb shared folders!

Art.



Running in sqlite 
application in Virtual Box, attempt to open a database with sqlite from a 
shared network folder \\Vboxsvr\testdata  however the open16 and openv2 (with 
read only)  both fail --- rc = 14.  App opens file if moved locally to hard 
drive.

using latest version of sqlite3 3.6.23.1 

this seems to be a bug in 
the open code of sqlite when accessing a file across a network.

Thank you,
Art Zerger
azer...@yahoo.com
===
From: "Griggs, Donald" 
Hi Art,
I just succeeded in opening a db with a UNC path, under Windows XP Pro, using 
the same version (3.6.23.1) with the command-line utility -- but I do 
NOT use virtual box.
Does the command-line utility fail for you under virtual box?  
Might you attempt it without virtualbox?  

Donald
--
From: Kees Nuyt 
A VBox shared folder is not exactly the best example of
"sharing over a network". The purpose of shared folders is
to copy files to and from virtual machines in an easy way.
I think the locking primitives of 
VBox shared folders are
not 100%.  You could try again with NFSv4.
-- 
  (  Kees Nuyt
  )
c[_]
--
From: Jean-Christophe Deschamps 
I don't believe so: I use several bases which I open with MS short UNC 
syntax 200 times a day without any problem ever (using vanilla 3.6.23.1).

Your 
issue is elsewhere.  Make sure the account you use has enough 
rights to the destination folder and check you can 
access it with, for 
instance, a innocent application like a hex 
editor or a third-party 
SQLite manager.
===


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

Re: [sqlite] Aggregate and join query very slow

[Max Vlasov]

On Thu, May 27, 2010 at 3:07 PM, Michael Ash  wrote:

> ...These are large tables (52,355 records in facility and 4,085,137 in
> release_cl).
>
> ...
> sqlite> explain query plan
>   ...> SELECT name,score
>   ...> FROM facility f, (SELECT facilitynumber,SUM(score_rev) AS score
>   ...> FROM release_cl
>   ...> WHERE media<3
>   ...> AND year=2006
>   ...> GROUP BY facilitynumber) r
>


Michael, from what I see, if your release_cl table is not properly indexed
to be quickly aggregated (media and year field), this will lead to full
table reading (so all the data of your 4M records). If it's properly
indexed, and the result number of records of this select is big, consider
adding non-indexed fields to this (or brand-new) index since otherwise
sqlite quickly finds records with this index, but have to look up main data
tree to retrieve other fields.

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

Re: [sqlite] Using cache stats to balance transaction size for optimal performance

[Pavel Ivanov]

> That's true, except for the case when exclusive lock fails; at least that's
> what Dan Kennedy says to my question from a couple of years ago:
> http://old.nabble.com/changes-in-cache-spill-locking-since-3.5.9--td20564357.html#a20564357

It's interesting feature, I didn't know about that. It would be also
useful to know will acquiring of EXCLUSIVE lock be re-attempted after
failure at some point during this transaction with indefinite cache
growing? If yes then how often SQLite will retry? Hopefully Dan reads
this and can answer those questions.


Pavel

On Thu, May 27, 2010 at 8:48 AM, Igor Sereda  wrote:
>
> Pavel,
>
> Thanks for the reply! I was afraid using pcache would be the only way :)
>
> As for this:
>
> Pavel Ivanov-2 wrote:
>>
>> No way. Cache won't ever grow just because you have large transaction.
>> It will only be spilled to disk and exclusive lock will be taken but
>> never trigger unbound growth.
>>
>
> That's true, except for the case when exclusive lock fails; at least that's
> what Dan Kennedy says to my question from a couple of years ago:
> http://old.nabble.com/changes-in-cache-spill-locking-since-3.5.9--td20564357.html#a20564357
>
> I guess that's an undocumented feature.
>
> Cheers,
> Igor
> --
> View this message in context: 
> http://old.nabble.com/Using-cache-stats-to-balance-transaction-size-for-optimal-performance-tp28690967p28693594.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] Using cache stats to balance transaction size for optimal performance

[Igor Sereda]

Pavel, 

Thanks for the reply! I was afraid using pcache would be the only way :) 

As for this:

Pavel Ivanov-2 wrote:
> 
> No way. Cache won't ever grow just because you have large transaction.
> It will only be spilled to disk and exclusive lock will be taken but
> never trigger unbound growth.
> 

That's true, except for the case when exclusive lock fails; at least that's
what Dan Kennedy says to my question from a couple of years ago:
http://old.nabble.com/changes-in-cache-spill-locking-since-3.5.9--td20564357.html#a20564357

I guess that's an undocumented feature.

Cheers,
Igor
-- 
View this message in context: 
http://old.nabble.com/Using-cache-stats-to-balance-transaction-size-for-optimal-performance-tp28690967p28693594.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] Using cache stats to balance transaction size for optimal performance

[Pavel Ivanov]

> And when the amount of data changed in a single transaction is large enough,
> it would cause either cache spill and exclusive lock on the database, or the
> growth of cache and memory consumption.

No way. Cache won't ever grow just because you have large transaction.
It will only be spilled to disk and exclusive lock will be taken but
never trigger unbound growth.

There's nothing in SQLite's external interface that could tell you how
much of its cache is dirty at the moment and requires writing to the
media at the end of transaction. But you can probably generate that
information by yourself. You need to create your own implementation of
SQLite's cache (see http://www.sqlite.org/c3ref/pcache_methods.html).
Then calculate how many pages you have in the cache in total and how
many pages are pinned using xFetch function and are not yet unpinned
using xUnpin method. Dividing second number by first should give you a
good estimate of how many pages are dirty.

This is solely a mental speculation - I didn't try it by myself and I
don't know SQLite's internals well enough to guarantee you that it
will work as described. But from what I know it should work.


Pavel

On Thu, May 27, 2010 at 7:29 AM, Igor Sereda  wrote:
>
> Michael,
>
> Thank you for your suggestion! The problem with this approach is that N
> would not be a constant that we could tune.
>
> As I mentioned, the amount of updates may vary, depending on the data
> received.
>
> For example, one piece of data may lead to a single INSERT. So it would be
> safe and effective to have N=1000, for example. Another piece of data may
> lead to 1000 INSERTs. Now, if we still have N=1000, then we'll have
> 1,000,000 INSERTs in a single transaction. It's completely unpredictable.
> And when the amount of data changed in a single transaction is large enough,
> it would cause either cache spill and exclusive lock on the database, or the
> growth of cache and memory consumption.
>
> Do you think this makes sense?
>
> We could theoretically count the number of DML statements or steps, but this
> would imply changing the underlying architecture of the application, so that
> any plug-in or extension that accesses SQLite also reports how much data did
> they change. It's not very convenient.
>
> Kind regards,
> Igor
>
>
> Black, Michael (IS) wrote:
>>
>> So only do N many records in one batch.  That's the easiest thing.  Forget
>> about the cache and just use responsiveness to adjust how many records you
>> allow at once.
>>
>> Pseudo-code:
>> recnum=0
>> BEGIN;
>> while more records
>>     INSERT
>>     recnum++
>>     if (recnum % 1000)
>>         COMMIT;
>>         BEGIN;
>> end
>> COMMIT;
>>
>
> --
> View this message in context: 
> http://old.nabble.com/Using-cache-stats-to-balance-transaction-size-for-optimal-performance-tp28690967p28692687.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] Aggregate and join query very slow

[Black, Michael (IS)]

MySql has a much larger default cache than sqlite3.  That could be one rather 
large difference in performance.
 
Try increasing sqlite3 cache from it's default of 2000k
PRAGMA cache_size=10;
 
Or more...
 
Also...no indexes on media or year?  And what does MySql's explain say?
 
Michael D. Black
Senior Scientist
Northrop Grumman Mission Systems
 



From: sqlite-users-boun...@sqlite.org on behalf of Michael Ash
Sent: Thu 5/27/2010 6:07 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] Aggregate and join query very slow



I am new to sqlite3, converting from mysql.

A query that  involves an aggregate function and a join is running
very slowly (taking about 15 seconds compared to mysql where it runs
in <1 second).  I've tried two variants of the query (each reprinted
below with the explain query plan), and both are roughly equally slow.
 These are large tables (52,355 records in facility and 4,085,137 in
release_cl).

Please let me know if you have advice; I am clearly not getting it.



sqlite> explain query plan
   ...> SELECT name,score
   ...> FROM facility f, (SELECT facilitynumber,SUM(score_rev) AS score
   ...> FROM release_cl
   ...> WHERE media<3
   ...> AND year=2006
   ...> GROUP BY facilitynumber) r
   ...> WHERE f.facilitynumber=r.facilitynumber
   ...> ORDER BY score DESC
   ...> LIMIT 10 ;
0|0|TABLE release_cl WITH INDEX facilitynumberRC ORDER BY
0|1|TABLE  AS r
1|0|TABLE facility AS f WITH INDEX FacilityNumberF


sqlite> explain query plan
   ...> SELECT name,city,state,SUM(score_rev) AS score
   ...> FROM release_cl r,facility f
   ...> WHERE r.facilitynumber=f.facilitynumber
   ...> AND media<3
   ...> AND year=2006
   ...> GROUP BY r.facilitynumber
   ...> ORDER BY score DESC
   ...> LIMIT 10 ;
0|0|TABLE release_cl AS r WITH INDEX facilitynumberRC ORDER BY
1|1|TABLE facility AS f WITH INDEX FacilityNumberF






--
Michael Ash, Associate Professor
  of Economics and Public Policy
Department of Economics and CPPA
University of Massachusetts
Amherst, MA 01003
Email m...@econs.umass.edu
Fax +1-413-545-2921
http://people.umass.edu/maash
___
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] Using cache stats to balance transaction size for optimal performance

[Black, Michael (IS)]

Maybe I'm confused but perhaps you don't understand the pseudo code or I don't 
understand your problem.  The idea is to process in batches instead of 
all-at-once.  I don't see where you came up with 1,000,000 inserts as though 
the pseudo-code is generating extra insert statements for you.  If you made N=1 
you would effectively be the same as running without BEGIN/COMMIT.
 
1-to-999 insert records with N=1000 would result in one BEGIN/COMMIT sequence.  
 
1000 INSERTS woud result in two BEGIN/COMMIT sequences (the 2nd BEGIN/COMMIT 
would actually have 0 records to commit as you would be right on the boundary)
2000 INSERTs would have 3 BEGIN/COMMIT
3000 INSERTs would have 4 BEGIN/COMMIT
 
It doesn't matter how many inserts you do...the pseudo-code logic just does a 
new COMMIT/BEGIN on every 1000 inserts.  You adjust N to make the system 
responsive to your users -- the smaller the N the faster the user response time 
for competing queries.
 
Am I missing something?  Do you not know what BEGIN/COMMIT is?
Let me rephase using your pseudocode --- this will commit every 1000 INSERTs.
 
count=0
batch=1000
BEGIN
while (have data) {
count++
if ((count % batch)==0) {
   COMMIT
   BEGIN
}
process next piece of data
}
}
COMMIT

 
Michael D. Black
Senior Scientist
Northrop Grumman Mission Systems
 



From: sqlite-users-boun...@sqlite.org on behalf of Igor Sereda
Sent: Thu 5/27/2010 6:29 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Using cache stats to balance transaction size for optimal 
performance




Michael,

Thank you for your suggestion! The problem with this approach is that N
would not be a constant that we could tune.

As I mentioned, the amount of updates may vary, depending on the data
received.

For example, one piece of data may lead to a single INSERT. So it would be
safe and effective to have N=1000, for example. Another piece of data may
lead to 1000 INSERTs. Now, if we still have N=1000, then we'll have
1,000,000 INSERTs in a single transaction. It's completely unpredictable.
And when the amount of data changed in a single transaction is large enough,
it would cause either cache spill and exclusive lock on the database, or the
growth of cache and memory consumption.

Do you think this makes sense?

We could theoretically count the number of DML statements or steps, but this
would imply changing the underlying architecture of the application, so that
any plug-in or extension that accesses SQLite also reports how much data did
they change. It's not very convenient.

Kind regards,
Igor


Black, Michael (IS) wrote:
>
> So only do N many records in one batch.  That's the easiest thing.  Forget
> about the cache and just use responsiveness to adjust how many records you
> allow at once.
> 
> Pseudo-code:
> recnum=0
> BEGIN;
> while more records
> INSERT
> recnum++
> if (recnum % 1000)
> COMMIT;
> BEGIN;
> end
> COMMIT;
>

--
View this message in context: 
http://old.nabble.com/Using-cache-stats-to-balance-transaction-size-for-optimal-performance-tp28690967p28692687.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] Using cache stats to balance transaction size for optimal performance

[Igor Sereda]

Michael,

Thank you for your suggestion! The problem with this approach is that N
would not be a constant that we could tune.

As I mentioned, the amount of updates may vary, depending on the data
received. 

For example, one piece of data may lead to a single INSERT. So it would be
safe and effective to have N=1000, for example. Another piece of data may
lead to 1000 INSERTs. Now, if we still have N=1000, then we'll have
1,000,000 INSERTs in a single transaction. It's completely unpredictable.
And when the amount of data changed in a single transaction is large enough,
it would cause either cache spill and exclusive lock on the database, or the
growth of cache and memory consumption.

Do you think this makes sense?

We could theoretically count the number of DML statements or steps, but this
would imply changing the underlying architecture of the application, so that
any plug-in or extension that accesses SQLite also reports how much data did
they change. It's not very convenient.

Kind regards,
Igor


Black, Michael (IS) wrote:
> 
> So only do N many records in one batch.  That's the easiest thing.  Forget
> about the cache and just use responsiveness to adjust how many records you
> allow at once.
>  
> Pseudo-code:
> recnum=0
> BEGIN;
> while more records
> INSERT
> recnum++
> if (recnum % 1000) 
> COMMIT;
> BEGIN;
> end
> COMMIT;
> 

-- 
View this message in context: 
http://old.nabble.com/Using-cache-stats-to-balance-transaction-size-for-optimal-performance-tp28690967p28692687.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] Aggregate and join query very slow

[Michael Ash]

I am new to sqlite3, converting from mysql.

A query that  involves an aggregate function and a join is running
very slowly (taking about 15 seconds compared to mysql where it runs
in <1 second).  I've tried two variants of the query (each reprinted
below with the explain query plan), and both are roughly equally slow.
 These are large tables (52,355 records in facility and 4,085,137 in
release_cl).

Please let me know if you have advice; I am clearly not getting it.



sqlite> explain query plan
   ...> SELECT name,score
   ...> FROM facility f, (SELECT facilitynumber,SUM(score_rev) AS score
   ...> FROM release_cl
   ...> WHERE media<3
   ...> AND year=2006
   ...> GROUP BY facilitynumber) r
   ...> WHERE f.facilitynumber=r.facilitynumber
   ...> ORDER BY score DESC
   ...> LIMIT 10 ;
0|0|TABLE release_cl WITH INDEX facilitynumberRC ORDER BY
0|1|TABLE  AS r
1|0|TABLE facility AS f WITH INDEX FacilityNumberF


sqlite> explain query plan
   ...> SELECT name,city,state,SUM(score_rev) AS score
   ...> FROM release_cl r,facility f
   ...> WHERE r.facilitynumber=f.facilitynumber
   ...> AND media<3
   ...> AND year=2006
   ...> GROUP BY r.facilitynumber
   ...> ORDER BY score DESC
   ...> LIMIT 10 ;
0|0|TABLE release_cl AS r WITH INDEX facilitynumberRC ORDER BY
1|1|TABLE facility AS f WITH INDEX FacilityNumberF






-- 
Michael Ash, Associate Professor
  of Economics and Public Policy
Department of Economics and CPPA
University of Massachusetts
Amherst, MA 01003
Email m...@econs.umass.edu
Fax +1-413-545-2921
http://people.umass.edu/maash
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] How to get a constraint name

[Дамян Богоев]

Hi,

I want find out how to get a foreign key constraint name stored in a sqlite
database. Could you assist me with this problem?
 Thank you in advance.
*Damyan Bogoev*
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Using cache stats to balance transaction size for optimal performance

[Black, Michael (IS)]

So only do N many records in one batch.  That's the easiest thing.  Forget 
about the cache and just use responsiveness to adjust how many records you 
allow at once.
 
Pseudo-code:
recnum=0
BEGIN;
while more records
INSERT
recnum++
if (recnum % 1000) 
COMMIT;
BEGIN;
end
COMMIT;
 
 
Cache will grow to a maximum size and not use any more memory after that.  
Doesn't matter what you do I don't think other than to adjust the maximum size 
of it.
 
Michael D. Black
Senior Scientist
Northrop Grumman Mission Systems
 



From: sqlite-users-boun...@sqlite.org on behalf of Igor Sereda
Sent: Thu 5/27/2010 3:27 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] Using cache stats to balance transaction size for optimal 
performance




I would like each transaction to be as large as possible, but not too large
to cause cache growth or cache spill.

We have a stream of incoming data, with each piece of data causing updates
in SQLite database. The number of rows inserted/updated for each data record
may vary.

If I enclose each data record's processing in a separate transaction, there
will be too many transactions -- it would be slow. If I enclose too many
records processing in a single transaction, the cache may grow or spill to
disk -- not wanted either.

It would be great if we could dynamically assess how much of the cache is
taken up by the transaction. Then, I would issue COMMIT as soon as cache use
is over some threshold, like 50%.

Pseudocode:

while (have data) {
BEGIN
while (have data && CACHE USE < 50%) {
process next piece of data
}
COMMIT
}

Is this possible? Any other best practices for optimizing transaction size?

Thanks!
Igor


--
View this message in context: 
http://old.nabble.com/Using-cache-stats-to-balance-transaction-size-for-optimal-performance-tp28690967p28690967.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] network access problem

[Black, Michael (IS)]

One thing to be clear on.   What OS are you running in your Virtualbox?  I hope 
it's not unix-flavored as that could/would be your problem.
 
Also...you didn't say that read/write worked over the UNC path...only that 
read-only failed.
 
Michael D. Black
Senior Scientist
Northrop Grumman Mission Systems
 



From: sqlite-users-boun...@sqlite.org on behalf of Art
Sent: Wed 5/26/2010 10:45 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] network access problem



Thanks for the suggestions:


Sqlite Manager also fails. 

This is a permission issue with the sql open api.  This is a "Shared Folder" 
under VirtualBox with "read only permission".  If the folder is tagged as full 
access then sql open works. 

There should be no reason that the openv2 statement with read_only specified 
should  fail under these circumstances, 

If this was a problem with virtualbox then you see their message board light up 
with complaints.  Its not, no other apps have problems accessing files through 
vb shared folders!

Art.



Running in sqlite application in Virtual Box, attempt to open a database with 
sqlite from a shared network folder \\Vboxsvr\testdata  however the open16 and 
openv2 (with read only)  both fail --- rc = 14.  App opens file if moved 
locally to hard drive.

using latest version of sqlite3 3.6.23.1

this seems to be a bug in the open code of sqlite when accessing a file across 
a network.

Thank you,
Art Zerger
azer...@yahoo.com
===
From: "Griggs, Donald" 
Hi Art,
I just succeeded in opening a db with a UNC path, under Windows XP Pro, using 
the same version (3.6.23.1) with the command-line utility -- but I do NOT use 
virtual box.
Does the command-line utility fail for you under virtual box? 
Might you attempt it without virtualbox? 

Donald
--
From: Kees Nuyt 
A VBox shared folder is not exactly the best example of
"sharing over a network". The purpose of shared folders is
to copy files to and from virtual machines in an easy way.
I think the locking primitives of VBox shared folders are
not 100%.  You could try again with NFSv4.
--
  (  Kees Nuyt
  )
c[_]
--
From: Jean-Christophe Deschamps 
I don't believe so: I use several bases which I open with MS short UNC
syntax 200 times a day without any problem ever (using vanilla 3.6.23.1).

Your issue is elsewhere.  Make sure the account you use has enough
rights to the destination folder and check you can access it with, for
instance, a innocent application like a hex editor or a third-party
SQLite manager.
===


 
___
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] Using cache stats to balance transaction size for optimal performance

[Igor Sereda]

I would like each transaction to be as large as possible, but not too large
to cause cache growth or cache spill.

We have a stream of incoming data, with each piece of data causing updates
in SQLite database. The number of rows inserted/updated for each data record
may vary.

If I enclose each data record's processing in a separate transaction, there
will be too many transactions -- it would be slow. If I enclose too many
records processing in a single transaction, the cache may grow or spill to
disk -- not wanted either.

It would be great if we could dynamically assess how much of the cache is
taken up by the transaction. Then, I would issue COMMIT as soon as cache use
is over some threshold, like 50%.

Pseudocode:

while (have data) {
BEGIN
while (have data && CACHE USE < 50%) {
process next piece of data
}
COMMIT
}

Is this possible? Any other best practices for optimizing transaction size?

Thanks!
Igor


-- 
View this message in context: 
http://old.nabble.com/Using-cache-stats-to-balance-transaction-size-for-optimal-performance-tp28690967p28690967.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