[sqlite] Online/Hot backup of WAL journalling mode database

2014-12-09 Thread Nick
quot; and "-wal" file). - exit process Restore: - rsync the file "test.db" from another drive/location. Regards Nick ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Online/Hot backup of WAL journalling mode database

2014-12-09 Thread Nick
On 9 Dec 2014, at 22:06, Simon Slavin wrote: > > On 9 Dec 2014, at 8:57pm, Nick <maill...@css-uk.net> wrote: > >> Environment is Linux with multiple (c. 4-6) processes accessing a single >> sqlite database named "test.db". >> >> Backup: &

Re: [sqlite] Online/Hot backup of WAL journalling mode database

2014-12-10 Thread Nick
On 10 Dec 2014, at 02:36, Simon Slavin wrote: > > On 10 Dec 2014, at 12:30am, Nick <maill...@css-uk.net> wrote: > >> That's interesting Simon I didn't expect the database not to be trustworthy. > > The database will be trustworthy at any instant. Your copy of it

Re: [sqlite] Online/Hot backup of WAL journalling mode database

2014-12-10 Thread Nick
be detected by running PRAGMA quick_check / integrity_check? Having the occasional backup db corrupted would be tolerable. Regards Nick ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Online/Hot backup of WAL journalling mode database

2014-12-11 Thread Nick
On 11 Dec 2014, at 10:08, Dan Kennedy wrote: > On 12/11/2014 05:49 AM, Nick wrote: >> On 10 Dec 2014, at 07:35, Dan Kennedy wrote: >> >>> Strictly speaking the database file may not be well-formed even if there is >>> no ongoing checkpoint. If: >>>

Re: [sqlite] Online/Hot backup of WAL journalling mode database

2014-12-11 Thread Nick
; checkpointing and disabling it between backups sounds bad. > I would say the docs (https://www.sqlite.org/wal.html#how_to_checkpoint) do not imply application initiated checkpoints is a bad idea. Regards Nick ___ sqlite-users mailing list sqlit

Re: [sqlite] Online/Hot backup of WAL journalling mode database

2014-12-11 Thread Nick
start over again. So if you have frequent writes then theoretically the backup API would not complete. In an ideal world the backup API would only copy pages altered during the write rather than start over. Regards Nick ___ sqlite-users mailing list

[sqlite] Transaction speed too slow?

2011-05-08 Thread Nick
d Does anyone have an explanation for this? The script used is below. I'd be grateful if people could confirm whether it is just my hardware or a common result FULL RESULTS: nick@Haribo:~$ sudo hdparm -W 0 /dev/sda /dev/sda: setting drive write-caching to 0 (off) write-caching = 0 (off) nick@Har

Re: [sqlite] Transaction speed too slow?

2011-05-09 Thread Nick
would run the script below. Thanks in advance Nick On 9 May 2011, at 13:31, Black, Michael (IS) wrote: > You do realize the number they quote is a MAXnot necessarily what you'll > get. > > > > With 16 transactions per second you're writing on transaction every 63ms. M

[sqlite] Max page count not being remembered across close/open in SQLite 3.7.6.3

2011-08-30 Thread Nick
Hi When performing some testing I noticed that SQLite was not enforcing the max_page_count pragma. Using the test case below is appears SQLite is not remembering the max_page_count across closing and opening a database file. Instead of the expected 12800 from PRAGMA max_page_count; I got

Re: [sqlite] Max page count not being remembered across close/open in SQLite 3.7.6.3

2011-08-30 Thread Nick
is PRAGMA I did not realise. Similar to the page_count PRAGMA I would have intuitively expected max_page_count would not need restating every time the database file is open. Nick ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.o

[sqlite] update on Ticket 3437

2010-01-24 Thread nick
Hi, I submitted this some time ago, see http://www.sqlite.org/cvstrac/tktview?tn=3437,39. I've just been messing about with this again and have found out what was happening. The following script demonstrates the problem and resolution: [begin bug2.sql] /* this command line will demonstrate

[sqlite] SQLite SELECT performance problem

2012-05-24 Thread Nick
Hello! I have a program that does some math in an SQL query. There are hundreds of thousands rows (some device measurements) in an SQLite table, and using this query, the application breaks these measurements into groups of, for example, 1 records, and calculates the average for each group.

Re: [sqlite] SQLite SELECT performance problem

2012-05-24 Thread Nick
2012 at 3:59 PM, Nick <eveningn...@gmail.com> wrote: > >> >> Any way I could make my C program execute this query as fast as the >> prebuilt command line tool does it? >> > > > Have you tried compiling with the -DSQLITE_THREAD

Re: [sqlite] SQLite SELECT performance problem

2012-05-24 Thread Nick
downloaded from sqlite.org) sqlite3.exe a command: pragma compile_options; and made sure all these options (#defines) were set in my own built of sqlite3.exe 2012/5/25 Simon Slavin <slav...@bigfraud.org>: > > On 24 May 2012, at 8:59pm, Nick <eveningn...@gmail.com> wrote:

Re: [sqlite] SQLite SELECT performance problem

2012-05-24 Thread Nick
ilho* > > 2012/5/24 Simon Slavin <slav...@bigfraud.org> > >> >> On 24 May 2012, at 8:59pm, Nick <eveningn...@gmail.com> wrote: >> >> > So why does a prebuilt, downloaded from the sqlite website, command >> > line tool takes only 4 seconds, whil

Re: [sqlite] SQLite SELECT performance problem

2012-05-24 Thread Nick
://dl.dropbox.com/u/74970714/database.sqlite Thanks to anyone who can help! 2012/5/25 Simon Slavin <slav...@bigfraud.org>: > > On 24 May 2012, at 11:13pm, Nick <eveningn...@gmail.com> wrote: > >> In my initial message I described some proof-of-concept that I've done. >> >&

Re: [sqlite] SQLite SELECT performance problem

2012-05-25 Thread Nick
; > > > From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on > behalf of Nick [eveningn...@gmail.com] > Sent: Thursday, May 24, 2012 5:49 PM > To: General Discussion of SQLite Database > Subject: EXT :Re: [sqlite] SQLite SELECT performance problem > > > The

[sqlite] The performance of indexed select

2018-01-05 Thread Nick
I am trying to analysis the performance of indexed select. CREATE TABLE t2(a INTEGER, b INTEGER, c TEXT); CREATE INDEX t2c ON t2(c); I think there may be much more leaf index b-tree pages whose header is '0x0A' if the length of the content of index key 'c' is always 20-25 bytes, as I notice

Re: [sqlite] The performance of indexed select

2018-01-05 Thread Nick
Thank you Simon. But I am still uncertain if it is a good way to replace column 'c'. CREATE TABLE t2(a INTEGER, b INTEGER, d INTEGER); or: CREATE TABLE t2(a INTEGER, b INTEGER, c TEXT, d INTEGER); and then CREATE INDEX t2d ON t2(d); SELECT count(*) FROM t2 WHERE d = xx; I

Re: [sqlite] The performance of indexed select

2018-01-05 Thread Nick
Some simple SQLs: SELECT count(*) FROM t2 WHERE c = xx; (or d = xx) -- Sent from: http://sqlite.1065341.n5.nabble.com/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org

Re: [sqlite] The performance of indexed select

2018-01-07 Thread Nick
Thank you Keith for your useful advice. I am considering to organize the columns based on BCNF. I guess that table t3 is needed to remove functional dependency, which means I should use table t2 and t3 instead of one table t2 with 4 columns a-d. Is that right? I am not familiar with the concept

[sqlite] About test_vfs.c in sqlite test

2018-01-19 Thread Nick
I find a file named test_vfs.c when I run the tcl tests in the source tree. When I open a wal-file with a sqlite3_file* file descriptor pFile1, it called sqlite3OsOpen(). The call tree is like the pic below: sqlite3OsOpen(pFile1) | | pVfs->xOpen ==> tvfsOpen

[sqlite] Speed issue of SELECT in my application

2018-01-16 Thread Nick
I have a table below in my application: CREATE TABLE t1 ( a INTEGER PRIMARY KEY AUTOINCREMENT, b INTEGER NOT NULL UNIQUE, c INTEGER NOT NULL, d INTEGER, e TEXT, f INTEGER, g INTEGER, h TEXT, i INTEGER,

Re: [sqlite] Speed issue of SELECT in my application

2018-01-16 Thread Nick
Thank you Simon. As you said, UNIQUE(b, i) is redundant, but I guess it will not affect the performance of the SELECT. I find "SEARCH TABLE t1 USING INDEX sqlite_autoindex_t1_1 (b>?)" when I use EXPLAIN QUERY PLAN, so I do not need to add any index, right? Um, I guess I have nothing to do to

Re: [sqlite] Speed issue of SELECT in my application

2018-01-16 Thread Nick
OK. Thank you for your help. -- Sent from: http://sqlite.1065341.n5.nabble.com/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Speed issue of SELECT in my application

2018-01-17 Thread Nick
Thank you all. As Simon said, 60ms may be a reasonable figure and I am trying to focus on the detail of my service according to all your suggestion. -- Sent from: http://sqlite.1065341.n5.nabble.com/ ___ sqlite-users mailing list

Re: [sqlite] Speed issue of SELECT in my application

2018-01-17 Thread Nick
Jens, I totally agree with your opinion of profile. I have tried to find some useful tools to profile applications using sqlite and all I found is sqlite3_profile()(gettimeofday) and vdbe_profile(hardware time) inside sqlite. I also know a little about Time Profile of Instruments but I am using

Re: [sqlite] Make some changes to the source code

2018-01-24 Thread Nick
Yup, I guess I understand you correctly about the mmap problem in the OS. I have seen some threads about it before. But I think wal+mmap is still a worthy consideration as db+mmap has already been supported even though it is disabled by default. At least I think I could use it in my own

Re: [sqlite] Make some changes to the source code

2018-01-24 Thread Nick
I use sqlite in my Android application. And I tried to run sqlite test on my MacOS PC. Some cases failed but I can not figure out it is indeed a corruption. Do you mean the corruption problems you mentioned will happen in db+mmap? I guess it should happen in both wal+mmap and db+mmap if it

Re: [sqlite] Make some changes to the source code

2018-01-24 Thread Nick
I ran tests in my MacOS 10.12.6. -- Sent from: http://sqlite.1065341.n5.nabble.com/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Make some changes to the source code

2018-01-25 Thread Nick
Thank you Simon, I totally understand you. And still hope for someone to give me some advice about my wal+mmap (Map file when opening it and do not truncate the file) . Thanks. -- Sent from: http://sqlite.1065341.n5.nabble.com/ ___ sqlite-users

Re: [sqlite] Performance issue in different versions

2018-02-01 Thread Nick
Um, I am a OS application developer and we just upgraded the source code on our developing engine. I am sure I used the same compile-options. SQLITE_SECURE_DELETE is not set. -- Sent from: http://sqlite.1065341.n5.nabble.com/ ___ sqlite-users

Re: [sqlite] Performance issue in different versions

2018-02-01 Thread Nick
Yup, absolutely you are right. I just ran a new test using the same upper bound on the amount of memory used for the page cache, then I found a reasonable result. Thank you, Dan. I did notice the cache_size change before but you made me realize it. Thanks a lot. -- Sent from:

Re: [sqlite] Performance issue in different versions

2018-02-01 Thread Nick
I realized that the amount of memory used for the page cache is different. And I found that is the root cause. Sorry for my careless mistake. Thank you. -- Sent from: http://sqlite.1065341.n5.nabble.com/ ___ sqlite-users mailing list

[sqlite] Performance issue in different versions

2018-02-01 Thread Nick
I update sqlite in my Android mobile phone from 3.9.2 to 3.16.2. And I find the default page_size in 3.9.2 is 1024 while in 3.16.2 is 4096 (changed since 3.12.0). I think SQLITE_DEFAULT_PAGE_SIZE has great effect on the performance so I use speedtest1.c to test it. There are many test cases in

[sqlite] Question about threadsafe

2018-02-06 Thread Nick
I use sqlite 3.16.2 in Android system and I have configured journal_mode=WAL, threadsafe=1. My understanding is that: WAL => readers and only one writer can run at the same time. threadsafe=1 => mutex is used in serialized mode so that two writers is supported. Is it correct? But

Re: [sqlite] [EXTERNAL] Question about threadsafe

2018-02-06 Thread Nick
Yep, Hick. We have the same understanding. But all I found is that process B did not wait for the lock and began to run directly. -- Sent from: http://sqlite.1065341.n5.nabble.com/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org

Re: [sqlite] Question about threadsafe

2018-02-06 Thread Nick
> (a) an error result of some kind or (b) a corrupt database. I did not see any info about errmsg. > Are your processes using the same database connection or does each one > have its own ? Two processes have two sqlite3_open(). So each one has its own. > Are you checking the result codes

Re: [sqlite] Question about threadsafe

2018-02-12 Thread Nick
Thank you Keith. And there are something I want to make sure. >THREADSAFE=1 means that the sqlite3 library enforces the re-entrancy requirements via mutexes attached to the CONNECTION object. This means that the library will serialize access to the sqlite3 engine for you so that only one call

Re: [sqlite] Question about threadsafe

2018-02-12 Thread Nick
I ran several multi-threads tests these days and I want to get a confirmation that my understanding is correct. I use WAL mode and I think whether or not use the same connection with THREADSAFE=1, 2 is the key to my question. Mode 1, threadsafe=2 + multiple threads use the same connection: It is

Re: [sqlite] Question about threadsafe

2018-02-13 Thread Nick
>> is it OK to use "threadsafe=2 and >> 2 connections" in my apps if the 2 threads may write at the same time? >Yes. So I think "threadsafe=2 + more than 1 connection + busy_handler" is a good way to use. Another possible way is "threadsafe=1 and share 1 connection", but if thread 1 begins a

Re: [sqlite] Question about threadsafe

2018-02-13 Thread Nick
>> So I think "threadsafe=2 + more than 1 connection + busy_handler" is a good >> way to use. >This is the normal way to use SQLite. I ran a test and I can still find "database is locked" even if I use busy_handler(threadsafe=2, 2 connections). When thread 1 executing a writing transaction,

Re: [sqlite] [EXTERNAL] Question about threadsafe

2018-02-07 Thread Nick
Yes. But Process A begin Process A insert Process B begin Process B insert Process A end Process B end In fact, begin means "BEGIN" and end means "COMMIT". So I think the result is strange. And I guess the difference between Serilaized and Multithread

[sqlite] Dealing with SQLITE_BUSY

2018-02-22 Thread Nick
I use sqlite3_open() to open two connections, and I have configured journal_mode=WAL, threadsafe=2. Connection 1 is doing: sqlite3_exec(db1, "BEGIN", 0, 0, ); sqlite3_exec(db1, "SELECT * FROM t1;", 0, 0, ); sqlite3_exec(db1, "INSERT INTO t1 VALUES(1, 1, \"aa\”)”, 0, 0, ); //SQLITE_BUSY

Re: [sqlite] Speed issue of SELECT in my application

2018-01-17 Thread Nick
I am confused about your table t2. It will be faster to query the table t1, but I need the content of column e and h when I query the data which means I need a extra SELECT from the table t2, is it right? At the same time, I guess it is more complicate to INSERT data into both t1 and t2. What

Re: [sqlite] Speed issue of SELECT in my application

2018-01-17 Thread Nick
Thank you Smith. The table is preseted with some data in my Android system. And I guess my APPLICATION is more like a SERVICE which will be started at system boot time and speed issue happens at the time. According to the some other reasons, I have to use wal+normal journal and sync mode. >3.

[sqlite] Make some changes to the source code

2018-01-24 Thread Nick
I am trying to make some changes to the source code of sqlite. As I found there will be a little improvement if I support MMAP to wal file. I guess the optimization is micro but it is useful in my test and this is a good way for me to study the code of sqlite :D 1.wal+mmap I could use

Re: [sqlite] [EXTERNAL] Does mmap increase PSS?

2018-04-12 Thread Nick
Thanks a lot, Hick. So, if - mmap_size=256M - run only one copy of my program (has no other process to split PSS) - have a large enough amount of main memory (bigger than 256M) - a big db file (bigger than 256M) Then the PSS of my program will be about 256M. Is that correct? -- Sent from:

Re: [sqlite] [EXTERNAL] Does mmap increase PSS?

2018-04-13 Thread Nick
I find I confused several concepts of memory. Sorry for that. And I guess I finally understand what my question really is: Still there is only one process doing a SELECT * in a 256M db file. Then 256M physical memory should be used when doing the query. (Ignore the cache_size.) So the PSS of my

Re: [sqlite] [EXTERNAL] Does mmap increase PSS?

2018-04-12 Thread Nick
OK, I understand. I ran a simple program to test if mmap will cause the increasing of PSS. But I did not find the PSS increase according to showmap: addr = (char *)mmap(NULL, length, PROT_READ, MAP_SHARED, fd, 0); for(i=0; i

[sqlite] Does mmap increase PSS?

2018-04-11 Thread Nick
Hi, I guess that "cache_size=2000" means PSS of my process will always less than 2M. But, when I use PRAMGA mmap_size to enable mmap, I found the PSS of my process will almost the same as my db. Is that correct? -- Sent from: http://sqlite.1065341.n5.nabble.com/

Re: [sqlite] [EXTERNAL] Does mmap increase PSS?

2018-04-12 Thread Nick
Thanks for your explanation. I want to get a confirmation that my understanding is correct and that if I use mmap_size=256M and I have only 1 process, then the PSS of the process will always the same as the size of my db file, as unixMapfile(-1) means map the whole file. (A big db file means 256M

[sqlite] Any operation to trigger osMunmap?

2018-04-18 Thread Nick
Hi, I have one process using sqlite with “pragma mmap_size=30M”. The operations of my process is insert-select-insert-select. So the PSS(private clean) will increase along with the growing of the db which is treated as memory leak by Mem-Analysor tool. I guess calling sqlite3_close() or pragma

Re: [sqlite] Any operation to trigger osMunmap?

2018-04-18 Thread Nick
So the PSS will not decrease even if the db becomes smaller after some DELETE/vacuum operations? I think it is a better way to free the mmap memory after every query automatically inside sqlite. Why not? -- Sent from: http://sqlite.1065341.n5.nabble.com/

[sqlite] SQLITE_ENABLE_STAT3/4 causing expensive statement recompilation

2013-12-11 Thread Nick Hutchinson
is dramatically outweighing any potential savings that a better query plan could offer! Has anyone else had a similarly negative experience with SQLITE_ENABLE_STAT3/4? Cheers, Nick ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org

[sqlite] EF6 + System.Data.Sqlite SaveChanges() not working?

2014-02-18 Thread Nick Zhu
me to the right direction, that'd be fantastic, thanks so much Nick ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLite for single user data manipulation

2014-04-16 Thread Nick Eubank
to read into RAM and manipulate with the usual suspects (R, Stata, Matlab, etc.). Thanks all! Nick ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Is 32bit SQLite limited to 1900mb RAM in windows?

2014-06-30 Thread Nick Eubank
bit build (on another project I discovered 32bit windows is limited to 1900 mb per thread due to how memory addresses are stored)? If so, any advice on installing 64bit version on Windows 8 for someone with zero experience compiling C? Thank you so much! Nick

Re: [sqlite] Is 32bit SQLite limited to 1900mb RAM in windows?

2014-06-30 Thread Nick Eubank
Thanks Cory -- is this fixed in 64 bit versions of SQLite? I know postgres never changed memory address variable storage in the 64 bit so the problem persists. Also: any advice on getting a 64bit installation for someone who doesn't really know how to compile C? Thanks! Nick On Monday, June 30

Re: [sqlite] Is 32bit SQLite limited to 1900mb RAM in windows?

2014-06-30 Thread Nick Eubank
, for example, because they never updated the addressing code when they built a 64bit version -- a lesson I learned the hard way. ) On Monday, June 30, 2014, Simon Slavin <slav...@bigfraud.org> wrote: > > On 30 Jun 2014, at 4:58pm, Nick Eubank <nickeub...@gmail.com > <javascript:;>&g

Re: [sqlite] Is 32bit SQLite limited to 1900mb RAM in windows?

2014-06-30 Thread Nick Eubank
> > > > > > > > On Mon, Jun 30, 2014 at 12:14 PM, Nick Eubank <nickeub...@gmail.com> > > wrote: > > > >> Sorry, I wasn't clear: I have 64 bit R and Windows, but since there are > no > >> 64 bit binaries for SQLite I started with 3

Re: [sqlite] Need help on SQLite In-Memory Mode

2014-08-11 Thread Nick Bao
Thanks, Joe! But it did not work for me. Still got the error: System.ArgumentException: Invalid ConnectionString format for parameter "FullUri" Nick Bao DL_DEV_4/DL_DEV_19, VP, Dalian Office - AvePoint, Inc. P: +86.411.8473.6866 | F: 159.0496.1680 | nick@avepoint.com Follow us o

[sqlite] memory usage after VACUUM

2011-03-09 Thread Nick Hodapp
the equation to test, however. Nick Hodap ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] duplicate a prepared statement?

2011-03-09 Thread Nick Hodapp
through the results of these queries in step with each other, not serially. I was hoping to avoid preparing the same sql multiple times, on the assumption that it would be more efficient to somehow duplicate a prepared statement. Is this possible? Nick Hodapp

Re: [sqlite] Sqlite Files

2011-05-23 Thread Nick Shaw
There's always the SQLite manager for FireFox add-on: http://code.google.com/p/sqlite-manager/. I use it quite a lot to quickly check the data inside my own sqlite databases. Has a pretty intuitive GUI. Nick. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users

Re: [sqlite] Biggest number in an INTEGER field

2011-06-01 Thread Nick Shaw
> Guys, an an SQLite3 INTEGER field what is the maximum number that fits in an INTEGER PRIMARY KEY field? According to http://www.sqlite.org/faq.html#q1, it is 9223372036854775807. Thanks, Nick. ___ sqlite-users mailing list sqlite-users@sqlite.

Re: [sqlite] Making data and tables persistent

2011-06-03 Thread Nick Shaw
S crash occurs before you commit them), the next time you opened the database, sqlite would rollback the uncommitted changes in the journal file (I assume). Nick. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] attach readonly db to main db that is readwrite

2011-09-28 Thread Nick Hodapp
My primary database is opened for read/write. I use the ATTACH command to attach a second database that lives in a read-only filesystem. It appears to mostly work, but is there anything I should be aware of or concerned about? One thing I noticed is if I run "ANALYZE" once the read-only is

[sqlite] Very slow processing of some SELECT statements

2011-10-17 Thread Nick Gammon
draw your attention to the fact that the string being searched for is quoted, and that it is declared as a text field in the database. Any suggestions welcomed. - Nick Gammon ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:80

Re: [sqlite] Very slow processing of some SELECT statements

2011-10-17 Thread Nick Gammon
On 17/10/2011, at 8:33 PM, Nick Gammon wrote: > ... > The following SQL: > > ... > SELECT * FROM exits WHERE fromuid = '2E515665758C87202B281C7FC'; > > > Takes over 2 seconds to execute (in particular, the SELECT statement). > Further to the above, ch

Re: [sqlite] Very slow processing of some SELECT statements

2011-10-17 Thread Nick Gammon
00.zip So the test was confirmed using sqlite3.exe, from the SQLite3 site, as distributed. - Nick ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Very slow processing of some SELECT statements

2011-10-17 Thread Nick Gammon
On 18/10/2011, at 3:38 PM, Dan Kennedy wrote: > Now fixed here: > > http://www.sqlite.org/src/ci/59bb999c8b?sbs=0 Thanks! ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Very slow processing of some SELECT statements

2011-10-18 Thread Nick Gammon
On 18/10/2011, at 4:46 PM, Sylvain Pointeau wrote: > Is it normal that fromuid of the table exits is STRING ? > I think it should be TEXT to be surely processed as text and not float That was an error. However it shouldn't take SQLite 2.5 seconds to handle *any* numeric literal. Especially as

[sqlite] Poor performance with nested query in outer join

2011-12-10 Thread Nick Smallbone
join instead of an outer join, or if I replace "select * from b" with just "b" (but, as I mentioned above, I can't do that in reality). This happens in SQLite 3.7.9, as well as the latest version from Fossil. Is it a bug, or am I just expec

Re: [sqlite] Poor performance with nested query in outer join

2011-12-10 Thread Nick Smallbone
Simon Slavin <slav...@bigfraud.org> writes: > On 9 Dec 2011, at 10:25pm, Nick Smallbone wrote: > >> select * from a left natural join (select * from b) where id = 1; > > Try not to use sub-selects when you can use a JOIN instead. > Especially don't use them in c

Re: [sqlite] Poor performance with nested query in outer join

2011-12-11 Thread Nick Smallbone
n or an explicit join here (and I would've been very surprised if it had, because they are exactly the same operation). Rather, the difference between my query and your query is that I have a subquery (select * from b) and you don't. Nick ___ sq

Re: [sqlite] Poor performance with nested query in outer join

2011-12-12 Thread Nick Smallbone
ry for the view), as far as I can tell. So if you LEFT JOIN with a VIEW you always get this problem. Nick ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] EXT :Re: Poor performance with nested query in outer join

2011-12-12 Thread Nick Smallbone
* from a left natural join v where id = 1; IIUC, when I execute the query on the second line, SQLite replaces the use of "v" with v's definition, "select * from b where ...", so as far as the query planner is concerned there is a subquery. Th

[sqlite] sqlite3 performace

2008-01-17 Thread Philip Nick
Greetings, Currently I am using sqlite3 in a multi-process/multi-threaded server setting. I use a Mutex to ensure only one process/thread can access the database at one time. The current flow of events: Get Mutex Open Database connection Run Query Close Database connection Release Mutex This

Re: [sqlite] sqlite3 performace

2008-01-17 Thread Philip Nick
() == SQLITE_ROW) >From my understanding the sqlite3_exec() is doing the same thing and sending the info too the callback. Any ideas? Thanks On Jan 17, 2008 5:09 PM, <[EMAIL PROTECTED]> wrote: > "Philip Nick" <[EMAIL PROTECTED]> wrote: > > Greetings, &g

Re: [sqlite] sqlite3 performace

2008-01-18 Thread Philip Nick
thread, so we don't have to keep opening the db. In the past we had lots of issues doing this, hence the open for each query model. Any advice would be appreciated. Phil On Jan 18, 2008 8:46 AM, Jay Sprenkle <[EMAIL PROTECTED]> wrote: > On Jan 17, 2008 3:48 PM, Philip Nick <[EMA

[sqlite] File locking with BEGIN/END

2010-05-25 Thread Nick Shaw
process to know when the database has changed on disk. Thanks, Nick. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] File locking with BEGIN/END

2010-05-25 Thread Nick Shaw
> Is it possible you have a busy-handler installed? Are you using SQLite directly or via some wrapper API? Yes and no. I set a busy handler of 1 minute, but it takes longer than this and doesn't timeout, plus I tried without the busy timeout; made no difference. Thanks, N

Re: [sqlite] File locking with BEGIN/END

2010-05-25 Thread Nick Shaw
on with the inserts. Seems to be faster doing it this way (though not by much). I've got the delay in the second process down to around 200ms which is much improved from the original delay of over a minute. :) Thanks all, Nick. ___ sqlite-users mailing

[sqlite] FTS3 query question

2010-06-02 Thread Nick Hodapp
MATCH in the requested context". Any pointers, please? Nick Hodapp ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] database corruption problem

2010-06-08 Thread Nick Shaw
ite. That can cause no end of problems, and is usually very hard to pin down unless you can reproduce it. If this is the first report you've ever had on this, and you have a fair few systems installed out in the field, my bet would be on #1. Thanks, N

Re: [sqlite] columnb = upper(columna)

2010-06-23 Thread Nick Shaw
ther long winded though, and is effective duplication of data. Using a VIEW as Simon recommends seems a lot simpler, or just select the field as upper(name) when you want to get the data out. Nick. -- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SqLite 3.7.0 amalgamation build warnings

2010-07-07 Thread Nick Shaw
ns too many references; ignoring further references from this source The latter one concerns me the most. I can hide the first one with a compiler pragma, but I can't hide the second as it's a BSCMAKE warning. Thanks, Nick. ___ sqlite

Re: [sqlite] SqLite 3.7.0 amalgamation build warnings

2010-07-07 Thread Nick Shaw
// --- End added lines The pragma warnings are to disable other build warnings that I consider safe to ignore. The lowest one, 4706 is the compile warning I mention below, and is caused by this specific line of code: for(eMode=0; (zMode = sqlite3JournalModename(eMode)); eMode++){ Thanks, Nick

Re: [sqlite] SqLite 3.7.0 amalgamation build warnings

2010-07-07 Thread Nick Shaw
3.6.x that I just hid with a pragma: pPage->nFree -= (nCell*2 + nUsable - cellbody); > warning C4244: '-=' : conversion from 'int' to 'u16', possible loss of data Thanks, Nick. --- > -Original Message- > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- >

Re: [sqlite] SqLite 3.7.0 amalgamation build warnings

2010-07-07 Thread Nick Shaw
ide the warning. Thanks, Nick. > -Original Message- > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- > boun...@sqlite.org] On Behalf Of Eric Smith > Sent: 07 July 2010 16:34 > To: General Discussion of SQLite Database > Subject: Re: [sqlite] SqLite 3.7.0 amalga

Re: [sqlite] SqLite 3.7.0 amalgamation build warnings

2010-07-15 Thread Nick Shaw
(note case) builds without the above warning, but then generates a warning about using the /Fr flag as it's deprecated!) [to hide the build warning]. Thanks all. 3.7.0 now compiles nicely with no errors/warnings and appears to work perfectly. Nick. ___ sq

Re: [sqlite] Are triggers the best way to store the modification time?

2010-09-08 Thread Nick Shaw
lumn in a separate table? It sounds like your triggering on ANY table update, not the specific table you're interested in. Nick. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] DB file locked when no other process using it - Found word(s) list error in the Text body

2010-09-15 Thread Nick Shaw
I'm not sure how to do this programmatically though, or how to find/release locks on other operating systems. Nick. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Andrew Wood Sent: 14 September 2010 14:46 To: General Discuss

[sqlite] sqlite3_open on non-DB files / corrupt DBs

2010-11-25 Thread Nick Shaw
of thing? Thanks, Nick. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3_open on non-DB files / corrupt DBs

2010-11-25 Thread Nick Shaw
> > > So... Assuming the above is how sqlite is meant to work, what would be > > the best way to ascertain if a successfully opened file is ok to use? > > Run "PRAGMA integrity_check" right after opening. Ah, that's what I was looking for! Thanks Igor, I'll

Re: [sqlite] sqlite3_open on non-DB files / corrupt DBs - Found word(s) list error in the Text body

2010-11-25 Thread Nick Shaw
t to be an issue, or the integrity of the database is more important than a delayed DB open (which happens in a seprate thread to my apps' main GUI, so the user won't notice an issue anyway). Thanks for all the help peeps, Nick. ___ sqlite-users mailing

Re: [sqlite] Compiler warnings in R-Tree code under Visual StudioExpress

2011-02-18 Thread Nick Shaw
on the file properties in Visual Studio, I get a ream of warnings, but they've yet to cause any problems at all (hence I compile with Level 3 warnings so I don't keep seeing them). Nick. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlit

[sqlite] INTEGER store size

2008-09-18 Thread Nick Shaforostoff
Hi. I'm deciding between having several INTEGER fields and only one, which is a bit combination (i'd access it using e.g. main.bits&0x0011, main.bits&0x0100 and so on). The docs say "INTEGER. The value is a signed integer, stored in 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the

  1   2   >