Re: [sqlite] [sqlite TOO slow

I used the following syntax to create index "CREATE UNIQUE INDEX persons_1x ON persons (pid,hid);" Gaurav On Thu, Nov 24, 2011 at 1:54 AM, Petite Abeille wrote: > > On Nov 24, 2011, at 8:48 AM, Simon Slavin wrote: > > > Is the table indexed on that column ? > > And if

Re: [sqlite] [sqlite TOO slow

On Nov 24, 2011, at 8:48 AM, Simon Slavin wrote: > Is the table indexed on that column ? And if it is... what's its selectivity? What 's the query plan? ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] [sqlite TOO slow

I am using multi threads but using single thread to test the speed. And when I do typeof(hid), it gives "real". Table is indexed on that column. I dont have a primary key, will that make any difference? Gaurav On Thu, Nov 24, 2011 at 1:48 AM, Simon Slavin wrote: > > On

Re: [sqlite] [sqlite TOO slow

On 24 Nov 2011, at 7:45am, Gaurav Vyas wrote: > I am trying to get a chunk of rows from a table which has 16 million rows. > The table is indexed. I am passing the query as "SELECT * FROM persons > WHERE hid = 5;" and it takes a few minutes to get me the results. Can > anyone suggest how to make

Re: [sqlite] [sqlite TOO slow

I am trying to get a chunk of rows from a table which has 16 million rows. The table is indexed. I am passing the query as "SELECT * FROM persons WHERE hid = 5;" and it takes a few minutes to get me the results. Can anyone suggest how to make it faster? Gaurav

Re: [sqlite] Free c code for embedded sqlite3

Docs would help people understand what you're up to... Nico -- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Free c code for embedded sqlite3

A base of code that implements about every embedded function for sqlite3, source code on my blog. It is a triple machine, looks at the world as ripples for ontology. I tries to follow SQLITE standards. The control program pops triple off of the configure table and executes them, installing more

Re: [sqlite] Bug

On Nov 23, 2011, at 8:50 PM, Wiktor Adamski wrote: > ISO/IEC 9075-2:2003: > ::= OVER specification> This is related to so-called analytics in Oracle parlance. Not quite related to the topic at hand. ___ sqlite-users mailing list

Re: [sqlite] Bug

> I believe OVER() is an Oracle-specific extension to SQL, not a > standard in any way. ISO/IEC 9075-2:2003: ::= OVER ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug

On Nov 23, 2011, at 7:58 PM, Pavel Ivanov wrote: > I believe OVER() is an Oracle-specific extension to SQL, not a > standard in any way. Well, over( partition by... order by ... ) is part of the analytical syntax of Oracle... nothing to do with ordering a result set... Function(arg1,...,

Re: [sqlite] Bug

On Wed, Nov 23, 2011 at 1:29 PM, Wiktor Adamski wrote: >> Apparently, using such a function in ORDER BY >> clause alone doesn't make the statement aggregate (whether it should is >> perhaps debatable) > > I suppose this may be in the standart. I'm 100% sure that this

Re: [sqlite] Bug

On 11/23/2011 1:29 PM, Wiktor Adamski wrote: Apparently, using such a function in ORDER BY clause alone doesn't make the statement aggregate (whether it should is perhaps debatable) I suppose this may be in the standart. If I recall correctly, the standard doesn't allow ORDER BY to reference

Re: [sqlite] SQLite Locking Issue From Another Process

On 22 Nov 2011, at 3:07pm, David Levinson wrote: > So my basic question is why is Process B locking the database when it is > opening the file for read-only access and not performing any writes on > the database being written to by Process A. > > > > Is it possible that a select statement

Re: [sqlite] SQLite Locking Issue From Another Process

You need WAL mode http://www.sqlite.org/draft/wal.html Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org

Re: [sqlite] Bug

> Apparently, using such a function in ORDER BY > clause alone doesn't make the statement aggregate (whether it should is > perhaps debatable) I suppose this may be in the standart. I'm 100% sure that this one is allowed by standart: ... ORDER BY avg(a) OVER() so likely ORDER BY avg(a) is also

[sqlite] SQLite Locking Issue From Another Process

We have Process A which writes constantly to our SQLite database and we have Process B which occasionally reads from this same database. When Process B opens the database for read-only access and performs a select statement on the database it causes Process A to get SQLITE_BUSY errors when

Re: [sqlite] Bug

>   In the first query, there is an aggregate in the result set, so an >   implicit GROUP BY is used.  The ORDER BY is meaningless, but not an >   error (and could be more easily written "ORDER BY 1"; see below). The order is not meaningless. It can return an error or do nothing. If aggregate in

Re: [sqlite] Bug

On Wed, Nov 23, 2011 at 08:17:17AM -0800, Wiktor Adamski scratched on the wall: > SQLite version 3.7.9 2011-11-01 00:52:41 > Enter ".help" for instructions > Enter SQL statements terminated with a ";" > sqlite> create table t(a); > sqlite> select avg(a) from t order by avg(a); > -- order by

Re: [sqlite] Bug

On Nov 23, 2011, at 6:28 PM, Pavel Ivanov wrote: > No, it's not a bug. It's SQL standard that such form of aggregate > query always returns one row. And when there's no rows in the table it > should return NULL (for all aggregate functions except count() which > returns 0). I said it's kind of

Re: [sqlite] Bug

On Wed, Nov 23, 2011 at 12:20 PM, Petite Abeille wrote: > On Nov 23, 2011, at 6:05 PM, Pavel Ivanov wrote: > >>  This query gives >> different and kind of unexpected result on empty table. ;) > > Ooops... I see what you mean... on an empty table... this returns one row

Re: [sqlite] Bug

On 11/23/2011 11:17 AM, Wiktor Adamski wrote: SQLite version 3.7.9 2011-11-01 00:52:41 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> create table t(a); sqlite> select avg(a) from t order by avg(a); -- order by aggregate possible sqlite> select 1 from t

Re: [sqlite] Bug

On Nov 23, 2011, at 6:05 PM, Pavel Ivanov wrote: > This query gives > different and kind of unexpected result on empty table. ;) Ooops... I see what you mean... on an empty table... this returns one row with a null value: sqlite> select max( 1 ) from t; That would qualify as a bug I guess

Re: [sqlite] Bug

On Nov 23, 2011, at 6:05 PM, Pavel Ivanov wrote: >> Well, if this is about Alice in Wonderland, then, what about: >> >>> select max( 1 ) from t order by avg( a ); >> 1 > > Well, apparently you did this on non-empty table. This query gives > different and kind of unexpected result on empty

Re: [sqlite] Bug

On Wed, Nov 23, 2011 at 11:50 AM, Petite Abeille wrote: > On Nov 23, 2011, at 5:35 PM, Pavel Ivanov wrote: > >> But although it's completely senseless >> just syntactically it looks correct - should produce just one row and >> thus ORDER BY will be a no-op. > > Well, if

Re: [sqlite] Bug

On Nov 23, 2011, at 5:35 PM, Pavel Ivanov wrote: > But although it's completely senseless > just syntactically it looks correct - should produce just one row and > thus ORDER BY will be a no-op. Well, if this is about Alice in Wonderland, then, what about: > select max( 1 ) from t order by

Re: [sqlite] Bug

> And FWIW, this query works as expected on MS SQL Works on Firebird and produces one record with value 1. RBS On Wed, Nov 23, 2011 at 4:35 PM, Pavel Ivanov wrote: > On Wed, Nov 23, 2011 at 11:28 AM, Simon Slavin wrote: >> On 23 Nov 2011, at 4:17pm,

Re: [sqlite] Bug

On Wed, Nov 23, 2011 at 11:28 AM, Simon Slavin wrote: > On 23 Nov 2011, at 4:17pm, Wiktor Adamski wrote: > >> sqlite> select 1 from t order by avg(a); -- should be possible > > Why should this be possible ?  For an 'ORDER BY' you need a value for each > row.  But aggregate

Re: [sqlite] Bug

On Nov 23, 2011, at 5:17 PM, Wiktor Adamski wrote: > sqlite> select 1 from t order by avg(a); -- should be possible > Error: misuse of aggregate: avg(); As it says on the tin: nonsensical. ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] Bug

On 23 Nov 2011, at 4:17pm, Wiktor Adamski wrote: > sqlite> select 1 from t order by avg(a); -- should be possible Why should this be possible ? For an 'ORDER BY' you need a value for each row. But aggregate functions produce only one value for the whole SELECT command. Simon.

[sqlite] Bug

SQLite version 3.7.9 2011-11-01 00:52:41 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> create table t(a); sqlite> select avg(a) from t order by avg(a); -- order by aggregate possible sqlite> select 1 from t order by a; -- order by column not in result possible

Re: [sqlite] SQLite: Database or disk full

On Wed, Nov 23, 2011 at 2:10 AM, Gaurav Vyas wrote: > There is no optimization as of now. I am just slitting the code into > various independent parts. And one more thing I found, I have installed > SQLite3 3.7.9 and when I am using sqlite3_open_v2 it gives error that says >

[sqlite] updating table only if key match in other table

update a set location='new york ' where a.location isnull and a.mz_tik in(select mz_tik from c); -- View this message in context: http://old.nabble.com/updating-table-only-if-key-match-in-other-table-tp32873978p32873978.html Sent from the SQLite mailing list archive at Nabble.com.

Re: [sqlite] Smart merge

nadavius wrote: > I have two tables T1, T2 - identical in fields [Index1 (INT), Index2(INT), > Count(INT), Value(INT)] > I would like to merge the content of T1 into T2 using the following ruels: > 1. Copy into T2 data from T1 where Index1 AND Index2 (like two keys, PK and >

Re: [sqlite] Smart merge

On 23 November 2011 11:23, nadavius wrote: > > Hi guys, > > I have two tables T1, T2 - identical in fields [Index1 (INT), Index2(INT), > Count(INT), Value(INT)] > I would like to merge the content of T1 into T2 using the following ruels: > 1. Copy into T2 data from T1 where

[sqlite] Smart merge

Hi guys, I have two tables T1, T2 - identical in fields [Index1 (INT), Index2(INT), Count(INT), Value(INT)] I would like to merge the content of T1 into T2 using the following ruels: 1. Copy into T2 data from T1 where Index1 AND Index2 (like two keys, PK and SK) do not exist in T2 2. In case

[sqlite] Smart merge

Hi guys, I have two tables T1, T2 - identical in fields [Index1 (INT), Index2(INT), Count(INT), Value(INT)] I would like to merge the content of T1 into T2 using the following ruels: 1. Copy into T2 data from T1 where Index1 AND Index2 (like two keys, PK and SK) do not exist in T2 2. In case