[sqlite] SELECT * with ORDER BY returning duplicate rows.
To add: The second query Seg-faults in the sqlite 3.8.8 dll near the end. The other two runs to end. It seems to be a NULL reference, the error ref: -- Last Script Error: Exception Executing Script: Access violation at address 61C11C1E in module 'sqlite3.dll'. Read of address -- Query: Select * from RawDataSamples order by ChannelID, SeqIndex; The last "seqIndex" value returned before the seg fault is 6360. Not sure if it is relevant, but he has no Primary Key on that table, but uses the first column in two table constraints - both as a child link to a cascading foreign key in another table (which in turn links to many others) and in an non-Unique Index. Schema: CREATE TABLE "RawDataSamples" ( "ChannelID" integer NOT NULL, "SeqIndex" integer NOT NULL, "Sample_0" double DEFAULT NULL, "Sample_1" double DEFAULT NULL, ... etc ... "Sample_29" double DEFAULT NULL, "Sample_30" double DEFAULT NULL, FOREIGN KEY (ChannelID) REFERENCES "Channels"(ChanID) ON DELETE CASCADE ON UPDATE CASCADE ); CREATE INDEX RDChanID ON RawDataSamples (ChannelID); Parent Schema: CREATE TABLE "Channels" ( "ChanID" INTEGER PRIMARY KEY, "SensorID" integer NOT NULL, "SequenceSetID" integer NOT NULL, "TGSetID" integer NOT NULL, "ChannelType" integer NOT NULL, FOREIGN KEY (SensorID) REFERENCES "Sensors"(SensorID) ON DELETE CASCADE ON UPDATE CASCADE, FOREIGN KEY (SequenceSetID) REFERENCES "SequenceDataSet"(SeqSetID) ON DELETE CASCADE ON UPDATE CASCADE, FOREIGN KEY (TGSetID) REFERENCES "TimeGates"(TGSetID) ON DELETE CASCADE ON UPDATE CASCADE, FOREIGN KEY (ChannelType) REFERENCES "ChannelTypes"(ChannelType) ON DELETE CASCADE ON UPDATE CASCADE ); On 2015-04-11 09:05 PM, Stephen Lee wrote: > > Select count(*) from RawDataSamples order by ChannelID, SeqIndex will return > 69770. (Correct value) > > > > Select * from RawDataSamples order by ChannelID, SeqIndex will return 83665 > rows. > > > > Select * from RawDataSamples order by SeqIndex,ChannelID will return 69770 > rows. (Again correct) > The database file is at > https://www.dropbox.com/s/1qo0qdeyn71biq4/land.sldb?dl=0 > >
[sqlite] sqlite3 (or sqlite4) performance on NFS
>From what I understand; - Read-Only data - Data doesn't change frequently - Central repository for data - Network latency causing issues My two cents on this is to keep a database revision ID kicking around and do a SQLite backup of the remote data to a local storage medium. At application launch, check the local version of the database, then check the NFS version, and if there is a mismatch or a local copy doesn't exist, have the application ask (Or force if no local copy exists) to copy the data from remote to local, then read data from the local source. This will be a bit of a PITA if you're talking gigabytes of storage on a saturated 100mbit network or if drive space is limited. (I love my quiet GigE network) If you ever make a change to the database of relevance, update the database ID on the NFS version and any new client connections will do the version validation and (ask to...) pull the required changes when required. I may love my GigE network, but just two days ago I turned off a network switch accidentally and lost all communication to my server unintentionally. Stupid things like that could put a halt on your production environment. If you keep things local, the network isn't going to matter (Unless your app also talks to other network appliances/devices/etc via the affected network) and the gain is that you're also going to increase the speed of your app. Depending on database size and memory amount, you could also increase the speed of database transactions by not only copying the data from the remote storage to a local drive, but go a step further and do another backup from local drive to RAM by doing a backup to [ :memory: ] and then access what is in memory. The downside of that, again depending on size of the database, is not only memory constraint but initial load time of the application considering NAS->Local->Memory. On Sat, Apr 11, 2015 at 9:30 AM, Peng Yu wrote: > Hi, > > I know that sqlite3 is not recommended to be used on NFS. But I only > use sqlite3 for read only (the only time that I write is to load the > initial data to the data base). With this restriction, NFS should be > fine as the storage since no file locking is needed. > > I remember that sqlite3 on NFS is significantly slower than on a local > disk (a few times slower when I tested a few years ago). > > I am wondering for my limited usage scenario, is it possible to make > sqlite3 on NFS as fast as sqlite3 on local storage (such as disable > file locking). Thanks. > > -- > Regards, > Peng > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
[sqlite] sqlite3 (or sqlite4) performance on NFS
On 2015-04-11 03:30 PM, Peng Yu wrote: > Hi, > > I know that sqlite3 is not recommended to be used on NFS. But I only > use sqlite3 for read only (the only time that I write is to load the > initial data to the data base). With this restriction, NFS should be > fine as the storage since no file locking is needed. > > I remember that sqlite3 on NFS is significantly slower than on a local > disk (a few times slower when I tested a few years ago). > > I am wondering for my limited usage scenario, is it possible to make > sqlite3 on NFS as fast as sqlite3 on local storage (such as disable > file locking). Thanks. You can use SQLite for reading over a network, it's just write concurrency that cannot be guaranteed (as you seem to know already). You can switch of syncing easily with pragma synchronous = 0; but that only really helps write speeds. Not sure about disabling File locking completely but you can create your own VFS to circumvent it similar to the "unix-none" VFS, but I wouldn't advise that. The slowness is not due to SQLite doing anything different over a network, it's because the data is on the other side of a router, Nothing much can make that any faster except maybe some network tweaking. The other advice for speed on a read-only DB is the same as when the DB file is local: - Set the Page size to match your physical layer (i.e Network in your case), usually 4K (pragma page_size = 4096;). - Make sure the cache has ample space to push large query data around (pragma chache_size = 1; perhaps). - Put the temp store to memory if you use temp tables during queries (pragma temp_store = 2;). - Use a memory-based journal mode (or at least a truncate) when doing queries in transactions (pragma journal_mode = MEMORY; ). Make sure whatever process builds the database keeps the Indices up to date and vacuums the database after update. If the network is still slow (such as an internet based VPN on a low bandwidth connection), what I find works well is periodic updates. I RAR the DB file in a script on the server, rename it to some hashed value, when all is done, publish the hash key in a file so all clients can see which is the latest complete DB, copy that to their own drives and uncompress it, This can be done by a second thread so that only when the data is completely available locally and uncompressed does it switch to that DB file as the new data source. The user on the client machine never realises any slowness. (You might have to indicate exactly how "fresh" the data is though). Links for all the above: https://www.sqlite.org/vfs.html https://www.sqlite.org/pragma.html#pragma_synchronous https://www.sqlite.org/pragma.html#pragma_cache_size https://www.sqlite.org/pragma.html#pragma_page_size https://www.sqlite.org/pragma.html#pragma_journal_mode https://www.sqlite.org/pragma.html#pragma_temp_store https://www.sqlite.org/lang_reindex.html https://www.sqlite.org/lang_vacuum.html Good luck Ryan
[sqlite] SELECT * with ORDER BY returning duplicate rows.
Hi I am using V3.8.9 of SQLite3.exe on Windows 7 (64bit). I have a table in a database which shows the following behaviour. The table, 'RawDataSamples', has columns ChannelID, SeqIndex and then Sample_1 .. Sample_30. Select count(*) from RawDataSamples order by ChannelID, SeqIndex will return 69770. (Correct value) Select * from RawDataSamples order by ChannelID, SeqIndex will return 83665 rows. Select * from RawDataSamples order by SeqIndex,ChannelID will return 69770 rows. (Again correct) The ChannelID values are 1 .. 10, while the SeqIndex values are from 0 .. 6976 for each ChannelID value. For ChannelID 1 .. 7, only Sample_0 has data, the other columns are null. For ChannelID 8 ..10, all columns have data. In the second query above, most, but not all, the results for ChannelID 9 and 10 are duplicated. If I delete all other ChannelID values and just leave 9 and 10, then the select will produce correct single values for ChannelID 9, but most results for 10 will still be duplicated. If I delete ChannelID = 9 values, ChannelID 10 values are no longer duplicated. I have other databases with the same structure which have fewer rows per ChannelID value, and they are ok. It seems to be related to the number of rows for the first order by column. The database file is at https://www.dropbox.com/s/1qo0qdeyn71biq4/land.sldb?dl=0 I did look through the bug list, and if I missed this one then I apologise. Regards Steve
[sqlite] sqlite3 (or sqlite4) performance on NFS
On 11 Apr 2015, at 2:30pm, Peng Yu wrote: > I am wondering for my limited usage scenario, is it possible to make > sqlite3 on NFS as fast as sqlite3 on local storage (such as disable > file locking). No. Data will flow to a local disk much faster than it can flow across your network. But you can make network access faster using the following: Open the database READ_ONLY or use "PRAGMA query_only = YES". This may increase speed a little. Use the following: PRAGMA journal_mode = OFF; PRAGMA locking_mode = EXCLUSIVE; Experiment with values for the following command: PRAGMA cache_size = 1; The best values for this depend on complicated aspects of your hardware: the size of blocks on your disk, the size of packets on your network, the speed of the data bus in your computer, and other things. The default is 2000. You may find that bigger or smaller numbers make a difference. Or you may find they make no difference. Simon.
[sqlite] Select query becomes distinct on where matches unique with null value
On 2015-04-11 06:12 PM, Simon Slavin wrote: > On 11 Apr 2015, at 4:13pm, Keith Medcalf wrote: > >> Interestingly if you run analyze, it works properly ... > Oh my. I don't like the idea that ANALYZE changes the result set. > > Simon. Yes, that would be worrisome... but not to worry, the bug is in the index usage and analyze probably coaxed it into using another index that works - so not an ANALYZE bug. It's already checked in and getting fixed at: http://www.sqlite.org/src/info/e3b1f625518edc0e
[sqlite] SELECT * with ORDER BY returning duplicate rows.
This problem is also fixed on trunk. Thanks for the report. On 4/11/15, Stephen Lee wrote: > Hi > > > > I am using V3.8.9 of SQLite3.exe on Windows 7 (64bit). > > > > I have a table in a database which shows the following behaviour. > > The table, 'RawDataSamples', has columns ChannelID, SeqIndex and then > Sample_1 .. Sample_30. > > > > Select count(*) from RawDataSamples order by ChannelID, SeqIndex will > return > 69770. (Correct value) > > > > Select * from RawDataSamples order by ChannelID, SeqIndex will return 83665 > rows. > > > > Select * from RawDataSamples order by SeqIndex,ChannelID will return 69770 > rows. (Again correct) > > > > The ChannelID values are 1 .. 10, while the SeqIndex values are from 0 .. > 6976 for each ChannelID value. > > For ChannelID 1 .. 7, only Sample_0 has data, the other columns are null. > > For ChannelID 8 ..10, all columns have data. > > > > In the second query above, most, but not all, the results for ChannelID 9 > and 10 are duplicated. > > If I delete all other ChannelID values and just leave 9 and 10, then the > select will produce correct single values for ChannelID 9, but most results > for 10 will still be duplicated. > > If I delete ChannelID = 9 values, ChannelID 10 values are no longer > duplicated. > > > > I have other databases with the same structure which have fewer rows per > ChannelID value, and they are ok. It seems to be related to the number of > rows for the first order by column. > > > > The database file is at > https://www.dropbox.com/s/1qo0qdeyn71biq4/land.sldb?dl=0 > > > > I did look through the bug list, and if I missed this one then I apologise. > > > > Regards > > > > Steve > > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp drh at sqlite.org
[sqlite] Select query becomes distinct on where matches unique with null value
This problem is fixed on trunk. Thanks for the report. On 4/11/15, Mike Gladysch wrote: > Hi, > > I expected that SQLite misbehaves in current versions. Since 3.8.4.3 > (bundled with PHP 5.5.20 and PHP 5.6.4) all is still ok, but with next > update of PHP there is SQLite 3.8.7.2 bundled. > > Table: > 6 colums including an primary on col1, an unique on col2 (not null), an > unique on col3 nullable, col 4) and two more nullable colums. > > Data: > 1;1;null;test;null;null > 2;2;null;test;null;null > 3;3;null;test;null;null > 4;4;something;test;null;null > > Select col1, col2, col3, col4, col5, col6 > From table > Where col3 is null and col4 ='test' > > Delivers different results: > > 3.8.4.3: 3 rows (expected, ok) > 3.8.7.2: 1 row (wrong) > 3.8.9: 1 row (wrong) > > Tested with Windows shell binaries. > > Mike > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp drh at sqlite.org
[sqlite] sqlite3 (or sqlite4) performance on NFS
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 04/11/2015 06:30 AM, Peng Yu wrote: > I am wondering for my limited usage scenario, is it possible to > make sqlite3 on NFS as fast as sqlite3 on local storage (such as > disable file locking). The latency is what is getting you. SQLite uses synchronous/blocking I/O. What version of NFS are you using? NFSv4 has features that significantly reduce the effects of latency. If you want to maximize performance then I recommend writing your own VFS, which is very easy for read only. That will let you do read ahead and caching. Heck you could just copy the database locally and use that. One technique I used in the past (for WAN optimisers) is to record the access patterns for a newly opened file, and then on subsequent opens pre-read that same data. This was very effective across many file formats, but SQLite was not tested/relevant. It also meant not having to understand the actual file formats. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1 iEYEARECAAYFAlUpwCQACgkQmOOfHg372QSt1gCgzORbl2o18gKfkzMx8qph557L AQUAn0Rcog3QZbwWeAck8tFlira84xZU =dgFW -END PGP SIGNATURE-
[sqlite] Select query becomes distinct on where matches unique with null value
On 11 Apr 2015, at 4:13pm, Keith Medcalf wrote: > Interestingly if you run analyze, it works properly ... Oh my. I don't like the idea that ANALYZE changes the result set. Simon.
[sqlite] Select query becomes distinct on where matches unique with null value
Yep, it's a Bug since at least 3.8.8. Minimal Test-case to reproduce: create table t( c1 integer primary key, c2 integer, c3 integer, UNIQUE (c2, c3) ); insert into t values( 1,null,'a'); insert into t values( 2,null,'a'); insert into t values( 3,'xxx','a'); select * from t; -- c1 | c2 | c3 -- | - | - -- 1 | | a -- 2 | | a -- 3 | xxx | a select * from t where c2 is null and c3='a'; -- c1 | c2 | c3 -- | --- | - -- 1 | | a SQLite implements a Unique constraint as one that regards NULL values as different to all other values (including other NULL values), as does Postgres and MySQL (Some others don't). I am guessing the SQLite engine hits the Unique key on a lookup and doesn't check if there are any other values that satisfies the WHERE clause if the where clause seems specific (as in this case). It should change the behaviour when a NULL check is included. On 2015-04-11 08:07 AM, Mike Gladysch wrote: > Hi, > > I expected that SQLite misbehaves in current versions. Since 3.8.4.3 (bundled > with PHP 5.5.20 and PHP 5.6.4) all is still ok, but with next update of PHP > there is SQLite 3.8.7.2 bundled. > > Table: > 6 colums including an primary on col1, an unique on col2 (not null), an > unique on col3 nullable, col 4) and two more nullable colums. > > Data: > 1;1;null;test;null;null > 2;2;null;test;null;null > 3;3;null;test;null;null > 4;4;something;test;null;null > > Select col1, col2, col3, col4, col5, col6 > From table > Where col3 is null and col4 ='test' > > Delivers different results: > > 3.8.4.3: 3 rows (expected, ok) > 3.8.7.2: 1 row (wrong) > 3.8.9: 1 row (wrong) > > Tested with Windows shell binaries. > > Mike
[sqlite] Select query becomes distinct on where matches unique with null value
Mike Gladysch wrote: > Select col1, col2, col3, col4, col5, col6 > From table > Where col3 is null and col4 ='test' That is not valid SQL. > 3.8.4.3: 3 rows (expected, ok) > 3.8.7.2: 1 row (wrong) How to reproduce: create table t(x, y, unique(x, y)); insert into t values(null, 1); insert into t values(null, 1); select * from t where x is null and y = 1; |1 Regards, Clemens
[sqlite] Select query becomes distinct on where matches unique with null value
On 11 April 2015 at 07:07, Mike Gladysch wrote: > Hi, . > Data: > 1;1;null;test;null;null > 2;2;null;test;null;null > 3;3;null;test;null;null > 4;4;something;test;null;null > > Select col1, col2, col3, col4, col5, col6 > From table > Where col3 is null and col4 ='test' > > Delivers different results: > > 3.8.4.3: 3 rows (expected, ok) > 3.8.7.2: 1 row (wrong) > 3.8.9: 1 row (wrong) > > Tested with Windows shell binaries. > > Mike So I tried this: SQLite version 3.8.9 2015-04-08 12:16:33 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> sqlite> create table t( c1 integer primary key, c2 integer unique not null, c3 integer unique, c4 integer, c5 integer, c6 integer ); sqlite> insert into t values( 1,1,null,'test',null,null); sqlite> insert into t values( 2,2,null,'test',null,null); sqlite> insert into t values( 3,3,null,'test',null,null); sqlite> insert into t values( 4,4,'something','test',null,null); sqlite> select c1,c2,c3,c4,c5,c6 from t where c3 is null and c4='test'; 1|1||test|| 2|2||test|| 3|3||test|| sqlite> Am I doing anything different from you? Regards, Simon
[sqlite] Select query becomes distinct on where matches unique with null value
Interestingly if you run analyze, it works properly ... --- Theory is when you know everything but nothing works. Practice is when everything works but no one knows why. Sometimes theory and practice are combined: nothing works and no one knows why. >-Original Message- >From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users- >bounces at mailinglists.sqlite.org] On Behalf Of R.Smith >Sent: Saturday, 11 April, 2015 06:09 >To: sqlite-users at mailinglists.sqlite.org >Subject: Re: [sqlite] Select query becomes distinct on where matches >unique with null value > >Yep, it's a Bug since at least 3.8.8. > >Minimal Test-case to reproduce: > >create table t( > c1 integer primary key, > c2 integer, > c3 integer, > UNIQUE (c2, c3) >); >insert into t values( 1,null,'a'); >insert into t values( 2,null,'a'); >insert into t values( 3,'xxx','a'); > >select * from t; > -- c1 | c2 | c3 > -- | - | - > -- 1 | | a > -- 2 | | a > -- 3 | xxx | a > >select * from t where c2 is null and c3='a'; > -- c1 | c2 | c3 > -- | --- | - > -- 1 | | a > > >SQLite implements a Unique constraint as one that regards NULL values as >different to all other values (including other NULL values), as does >Postgres and MySQL (Some others don't). > >I am guessing the SQLite engine hits the Unique key on a lookup and >doesn't check if there are any other values that satisfies the WHERE >clause if the where clause seems specific (as in this case). It should >change the behaviour when a NULL check is included. > > > >On 2015-04-11 08:07 AM, Mike Gladysch wrote: >> Hi, >> >> I expected that SQLite misbehaves in current versions. Since 3.8.4.3 >(bundled with PHP 5.5.20 and PHP 5.6.4) all is still ok, but with next >update of PHP there is SQLite 3.8.7.2 bundled. >> >> Table: >> 6 colums including an primary on col1, an unique on col2 (not null), an >unique on col3 nullable, col 4) and two more nullable colums. >> >> Data: >> 1;1;null;test;null;null >> 2;2;null;test;null;null >> 3;3;null;test;null;null >> 4;4;something;test;null;null >> >> Select col1, col2, col3, col4, col5, col6 >> From table >> Where col3 is null and col4 ='test' >> >> Delivers different results: >> >> 3.8.4.3: 3 rows (expected, ok) >> 3.8.7.2: 1 row (wrong) >> 3.8.9: 1 row (wrong) >> >> Tested with Windows shell binaries. >> >> Mike > >___ >sqlite-users mailing list >sqlite-users at mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sqlite3 (or sqlite4) performance on NFS
Hi, I know that sqlite3 is not recommended to be used on NFS. But I only use sqlite3 for read only (the only time that I write is to load the initial data to the data base). With this restriction, NFS should be fine as the storage since no file locking is needed. I remember that sqlite3 on NFS is significantly slower than on a local disk (a few times slower when I tested a few years ago). I am wondering for my limited usage scenario, is it possible to make sqlite3 on NFS as fast as sqlite3 on local storage (such as disable file locking). Thanks. -- Regards, Peng
[sqlite] Select query becomes distinct on where matches unique with null value
Hi, I expected that SQLite misbehaves in current versions. Since 3.8.4.3 (bundled with PHP 5.5.20 and PHP 5.6.4) all is still ok, but with next update of PHP there is SQLite 3.8.7.2 bundled. Table: 6 colums including an primary on col1, an unique on col2 (not null), an unique on col3 nullable, col 4) and two more nullable colums. Data: 1;1;null;test;null;null 2;2;null;test;null;null 3;3;null;test;null;null 4;4;something;test;null;null Select col1, col2, col3, col4, col5, col6 >From table Where col3 is null and col4 ='test' Delivers different results: 3.8.4.3: 3 rows (expected, ok) 3.8.7.2: 1 row (wrong) 3.8.9: 1 row (wrong) Tested with Windows shell binaries. Mike
[sqlite] Request: Metadata about C API constants and functions
Thanks, Richard I found (https://www.sqlite.org/docsrc/timeline) & https://www.sqlite.org/docsrc/artifact/5c48dd261dbe5804 very useful. Last year I was parsing 100M of XML stuff and loading it into an Sqlite database. A bit of hammering required as the XML had minor errors like missing end tags. "malformed" In a meeting, we were discussing a very specific piece of functionality. "How many times is this function used?" One guy said "About 300 times" by I was able to answer 2365 times, within 30 seconds, just some simple SQL. Incidentally, it was far easier to regenerate the XML from the Sqlite, similar to a comma delimited format, without all the "malformed" problems. The malformed XML was targeted to be stored in Git. regs, Kev
