Re: [sqlite] Not reading data in the -wal file? (Not Reproducible)

[Jerry Krinock]

Thank you to Igor and Richard.  I've studied this issue more, and still don't 
have an answer, although I've not been able to reproduce it either.  I'm not 
using shared cache, and even if I did leave a database connection open, which 
seems impossible since sqlite3_open(), sqlite3_finalize() and sqlite3_close() 
appear in that order with no early returns in the code and which is always 
called on the main thread, the next query would open a new database connection.

But at least I tried, and got advice from the smartest people out there.

Jerry

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

Re: [sqlite] Query on primary key not using primary key?

[Brad House]

Without additional information, SQLite guesses that the data_idx index will
narrow down the search to about 7 entries in the table.  This is, of
course, a guess, but it is a reasonable guess for most indices.  The
primary key, even though it is unique, has an IN clause with 50 entries, it
SQLite guesses it will narrow the search down to 50 entries.  SQLite picks
the index that leads to the least amount of searching: 7 entries versus 50.

In your case, I'm guessing that data_idx is really not a very good index
and might ought to be dropped for doing little more than taking up space.
What does the sqlite_stat1 entry for data_idx say?


I simply narrowed down an example from my application.  The data_idx actually
represents an index with 3 columns for a very-commonly used query in the
application which wasn't intended to be used for the query provided.

It sounds like the solution is to just run ANALYZE, then both queries
choose the right index.  I was just shocked to find the primary key
not used when referencing rows by primary key.

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

Re: [sqlite] Query on primary key not using primary key?

[Richard Hipp]

On Fri, Jun 7, 2013 at 12:56 PM, Brad House  wrote:

>  I would expect all queries which specify the primary key components
>>> in the WHERE clause to use the Primary Key in the query plan, regardless
>>> of if ANALYZE has been run or not.
>>>
>>>
>> SQLite examines many different strategies for evaluating each query.  For
>> each strategy it tries to estimate the total run-time.  It then selects
>> the
>> strategy that gives the least run-time.  Whether or not the PRIMARY KEY is
>> used as part of that strategy is not a consideration.
>>
>> ANALYZE does not change this.  The purpose of ANALYZE is merely to provide
>> additional information to help SQLite give a better estimate of the
>> run-time for each of the query strategies under consideration.
>>
>
> I guess I just don't understand how it would come up with a run-time
> strategy
> to NOT use a primary key (or any unique index) when the WHERE clause
> _exactly_
> matches such an index.  It also seemed to 'guess' that there'd be 2 result
> records without ANALYZE data and thus somehow chose a non-unique index
> utilizing
> fewer columns over a unique index ...
>
> The performance penalty is huge in my example, it's the difference of ~4s
> vs ~0.005s.
>
> Is this really not considered an issue/bug?
>

Without additional information, SQLite guesses that the data_idx index will
narrow down the search to about 7 entries in the table.  This is, of
course, a guess, but it is a reasonable guess for most indices.  The
primary key, even though it is unique, has an IN clause with 50 entries, it
SQLite guesses it will narrow the search down to 50 entries.  SQLite picks
the index that leads to the least amount of searching: 7 entries versus 50.

In your case, I'm guessing that data_idx is really not a very good index
and might ought to be dropped for doing little more than taking up space.
What does the sqlite_stat1 entry for data_idx say?

-- 
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] An "unable to open database file" error that has nothing to do with opening database file

[Warren Young]

On 6/6/2013 21:56, Philip Goetz wrote:


I think the problem is that the Cygwin distribution has the wrong
version of SQLite, one built for unix.


Nope.  And even if true, it wouldn't be the right explanation.

There are two major ways to build SQLite on Cygwin:

1. By default, building SQLite under Cygwin gets you a special 
Cygwin-aware mode, where SQLite bypasses the Cygwin DLL for some things, 
calling the Win32 API directly.  This is how the current[*] official 
binaries in the Cygwin distro are built.  Such a build still uses POSIX 
APIs for opening files, though, so the paths go to the Cygwin DLL first, 
and then after translation, to the Win32 API.  Since such a built knows 
its running on Windows, it shouldn't be using illegal characters in 
generated file names.


2. You can also build SQLite in a pure POSIX mode, with no direct calls 
to Win32 at all.  This mode is more compatible with other POSIX programs 
running under Cygwin, but less compatible with native Win32 builds of 
SQLite.  This distinction is irrelevant, however, because the file 
opening path is the same as with the Cygwin-aware build.



[*] (You can find test builds of SQLite for Cygwin built in Unix mode 
instead of Cygwin mode, but there hasn't been an official one in many 
months, which was quickly replaced due to the problems it caused.  We 
may be switching the official builds back to Unix mode soon, providing 
we can fix those problems, since the special Cygwin mode of SQLite 
causes its own problems.  It's one of those "having your cake and eating 
it too" kinds of things.)

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

Re: [sqlite] Query on primary key not using primary key?

[Brad House]

On 06/07/2013 12:46 PM, Simon Slavin wrote:


On 7 Jun 2013, at 5:37pm, Brad House  wrote:


I've modified my code to run an Analyze on startup to work around this,
but it obviously takes time to run and slows down startup.


I can't answer your question about why this happens in the first place, but I 
can tell you that the results of ANALYZE are saved in the database file through 
closing and reopening.  If you have run ANALYZE once on data which looks like 
the data that will be in your database in normal use, then you don't have to 
run it again.  SQLite will continue to use that information about table sizes 
and 'chunkiness' when devising query plans in the future.


Unfortunately we can't easily predict when analyze might be useful to run
due to transformations in the data that might affect the query planner.
We just temporarily added it as a workaround until we come up with a
better solution which might be something more cron-like.

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

Re: [sqlite] Query on primary key not using primary key?

[Brad House]

I would expect all queries which specify the primary key components
in the WHERE clause to use the Primary Key in the query plan, regardless
of if ANALYZE has been run or not.



SQLite examines many different strategies for evaluating each query.  For
each strategy it tries to estimate the total run-time.  It then selects the
strategy that gives the least run-time.  Whether or not the PRIMARY KEY is
used as part of that strategy is not a consideration.

ANALYZE does not change this.  The purpose of ANALYZE is merely to provide
additional information to help SQLite give a better estimate of the
run-time for each of the query strategies under consideration.


I guess I just don't understand how it would come up with a run-time strategy
to NOT use a primary key (or any unique index) when the WHERE clause _exactly_
matches such an index.  It also seemed to 'guess' that there'd be 2 result
records without ANALYZE data and thus somehow chose a non-unique index utilizing
fewer columns over a unique index ...

The performance penalty is huge in my example, it's the difference of ~4s vs 
~0.005s.

Is this really not considered an issue/bug?

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

Re: [sqlite] Query on primary key not using primary key?

[Simon Slavin]

On 7 Jun 2013, at 5:37pm, Brad House  wrote:

> I've modified my code to run an Analyze on startup to work around this,
> but it obviously takes time to run and slows down startup.

I can't answer your question about why this happens in the first place, but I 
can tell you that the results of ANALYZE are saved in the database file through 
closing and reopening.  If you have run ANALYZE once on data which looks like 
the data that will be in your database in normal use, then you don't have to 
run it again.  SQLite will continue to use that information about table sizes 
and 'chunkiness' when devising query plans in the future.

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

Re: [sqlite] Query on primary key not using primary key?

[Richard Hipp]

On Fri, Jun 7, 2013 at 12:37 PM, Brad House  wrote:

> I would expect all queries which specify the primary key components
> in the WHERE clause to use the Primary Key in the query plan, regardless
> of if ANALYZE has been run or not.
>

SQLite examines many different strategies for evaluating each query.  For
each strategy it tries to estimate the total run-time.  It then selects the
strategy that gives the least run-time.  Whether or not the PRIMARY KEY is
used as part of that strategy is not a consideration.

ANALYZE does not change this.  The purpose of ANALYZE is merely to provide
additional information to help SQLite give a better estimate of the
run-time for each of the query strategies under consideration.


-- 
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] Query on primary key not using primary key?

[Brad House]

I would expect all queries which specify the primary key components
in the WHERE clause to use the Primary Key in the query plan, regardless
of if ANALYZE has been run or not.

I would also think it would assume any index which covers the most
where-clause components would be the most efficient if analyze had
never been ran.

This doesn't appear to be the case...


Example data:

CREATE TABLE data (c1 INT, c2 INT, c3 INT, c4 INT, c5 TEXT, PRIMARY KEY(c1, 
c2));
CREATE INDEX data_idx ON data (c1, c3, c4);
INSERT INTO data VALUES(1, 1, 0, 0, "test");
...1,000,000 records later...
INSERT INTO data VALUES(1, 100, 0, 0, "test");


Then:

EXPLAIN QUERY PLAN UPDATE data SET c3 = 5, c4 = 3 WHERE c1 = 1 AND c2 IN 
(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50);
0|0|0|SEARCH TABLE data USING INDEX data_idx (c1=?) (~2 rows)
0|0|0|EXECUTE LIST SUBQUERY 0


If I run ANALYZE, I get the expected result afterward:

EXPLAIN QUERY PLAN UPDATE data SET c3 = 5, c4 = 3 WHERE c1 = 1 AND c2 IN 
(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50);
0|0|0|SEARCH TABLE data USING COVERING INDEX sqlite_autoindex_data_1 (c1=? AND 
c2=?) (~50 rows)
0|0|0|EXECUTE LIST SUBQUERY 0


Tested on 3.7.15, 3.7.16, 3.7.17 ... didn't go back too far to see
if this issue was introduced at some point or if it has always
been this way.

I've modified my code to run an Analyze on startup to work around this,
but it obviously takes time to run and slows down startup.

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

Re: [sqlite] select count(*) does not use primary key??

[Richard Hipp]

On Fri, Jun 7, 2013 at 11:51 AM, Ryan Johnson
wrote:

> On 06/06/2013 10:52 AM, Gabriel Corneanu wrote:
>
>> In my opinion, count(*) is the same as count(rowid) (I see that even
>> count() is accepted); I could say it's even the same as count(x) (any
>> other
>> field).
>>
> Not quite... count(x) only counts rows having non-NULL x. Granted, that's
> not a problem for rowid/pk (which are not allowed to be NULL), but it
> matters a lot in the general case.
>

PRIMARY KEYs (except for INTEGER PRIMARY KEYs) are allowed to be NULL in
SQLite.  This goes back to a bug in the code from many years ago.  By the
time the bug was discovered, SQLite was already in wide-spread use and so
the decision was made to not fix the bug since doing so would cause
compatibility problems.

-- 
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] select count(*) does not use primary key??

[Ryan Johnson]

On 06/06/2013 10:52 AM, Gabriel Corneanu wrote:

In my opinion, count(*) is the same as count(rowid) (I see that even
count() is accepted); I could say it's even the same as count(x) (any other
field).
Not quite... count(x) only counts rows having non-NULL x. Granted, 
that's not a problem for rowid/pk (which are not allowed to be NULL), 
but it matters a lot in the general case.


(but that doesn't explain the problem you're seeing)

Ryan

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

Re: [sqlite] MMIO and VFS Obfuscation

[Drake Wilson]

Quoth Drake Wilson , on 2013-06-07 08:18:05 -0500:
> Actually, I dropped a paragraph on the floor, sorry.  It's probably better to 
> use
> xRead for this, since in that case SQLite will manage its own memory for the 
> cache
> of decrypted pages.  The loss in that case, if you still use mmap behind the 
> scenes,
> is that you don't respect PRAGMA mmap_size anymore since the rest of SQLite 
> will
> think you're using "normal" I/O, and so it becomes impossible for other code 
> to
> turn mmap off to avoid I/O-error-based execution faults or such.

Gyaaah, sorry for all the little corrections---that's incomplete too, since you 
could
still respond to SQLITE_FCNTL_MMAP_SIZE in xFileControl; you'd just have to 
make sure
to apply it to the xRead path.

Now I will be quiet for a while, since my head apparently isn't fully in it.  
c.c

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

Re: [sqlite] regexp.c

[Philip Bennefall]

  - Original Message - 
  From: Richard Hipp 
  To: phi...@blastbay.com ; General Discussion of SQLite Database 
  Sent: Friday, June 07, 2013 1:14 PM
  Subject: Re: [sqlite] regexp.c





  On Fri, Jun 7, 2013 at 5:30 AM, Philip Bennefall  wrote:

Hello all,

I was having a quick look at the extensions provided in ext/misc in the 
source tree, and I am pleasantly surprised at the number of useful things that 
I found in there that I didn't know about. I was wondering about the regular 
expression parser in particular. Does it support capture groups? The source 
seems to indicate no, but I just wanted to make sure.


  Capture groups are not supported by the regexp.c implementation in ext/misc.  
On the other hand, that means that run-time is guaranteed linear in the size of 
the input.

  Thanks, Richard. That does make sense. On another note, generally how well 
tested are these extensions? Should I assume that since they're not part of the 
SqLite amalgamation, they aren't as extensively tested and maintained as the 
rest of SqLite's codebase?

  Kind regards,

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

Re: [sqlite] MMIO and VFS Obfuscation

[Drake Wilson]

Quoth Drake Wilson , on 2013-06-07 08:14:27 -0500:
> If you really want, you might be able to implement xFetch to allocate a shadow
> buffer, decrypt from the map into that, and return that pointer.  Since it's
> designed for accessing maps directly, though, I don't see it documented what
> the lifetime of that pointer has to be---it might be "until the file is 
> closed",
> in which case you have to keep those shadow buffers alive the entire time, but
> you might still avoid taking syscalls that way.

Actually, I dropped a paragraph on the floor, sorry.  It's probably better to 
use
xRead for this, since in that case SQLite will manage its own memory for the 
cache
of decrypted pages.  The loss in that case, if you still use mmap behind the 
scenes,
is that you don't respect PRAGMA mmap_size anymore since the rest of SQLite will
think you're using "normal" I/O, and so it becomes impossible for other code to
turn mmap off to avoid I/O-error-based execution faults or such.

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

Re: [sqlite] MMIO and VFS Obfuscation

[Drake Wilson]

Quoth Paul Vercellotti , on 2013-06-07 01:07:35 -0700:
> The current system does in-place decryption on read (no copying), which adds
> almost no overhead to the operation.    Now in-place decryption with a
> memory-mapped file seems like it would dirty the page, which could
> accidentally get flushed back to the file on close potentially corrupting the
> database - I'm wondering if that's a valid concern.  

Yes, you can't safely do it in-place on the mapped region; you'd have to make a
copy.  Since the big advantage of memory mapping is avoiding the copy, the 
benefit
would be noticeably decreased, I'd think---though if your decryption code can do
an out-of-place transformation "just as easily" then you could still elide that
together with the copy and maybe be a bit easier on the cache than an explicit
kernel->user copy plus an in-place transformation.

If you really want, you might be able to implement xFetch to allocate a shadow
buffer, decrypt from the map into that, and return that pointer.  Since it's
designed for accessing maps directly, though, I don't see it documented what
the lifetime of that pointer has to be---it might be "until the file is closed",
in which case you have to keep those shadow buffers alive the entire time, but
you might still avoid taking syscalls that way.

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

Re: [sqlite] problem sqlite3 c++ please

[Simon Slavin]

On 6 Jun 2013, at 2:30pm, Maxime Gerum  wrote:

>  but in c++, if i make request[4]= ”.mode csv” 

Commands starting with "." are specially built into the SQLite shell tool 
application.  They are not part of SQLite itself and cannot be used just by 
using API calls.  Sorry.

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

Re: [sqlite] FW: problem sqlite3 c++ please

[fnoyanisi]

Is this a repeated post? 

You cannot use shell commands as Sql statements in c api. Write your own export 
routine. Here is one I wrote.

https://github.com/fnoyanisi/sqlite3_capi_extensions

On 06/06/2013, at 11:02 PM, Maxime Gerum  wrote:

> 
> Hello, i’m french, sorry if i make errors of langage In my school project, i 
> use sqlite3 with c++, i can make any request such as create table or select 
> *from, no problems. But i would export my database in a csv file, in sqlite3> 
> .mode csv 
>   .separator ,
>.output 
> client.csv
>select *from client; it works, but in c++, if 
> i make request[4]= ”.mode csv”
>   “.separator ,”  
> ”.output client.csv”  
> “select *from client” 
> only select *from client works, i have an error : near ”.” : syntax error 
> Please help me,  Maxime Envoyé depuis Windows 8   
>   
> ___
> 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] FW: problem sqlite3 c++ please

[Maxime Gerum]

 Hello, i’m french, sorry if i make errors of langage In my school project, i 
use sqlite3 with c++, i can make any request such as create table or select 
*from, no problems. But i would export my database in a csv file, in sqlite3> 
.mode csv   
.separator ,
   .output client.csv   

select *from client; it works, but in c++, if i make 
request[4]= ”.mode csv” 
 “.separator ,” 
 ”.output client.csv”   
   “select *from client” only select *from 
client works, i have an error : near ”.” : syntax error Please help me,  Maxime 
Envoyé depuis Windows 8   
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] FW: problem sqlite3 c++ please

[Maxime Gerum]

 Hello, i’m french, sorry if i make errors of langage In my school project, i 
use sqlite3 with c++, i can make any request such as create table or select 
*from, no problems. But i would export my database in a csv file, in sqlite3> 
.mode csv   
.separator ,
   .output client.csv   

select *from client; it works, but in c++, if i make 
request[4]= ”.mode csv” 
 “.separator ,” 
 ”.output client.csv”   
   “select *from client” only select *from 
client works, i have an error : near ”.” : syntax error Please help me,  Maxime 
Envoyé depuis Windows 8 
  
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] select count(*) does not use primary key??

[Eduardo Morras]

On Fri, 07 Jun 2013 13:12:14 +0200
Clemens Ladisch  wrote:

> Eduardo Morras wrote:
> > where t.a = NULL
> 
>   where t.a IS NULL
> 
> (NULL compares as not equal to any value, including itself.)

OPppss you're right. Thought too fast and wrote even faster :(

> 
> Regards,
> Clemens

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

Re: [sqlite] regexp.c

[Richard Hipp]

On Fri, Jun 7, 2013 at 5:30 AM, Philip Bennefall wrote:

> Hello all,
>
> I was having a quick look at the extensions provided in ext/misc in the
> source tree, and I am pleasantly surprised at the number of useful things
> that I found in there that I didn't know about. I was wondering about the
> regular expression parser in particular. Does it support capture groups?
> The source seems to indicate no, but I just wanted to make sure.
>

Capture groups are not supported by the regexp.c implementation in
ext/misc.  On the other hand, that means that run-time is guaranteed linear
in the size of the input.

-- 
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] select count(*) does not use primary key??

[Clemens Ladisch]

Eduardo Morras wrote:
> where t.a = NULL

  where t.a IS NULL

(NULL compares as not equal to any value, including itself.)


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

Re: [sqlite] select count(*) does not use primary key??

[Eduardo Morras]

On Thu, 6 Jun 2013 10:53:55 -0400
Richard Hipp  wrote:

> On Thu, Jun 6, 2013 at 10:52 AM, Gabriel Corneanu  > wrote:
> 
> > Strange is, count(*) uses the cover index for a but "select count(a)" does
> > NOT use the same cover index...
> >
> 
> count(a) has to check for NULL values of a, which are not counted.
> count(*) does not.

If I understand well , select count(a) from t = (select count(*) from t) - 
(select count(*) from t where t.a = NULL) and both selects will use cover 
indexs, doesn't it? 


> -- 
> D. Richard Hipp
> d...@sqlite.org

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

Re: [sqlite] select count(*) does not use primary key??

[Gabriel Corneanu]

This is not at all my case ...

I don't obviously write 1 by 1, but using blocks of data ( array of 
struct ), virtual tables wrappers, and "insert ... select".
This way I can achieve >200k rec/s, or at least 100k when having some 
more fields.
Right now I'm completely CPU bound, it's 100% load at high rate. IO is 
almost out of question, at <10MB /s; and I use 8k page size and of 
course synchronous off, wal mode...
Another type of data (less fields but with a blob inside 2-32kB) easily 
reaches ~40MB/s but only a few thousands rec/s.
The performance drops abruptly when having more fields (I don't remember 
the magic threshold); it seems most of the load is needed for field 
coding ? I use only integers for space optimization (varint); this is 
also good as I have high dynamic range.


Multi-core sure helps to have enough CPU power for the rest (hardware 
connection, pre-processing, etc).


I would definitely like to be able to get more performance, but I can 
live with the current numbers. One can use some high-end CPUs if really 
wants such high rates (the hardware around costs ~100x more :) ).
BTW I asked a few times already, is it possible to get/compile a windows 
dll for sqlite4 (just for evaluation)?

Last time I checked, it didn't compile on windows at all.

Gabriel

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

Re: [sqlite] Header Application-ID list

[Eduardo Morras]

On Thu, 6 Jun 2013 08:15:57 -0400
Richard Hipp  wrote:

> On Thu, Jun 6, 2013 at 8:05 AM, Eduardo Morras  wrote:
> 
> >
> > Hi,
> >
> > Is there an official list of assigned application id sqlite header? If
> > exist, How can I register my application-id?
> >
> 
> The official list is here:  www.sqlite.org/src/artifact/f2b23a6bde8f
> 
> Send a request to this mailing list to add new items to the official list.

Thanks, I'll wait a bit until decide what hex describe better the app.
 
> Ideally, this content would be picked up by unix "file" command and be
> distributed to all unix systems.  However, my repeated emails to the
> maintainer Christos Zoulas about this have gone unanswered.  So for now the
> unix "file" command won't recognize the app-id unless you configure it
> yourself.

Then the magic.txt file should have more 'advertising campaign'. A d/l link 
www.sqlite.org front page, distribute within amalgamation and/or installation 
inside port/package/pkgsrc/rpm/your_linux_install_format as sqlite3 man pages 
do.

About Microsoft Windows, there's file in cygwin. But perhaps it's a MS problem 
not have a similar tool. I remember that in MacOS 6 (1988), perhaps earlier 
versions, you must register 4bytes for developer and 4 bytes for application 
(negative values preassigned for Apple use only) so system can identify file 
types, group data files with apps and developers.

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

[sqlite] regexp.c

[Philip Bennefall]

Hello all,

I was having a quick look at the extensions provided in ext/misc in the 
source tree, and I am pleasantly surprised at the number of useful things 
that I found in there that I didn't know about. I was wondering about the 
regular expression parser in particular. Does it support capture groups? The 
source seems to indicate no, but I just wanted to make sure.


Kind regards,

Philip Bennefall 


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

Re: [sqlite] select count(*) does not use primary key??

[Simon Slavin]

On 7 Jun 2013, at 8:57am, Gabriel Corneanu  wrote:

> BTW I found this by opening some file over network, which of course made 
> everything worse.
> [...]
> Not that I really need, but I have to support specified data rates up to 100k 
> records / second.

Maximum speed of a SQLite database is usually limited by speed of rotating hard 
disk.  If you do the maths on rotational latency you'll find there's no way to 
get 100k disk accesses per second.  SSD improves on this.

Do you have to support 100k records/second over network ?  If so,

what networking (Ethernet ?  WiFi ?) are you using
what networking file system are you using, what
what kind of mass storage device is your database file stored on ?

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

[sqlite] MMIO and VFS Obfuscation

[Paul Vercellotti]

Hi there,

We use a vfs-based obfuscation system using the old xRead and xWrite methods.  
I'm wondering if these can be adapted to work with the new memory-mapped i/o 
functionality in a way that still has the advantages of memory-mapped i/o?

The current system does in-place decryption on read (no copying), which adds 
almost no overhead to the operation.    Now in-place decryption with a 
memory-mapped file seems like it would dirty the page, which could accidentally 
get flushed back to the file on close potentially corrupting the database - I'm 
wondering if that's a valid concern.  

Anyway, any ideas on this?

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

Re: [sqlite] select count(*) does not use primary key??

[Gabriel Corneanu]

I admit I didn't think (or didn't even read in detail) about technical 
implementation.


This is an extract from analyzer:
*** Table AE_DATA 

Percentage of total database..  99.89%
Number of entries. 1030371
Bytes of storage consumed. 67846144
Bytes of payload.. 6118671990.2%
Average payload per entry. 59.38
Average unused bytes per entry 0.34
Average fanout 752.00
Fragmentation.   0.35%
Maximum payload per entry. 65
Entries that use overflow. 00.0%
Index pages used.. 11
Primary pages used 8271
Overflow pages used... 0
Total pages used.. 8282
Unused bytes on index pages... 15678   17.4%
Unused bytes on primary pages. 337429   0.50%
Unused bytes on overflow pages 0
Unused bytes on all pages. 353107   0.52%

So I understand that the 11 index pages are pure btree pages, but the 
leaves are actually in the ~8000 data pages.
And it probably needs to visit (i.e. load) all data pages to count the 
leaves...
Even if there would be some counter in the header of each page, it still 
needs to load the pages which is bad for IO...


BTW I found this by opening some file over network, which of course made 
everything worse.
For my case (file format) the data is append (write) only, so max(rowid) 
works equally good.
As a note, I actually HAVE the record count stored somewhere else but I 
had this query in a generic copy routine which was also used for some 
other small tables.
I agree it's some kind of corner case, usually tables have some kind of 
indices. But in this case I need high speed, indices would bring 
performance down.
Not that I really need, but I have to support specified data rates up to 
100k records / second.

And I only access the data sequentially by rowid.

Just for the sake of discussion: I imagine some hacks to the btree to 
optimize this special case.
The btree nodes could store the number of leaves just for the data pages 
(e.g. 0: unknown, >0 valid number); it would need to propagate up the 
info just until it reaches a parent in an index page. And it needs to 
update this info only when a node changes from leaf to having a child.


Thanks for all your time,
Gabriel
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users