Re: [sqlite] sqlite3 last insert rowid

[Hick Gunter]

No. It returns the value of the (hidden) column rowid.

-Ursprüngliche Nachricht-
Von: Ashok Pitambar [mailto:ashokpitam...@gmail.com]
Gesendet: Dienstag, 26. Februar 2013 08:51
An: sqlite-users@sqlite.org
Betreff: [sqlite] sqlite3 last insert rowid

Hi All,

Does function sqlite3_last_insert_rowid returns the composite key  if created 
with 2 columns?

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


--
 Gunter Hick
Software Engineer
Scientific Games International GmbH
Klitschgasse 2 – 4, A - 1130 Vienna, Austria
FN 157284 a, HG Wien
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This e-mail is confidential and may well also be legally privileged. If you 
have received it in error, you are on notice as to its status and accordingly 
please notify us immediately by reply e-mail and then delete this message from 
your system. Please do not copy it or use it for any purposes, or disclose its 
contents to any person as to do so could be a breach of confidence. Thank you 
for your cooperation.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] sqlite3 last insert rowid

[Ashok Pitambar]

Hi All,

Does function sqlite3_last_insert_rowid returns the composite key  if
created with 2 columns?

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

Re: [sqlite] like query

[dd]

   >>SELECT * FROM emp WHERE column_test BETWEEN "somedata/" AND
"somedata/zzz"

   This database has unicode strings(chinese/japanese/...etc strings). can
you tell me which is the correct character to replace with z?




On Mon, Feb 25, 2013 at 8:13 PM, Simon Slavin  wrote:

>
> On 25 Feb 2013, at 2:46pm, dd  wrote:
>
> >  Table has string data type column. format of strings:
> > somedata1/somedata2/somedata3
> >
> >  I have written query to search : select * from emp where column_test
> like
> > "somedata/%";
> >
> >  It gives perfomance as per articles in internet. Is it? If yes, what is
> > alternate query for this?
>
> If the format of your 'LIKE' clause is always that you have fixed text at
> the beginning, then you can speed up your search a lot.  Create an index on
> the 'column_test' field, and use this query:
>
> SELECT * FROM emp WHERE column_test BETWEEN "somedata/" AND "somedata/zzz"
>
> replace 'zzz' with '~~~' or something similar if you're being really fussy.
>
> 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] like query

[dd]

Thanks Richard.


On Mon, Feb 25, 2013 at 6:54 PM, Richard Hipp  wrote:

> On Mon, Feb 25, 2013 at 9:46 AM, dd  wrote:
>
> > Hi,
> >
> >   Table has string data type column. format of strings:
> > somedata1/somedata2/somedata3
> >
> >   I have written query to search : select * from emp where column_test
> like
> > "somedata/%";
> >
> >   It gives perfomance as per articles in internet. Is it? If yes, what is
> > alternate query for this?
> >
>
> The query might go faster if you do:
>
> CREATE INDEX emp_idx1 ON emp(column_test COLLATE nocase);
>
> Or, if you really intended to do a case-sensitive search, you could say:
>
> SELECT * FROM emp WHERE column_test GLOB 'somedata/*';
>
>
>
>
>
> >
> >   Thanks in advance.
> >
> > Best Regards,
> > dd.
> > ___
> > 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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] locked database returning SQLITE_IOERR, not SQLITE_BUSY

[Greg Janée]

Have you set a SQLite timeout of a few seconds ?  See here:




I was expecting to get SQLITE_BUSY and nothing else.


If the _BUSY state lasts for longer than the timeout you've set,  
then you'll get _IOERR.  Setting a long timeout gives the software  
longer to deal with the busy condition quietly before concluding  
that something has locked up the databases longer than reasonable.


This explanation doesn't fit for a number of reasons.

- I've observed this problem using both the native Python sqlite3  
library and apsw, and the native library sets a default timeout of 5s,  
and there's nothing that takes remotely that long in my example.


- The documentation referenced above says that SQLITE_BUSY will be  
returned if the timeout is exceeded, and I do in fact occasionally  
receive such return codes (when using apsw, which has no default  
timeout).  The condition under which SQLITE_IOERR_BLOCKED might be  
returned (I'm referring to  here) would seem applicable only to the process trying to commit,  
but as my log showed, it's the process trying to begin a transaction  
that is receiving the error code.


- In any case, I'm receiving SQLITE_IOERR_LOCK, not  
SQLITE_IOERR_BLOCKED.


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

Re: [sqlite] SQLite equivalent to Oracle's MERGE INTO

[Petite Abeille]

On Feb 25, 2013, at 11:54 PM, anydacdev anydacdev  wrote:

> I was wondering what is SQLite's equivalent to:
> 
> MERGE INTO x TGT

There is none. Even though it's standard SQL (SQL:2003 or such), this is not 
supported by SQLite in any way, shape, or form.

One could make do with a loop of  'insert or ignore' + 'update'. But that's 
about it. Rather limited and cumbersome. Sigh…

If there was just *one* statement I wish SQLite supported, MERGE would be it. 

N.B. 

Be wary of 'insert or replace'  as it's really the equivalent of 'delete' + 
'insert'… usually not what one wants…

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

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

Re: [sqlite] SQLite equivalent to Oracle's MERGE INTO

[Igor Tandetnik]

On 2/25/2013 5:54 PM, anydacdev anydacdev wrote:

I was wondering what is SQLite's equivalent to:

MERGE INTO x TGT
USING (SELECT NAME, KEY FROM y) SRC
ON (TGT.key = SRC.key)
WHEN MATCHED THEN
   UPDATE SET TGT.NAME = NAME
WHEN NOT MATCHED THEN
   INSERT (TGT.NAME) VALUES (SRC.NAME)


If x.key happens to be unique (through PRIMARY KEY or UNIQUE constraint, 
or a UNIQUE index), then you can do INSERT OR REPLACE 
(http://sqlite.org/lang_conflict.html)


insert or replace into x(name, key)
select name, key from y;

If x has other fields besides name and key, and you want to preserve 
them on update path, then it becomes a bit trickier:


insert or replace into x(name, key, otherField)
select name, key, otherField from y left join x on (y.key = x.key);

If x.key is not unique, then there's nothing better in SQLite than 
running INSERT and UPDATE statements separately.

--
Igor Tandetnik

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

[sqlite] SQLite equivalent to Oracle's MERGE INTO

[anydacdev anydacdev]

I was wondering what is SQLite's equivalent to:

MERGE INTO x TGT
USING (SELECT NAME, KEY FROM y) SRC
ON (TGT.key = SRC.key)
WHEN MATCHED THEN
  UPDATE SET TGT.NAME = NAME
WHEN NOT MATCHED THEN
  INSERT (TGT.NAME) VALUES (SRC.NAME)

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

Re: [sqlite] locked database returning SQLITE_IOERR, not SQLITE_BUSY

[Simon Slavin]

On 25 Feb 2013, at 10:22pm, Greg Janée  wrote:

> I'm accessing an SQLite database from two processes simultaneously.  If 
> there's contention, one process will receive an SQLITE_BUSY; that's fine.  
> However, occasionally a process will receive an SQLITE_IOERR with the 
> extended result code SQLITE_IOERR_LOCK.

Have you set a SQLite timeout of a few seconds ?  See here:



> I was expecting to get SQLITE_BUSY and nothing else.

If the _BUSY state lasts for longer than the timeout you've set, then you'll 
get _IOERR.  Setting a long timeout gives the software longer to deal with the 
busy condition quietly before concluding that something has locked up the 
databases longer than reasonable.

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

Re: [sqlite] [SQLite.ADO.Net] Upgrading XP to SQLite version?

[Simon Slavin]

On 25 Feb 2013, at 2:18pm, Gilles Ganault  wrote:

>   I'm running the 32-bit version of XPSP3, whose "Add and Remove
> Programs" show that I have "QQLite ADO.NET 2*0/3.5 Provider 1.065.0"
> from Phoenix Software Solutions, LLC (http://sqlite.phxsoftware.com).
> 
> Now that SQLite.Ado.Net is handled by www.slite.org, what is the right
> way to upgrade to the latest and greatest? 

There is no particular version of SQLite installed on your computer, and you 
don't have to worry about downloading the latest version of SQLite.  Each 
program which uses SQLite might be using a different version of SQLite, and 
there is no real problem with this.  The version of SQLite.Ado.Net the program 
uses matters to you only if you are the programmer.  If you are a programmer 
you use whatever library is useful for your development platform when you 
compile your program.

If you are not a programmer then changing something about SQLite will do you no 
good.  If you can use an existing program use it.  If you can't you need a 
whole new program and it will do no good just to download a new SQLite 
component without changing the rest of the program.

So my guess is that at some point you have installed some program which uses 
the version of SQLite.Ado.Net you quote.  That would be the only reason to have 
it.  And the only people who need to worry about it are the programmers who 
wrote that program.

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

[sqlite] locked database returning SQLITE_IOERR, not SQLITE_BUSY

[Greg Janée]

I'm accessing an SQLite database from two processes simultaneously.   
If there's contention, one process will receive an SQLITE_BUSY; that's  
fine.  However, occasionally a process will receive an SQLITE_IOERR  
with the extended result code SQLITE_IOERR_LOCK.  This seems to occur  
if the other process is in the middle of committing (at least, that's  
the only time I've been able to observe it).  Is this reasonable  
behavior?  I was expecting to get SQLITE_BUSY and nothing else.   
(SQLite 3.7.0.1 on Solaris 10; database on local filesystem.)


Here's a log showing the serial actions of two processes, 9157 and 9096:

9157: opening cursor
9157: got cursor
9157: issuing begin immediate
9157: begun
9157: issuing delete
9157: deleted
9157: issuing commit
9096: opening cursor
9096: got cursor
9096: issuing begin immediate
9096: exception: IOError: disk I/O error, errcode=10, extended=3850
9096: closing cursor
9096: closed
9157: committed
9157: closing cursor
9157: closed

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

Re: [sqlite] [SQLite.ADO.Net] Upgrading XP to SQLite version?

[Gilles Ganault]

On Mon, 25 Feb 2013 15:18:17 +0100, Gilles Ganault
 wrote:
>When do we need the design-time components? Which package should I use
>for development + deployment onto users' host?

I'm not clear about which package to install on a development host,
and which to install on a customer's host.

1. Here's what I thought I understood:

- Setups: Includes design-time component for VS 2005, and Visual C++
2005 SP1 runtime
- Precompiled Binaries:  The Visual C++ 2012 RTM runtime is not
included
- Precompiled Statically-Linked: The Visual C++ 2005 SP1 runtime for
x86 is statically linked

Setups should only be installed on developer's host, and only if we
actually need the design-time components.
The Precompiled binaries should be  installed on the customer's host
through xcopy.
Use the static packages if the customer doesn't have admin rights to
install the VC++ runtime

Each package is available in two versions:
- bundle = "mixed-mode assembly + all the binaries"; mixed-mode
assembly only necessary when the binary assembly must be installed in
the GAC
- no-bundle = "all the binaries"

2. And here are the questions:
- What does the design-time component offer?

- What applications rely on the binary assembly in the GAC, and hence,
require installing the bundle packages?

3. Am I correct in understanding that the "no-bundle Precompiled
binaries" are usually the right choice, provided the customer can
install the VC++ runtime?

Thank you.

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

Re: [sqlite] minor typos in lang_corefunc.html's description of abs(X). domain of abs(X)

[Igor Tandetnik]

On 2/25/2013 10:53 AM, Eric Rubin-Smith wrote:

Minor typos aside, it's interesting that SQLite is nice enough to convert
integers strictly less than -1 * 2^63 to floats and then take the floating
absolute value.


There are no 64-bit signed integers (which is the only integral type 
SQLite deals with) less than -1 * 2^63. If you have a number like that, 
it must necessarily be of a floating point type to begin with. No 
conversion is taking place.



sqlite> select abs(-9223372036854775809);
9.22337203685478e+18


The conversion here is being performed by the SQL parser, which takes a 
sequence of characters "-9223372036854775809" and interprets it as a 
numeric literal representing a floating point number 
-9.22337203685478e+18 (note loss of precision).

--
Igor Tandetnik

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

Re: [sqlite] like query

[Simon Slavin]

On 25 Feb 2013, at 2:46pm, dd  wrote:

>  Table has string data type column. format of strings:
> somedata1/somedata2/somedata3
> 
>  I have written query to search : select * from emp where column_test like
> "somedata/%";
> 
>  It gives perfomance as per articles in internet. Is it? If yes, what is
> alternate query for this?

If the format of your 'LIKE' clause is always that you have fixed text at the 
beginning, then you can speed up your search a lot.  Create an index on the 
'column_test' field, and use this query:

SELECT * FROM emp WHERE column_test BETWEEN "somedata/" AND "somedata/zzz"

replace 'zzz' with '~~~' or something similar if you're being really fussy.

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

[sqlite] minor typos in lang_corefunc.html's description of abs(X). domain of abs(X)

[Eric Rubin-Smith]

"The abs(X) function returns the absolute value of the numeric argument X.
Abs(X) returns NULL if X is NULL. Abs(X) return 0.0 if X is a string or
blob that cannot be converted to a numeric value. If X is the integer
-9223372036854775807 then abs(X) throws an integer overflow error since
there is no equivalent positive 64-bit two complement value."

* The int should be -9223372036854775808.

* Also, "return 0.0" should be "returns 0.0".

* Also, "two complement" should be "two's complement".

Minor typos aside, it's interesting that SQLite is nice enough to convert
integers strictly less than -1 * 2^63 to floats and then take the floating
absolute value.

This means that the only gap in the function's domain is at the sole
integer value -1 * 2^63.  One greater and SQLite gives you a value; one
less and SQLite gives you a value:

sqlite> select abs(-9223372036854775807);
9223372036854775807
sqlite> select abs(-9223372036854775808);
Error: integer overflow
sqlite> select abs(-9223372036854775809);
9.22337203685478e+18

Given that, and given SQLite's general philosophy of being flexible about
its inputs, would it make sense for SQLite to convert -1 * 2^63 to a float
and return the floating absolute value, as it does for (-1 * 2^63) - 1?

I can also see the arguments against that choice: users who were careful to
store a 64-bit integer into a field may be surprised when it gets converted
to a float when passed through abs().  You also have backward compatibility
considerations.

Thanks as always to drh and team for the awesome tool.

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

Re: [sqlite] experimental (better?) usage based sqlite cache

[Gabriel Corneanu]

That's why I asked about feedback.
My implementation is ~100 lines longer, so I think it's still "lite".
There is nothing complex in it; apart from heap implementation, there 
are quite a few simplifications in the original code.


Gabriel

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

Re: [sqlite] experimental (better?) usage based sqlite cache

[Gabriel Corneanu]

I thought more about a "minus" (subtract minimum), but this might be a 
better option.


Regards,
Gabriel

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

Re: [sqlite] like query

[Richard Hipp]

On Mon, Feb 25, 2013 at 9:46 AM, dd  wrote:

> Hi,
>
>   Table has string data type column. format of strings:
> somedata1/somedata2/somedata3
>
>   I have written query to search : select * from emp where column_test like
> "somedata/%";
>
>   It gives perfomance as per articles in internet. Is it? If yes, what is
> alternate query for this?
>

The query might go faster if you do:

CREATE INDEX emp_idx1 ON emp(column_test COLLATE nocase);

Or, if you really intended to do a case-sensitive search, you could say:

SELECT * FROM emp WHERE column_test GLOB 'somedata/*';





>
>   Thanks in advance.
>
> Best Regards,
> dd.
> ___
> 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] like query

[dd]

Hi,

  Table has string data type column. format of strings:
somedata1/somedata2/somedata3

  I have written query to search : select * from emp where column_test like
"somedata/%";

  It gives perfomance as per articles in internet. Is it? If yes, what is
alternate query for this?

  Thanks in advance.

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

[sqlite] Error opening encrypted databases in multiple threads

[Thomas Müller]

We are using a bunch of encrypted SQLite databases and occasionally we get some 
errors with different error codes on SQLiteConnection.Open or 
SQLiteCommand.ExecuteReader().
This does not happen when the databases are not encrypted or if any database 
actions have been successfully done before.

All objects are only used on the thread they were created on.

I made a small solution to demonstrate this issue
SQLiteTest opens the same database 100 times. On my QuadCore with HT this fails 
about 50% of the time.

The ProcessStarter project therefore runs SQLiteTest 100 times.
https://www.dropbox.com/s/1wu0h97zbfi29kt/SQLiteTest.zip

It looks like some initialization code is not properly protected against multi 
threaded access. As a workaround I added the following code before anything is 
done with any database.

using (var connection = new SQLiteConnection("data 
source=:memory:;password=pwd"))
connection.Open();

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

[sqlite] [SQLite.ADO.Net] Upgrading XP to SQLite version?

[Gilles Ganault]

Hello

I'm running the 32-bit version of XPSP3, whose "Add and Remove
Programs" show that I have "QQLite ADO.NET 2*0/3.5 Provider 1.065.0"
from Phoenix Software Solutions, LLC (http://sqlite.phxsoftware.com).

Now that SQLite.Ado.Net is handled by www.slite.org, what is the right
way to upgrade to the latest and greatest? 

The wiki says:

"1. The setup packages are intended to be installed only on developer
machines and then only when the design-time components for Visual
Studio are required. In theory, they can be installed on customer
machines as well; however, this type of deployment is not recommended.

2. The binary packages are intended to be used by developers in order
to obtain the assembly binaries necessary for development and
deployment of their applications onto customer machines via XCOPY
deployment. [...]
Using XCOPY deployment in conjunction with the native library
pre-loading feature, especially for customer machines, is highly
recommended."
http://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki

When do we need the design-time components? Which package should I use
for development + deployment onto users' host?

Thank you.

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

Re: [sqlite] Do not read File change counter

[Richard Hipp]

On Mon, Feb 25, 2013 at 8:46 AM, Joost Voogt  wrote:

> Apparently I did something wrong the first time when I tried the exclusive
> lock because now I don't see SQLite reading the file change counter
> anymore. Sorry for that.
>
> I did some performance measurement on an FTS index on Android and I see
> some performance gain of around 5%. That is a nice gain but I'm wondering
> if there are any big drawbacks of using the exclusive lock. Probably when
> the FTS process (app) crashes you'll have to reboot the device as there
> still is an exclusive lock on the database? Are there any other problems
> that I'm unaware of which using the exclusive lock has?
>

No.  The lock is an ordinary Posix Advisory Lock and automatically goes
away when the application crashes.  The only disadvantage to an exclusive
lock is that other processes can neither read nor write to the database
file while you are holding it.



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



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

Re: [sqlite] Do not read File change counter

[Joost Voogt]

Apparently I did something wrong the first time when I tried the exclusive
lock because now I don't see SQLite reading the file change counter
anymore. Sorry for that.

I did some performance measurement on an FTS index on Android and I see
some performance gain of around 5%. That is a nice gain but I'm wondering
if there are any big drawbacks of using the exclusive lock. Probably when
the FTS process (app) crashes you'll have to reboot the device as there
still is an exclusive lock on the database? Are there any other problems
that I'm unaware of which using the exclusive lock has?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] experimental (better?) usage based sqlite cache

[Ryan Johnson]

On 25/02/2013 7:24 AM, Simon Slavin wrote:

On 25 Feb 2013, at 11:33am, Howard Chu  wrote:


Gabriel Corneanu wrote:

Following a few other discussions, I had the feeling that sqlite should
benefit from a cache which discards cached pages in a least frequently
used order.

Just offhand, classical LRU is quite poor in terms of lock overhead.

Gabriel writes "least frequently used".  Howard writes "least recently used".  
You're not writing about the same thing.

And the speed advantages of any algorithm used must be assessed before anything 
new is implemented.  SQLite is meant to be 'lite' and have almost nothing in.  
Complicated algorithms and use-counting should be added only if they improve 
things a lot.
(This is also partly a response to Howard, who suggests to just rely on 
the OS fs cache)


There seem to be quite a few sqlite3 users on platforms where I would 
not trust the OS to provide effective caching (embedded, smartphone, 
etc.) and we have seen complaints on the list from them about slow I/O. 
Granted, that's often due to logging overheads, but if we're going to 
see any real benefit from some new caching strategy in sqlite3, it's 
likely to show up there.


As for `lite', this could a pluggable extension, just like the fts 
stuff. Lots of people don't need it, but it can be acquired if needed.


Ryan

P.S. locking overheads are a bogey-man on a belligerently 
single-threaded system...


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

Re: [sqlite] experimental (better?) usage based sqlite cache

[Hick Gunter]

Just before the first fetch counter overflows, right shift all counters by 1. 
This does not change the purge order, keeps the counters in limits and happens 
infrequently (depending on the size of the counter).

-Ursprüngliche Nachricht-
Von: Gabriel Corneanu [mailto:gabrielcorne...@gmail.com]
Gesendet: Montag, 25. Februar 2013 12:25
An: sqlite-users@sqlite.org
Betreff: [sqlite] experimental (better?) usage based sqlite cache

Following a few other discussions, I had the feeling that sqlite should benefit 
from a cache which discards cached pages in a least frequently used order.
It generally means, index pages, often used data pages, etc, should be 
preferred (meaning kept in memory) compared to some infrequent used pages.
This helps me where I have big files which are mostly written once, but I also 
have some small tables with summaries; these should be better cached, the same 
for indices.

I first implemented a custom cache in Delphi (Pascal) using some high level 
(generic) containers (hash for keys,  heap for usage data); there is a 
significant overhead due to the classes I used and maybe also compiler 
differences.
My own usage shows some visible improvements, therefore I took some time to 
implement it directly in core (pcache1).

I would like to ask anyone who sees this interesting to try and give some 
feedback about benefits (if at all :)).
Feedback / results / benchmarks are welcome.

If it is useful, I would be happy to contribute it.
The diff is done against 3.7.15.2 ; I'm not sure if it makes it to the list, so 
here is the diff text:
http://pastebin.com/RrzqWjWv

Technical details:
- each page has a fetch counter
- the LRU list is changed to a heap, arranged according to this counter
- when discarding pages, the page with the minimum fetch counter is selected 
Apart from the heap operations, the other changes are quite straightforward.

I run some tests to check for errors, maybe someone can check if the 
initialization is done in proper place (especially for shared cache group).
There is an important catch; the fetch counter overflow. I don't have yet a 
definitive idea how/when to limit or to correct it.
So this problem is currently postponed until the tests show actual benefit / 
interest.

Regards,
Gabriel





--
 Gunter Hick
Software Engineer
Scientific Games International GmbH
Klitschgasse 2 – 4, A - 1130 Vienna, Austria
FN 157284 a, HG Wien
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This e-mail is confidential and may well also be legally privileged. If you 
have received it in error, you are on notice as to its status and accordingly 
please notify us immediately by reply e-mail and then delete this message from 
your system. Please do not copy it or use it for any purposes, or disclose its 
contents to any person as to do so could be a breach of confidence. Thank you 
for your cooperation.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] experimental (better?) usage based sqlite cache

[Howard Chu]

Simon Slavin wrote:


On 25 Feb 2013, at 11:33am, Howard Chu  wrote:


Gabriel Corneanu wrote:

Following a few other discussions, I had the feeling that sqlite should
benefit from a cache which discards cached pages in a least frequently
used order.


Just offhand, classical LRU is quite poor in terms of lock overhead.


Gabriel writes "least frequently used".  Howard writes "least recently used".  
You're not writing about the same thing.


Doh, you're right. Sorry for the noise, going back to get some caffeine now.

--
  -- Howard Chu
  CTO, Symas Corp.   http://www.symas.com
  Director, Highland Sun http://highlandsun.com/hyc/
  Chief Architect, OpenLDAP  http://www.openldap.org/project/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] experimental (better?) usage based sqlite cache

[Simon Slavin]

On 25 Feb 2013, at 11:33am, Howard Chu  wrote:

> Gabriel Corneanu wrote:
>> Following a few other discussions, I had the feeling that sqlite should
>> benefit from a cache which discards cached pages in a least frequently
>> used order.
> 
> Just offhand, classical LRU is quite poor in terms of lock overhead.

Gabriel writes "least frequently used".  Howard writes "least recently used".  
You're not writing about the same thing.

And the speed advantages of any algorithm used must be assessed before anything 
new is implemented.  SQLite is meant to be 'lite' and have almost nothing in.  
Complicated algorithms and use-counting should be added only if they improve 
things a lot.

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

Re: [sqlite] experimental (better?) usage based sqlite cache

[Gabriel Corneanu]

I'm not sure we're talking about the same thing.
For me caching here means avoiding IO, not memory and/or locks.
The heap itself also needs some work, but logarithmic.
The default value of 2000 pages cache should me enough for most useful 
pages (indices, roots...), having an overhead of max ~11 entries to update.


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

Re: [sqlite] experimental (better?) usage based sqlite cache

[Howard Chu]

Gabriel Corneanu wrote:

Following a few other discussions, I had the feeling that sqlite should
benefit from a cache which discards cached pages in a least frequently
used order.


Just offhand, classical LRU is quite poor in terms of lock overhead. The CLOCK 
refinement scales much better, because no reorganizing of LRU lists is needed 
during page references.


And of course, having gone thru all of these exercises of fancy 
application-level cache algorithms already, it's still obvious that the best 
approach is to leave it to the kernel.


--
  -- Howard Chu
  CTO, Symas Corp.   http://www.symas.com
  Director, Highland Sun http://highlandsun.com/hyc/
  Chief Architect, OpenLDAP  http://www.openldap.org/project/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] experimental (better?) usage based sqlite cache

[Gabriel Corneanu]

Following a few other discussions, I had the feeling that sqlite should 
benefit from a cache which discards cached pages in a least frequently 
used order.
It generally means, index pages, often used data pages, etc, should be 
preferred (meaning kept in memory) compared to some infrequent used pages.
This helps me where I have big files which are mostly written once, but 
I also have some small tables with summaries; these should be better 
cached, the same for indices.


I first implemented a custom cache in Delphi (Pascal) using some high 
level (generic) containers (hash for keys,  heap for usage data); there 
is a significant overhead due to the classes I used and maybe also 
compiler differences.
My own usage shows some visible improvements, therefore I took some time 
to implement it directly in core (pcache1).


I would like to ask anyone who sees this interesting to try and give 
some feedback about benefits (if at all :)).

Feedback / results / benchmarks are welcome.

If it is useful, I would be happy to contribute it.
The diff is done against 3.7.15.2 ; I'm not sure if it makes it to the 
list, so here is the diff text:

http://pastebin.com/RrzqWjWv

Technical details:
- each page has a fetch counter
- the LRU list is changed to a heap, arranged according to this counter
- when discarding pages, the page with the minimum fetch counter is selected
Apart from the heap operations, the other changes are quite straightforward.

I run some tests to check for errors, maybe someone can check if the 
initialization is done in proper place (especially for shared cache group).
There is an important catch; the fetch counter overflow. I don't have 
yet a definitive idea how/when to limit or to correct it.
So this problem is currently postponed until the tests show actual 
benefit / interest.


Regards,
Gabriel



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