Re: [sqlite] Not reading data in the -wal file? (Not Reproducible)
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?
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?
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
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?
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?
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?
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?
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?
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??
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??
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
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
- 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
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
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
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
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
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
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??
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
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??
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??
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??
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
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
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??
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
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??
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
