Re: AW: [sqlite] Bad performance with large joins?

Jim Dodgen <[EMAIL PROTECTED]> wrote: > > 2. get the optimizer to ignore indexes that I know cannot help > >I do this in the where clause, by adding a zero or concatenating a "" > depending upon the data type > > examples: > > where sex = "M" > changed to > where sex||"" = "M"

Re: AW: [sqlite] Bad performance with large joins?

As has been stated in the past SQLite is a small foot print RDBMS, one of the things keeping it small is that it does not have a large query optimizer. One of the things that can happen is if you have lots of indexes the optimizer may pick something non optimum My tricks are as follows: 1.

Re: [sqlite] Column names in a UNION

Same is true for Oracle 8i/9i/10g. E. --- Rob Lohman <[EMAIL PROTECTED]> wrote: > Microsoft SQL seems to use the column names from the > first select. So that would be "a, b" in your example. > > Rob > > - Original Message - > From: <[EMAIL PROTECTED]> > To: "Sqlite-users"

Re: [sqlite] Column names in a UNION

Oracle 8i: SQL> select 1 as a, 2 as b from dual UNION ALL select 3 as b, 4 as a from dual; A B -- -- 1 2 3 4 SQL> select * from (select 1 as a, 2 as b from dual UNION ALL select 3 as b, 4 as a from dual) where b =

Re: [sqlite] Wierd between results

Yeah I did. sorry about the rushed typing. DRH managed to sort it out for me. I had imported a ver 2 database to version 3 and changed a time values to integers, but forgot to change the column to integer. Lloydie-T - Original Message - From: "Kurt Welgehausen" <[EMAIL PROTECTED]>

Re: [sqlite] Wierd between results

"Lloyd Thomas" <[EMAIL PROTECTED]> wrote: > if use > SELECT count(call_id) as num_rows WHERE ring_time BETWEEN 6 and 10; > I get 0 results You DID specify a table in your actual queries, didn't you? select * from tbl; t x y -- -- -- 1

Re: [sqlite] Column names in a UNION

On 3/25/06, Joe Wilson <[EMAIL PROTECTED]> wrote: > Since we're on this topic, what do other databases return for these queries? > > sqlite> select 1 as a, 2 as b UNION ALL select 3 as b, 4 as a; > a|b > 1|2 > 3|4 mysql> select 1 as a, 2 as b UNION ALL select 3 as b, 4 as a; +---+---+ | a

Re: [sqlite] Questions to autocommit mode

Markus Kolb <[EMAIL PROTECTED]> wrote: > > I am using autocommit and I call sqlite3_exec with a SQL-INSERT inside a > sqlite_callback()-function which gets called for each row of a > SQL-SELECT (sqlite3_exec, too). > > So again my question what can I do to get the data written to DB file or >

Re: [sqlite] Questions to autocommit mode

[EMAIL PROTECTED] wrote: > Markus Kolb <[EMAIL PROTECTED]> wrote: >> Hello, >> >> I have some questions to autocommit mode of SQLite 3.3.4 and its >> transactions. >> >> Did I understand it right that new or changed row data is only written >> to disk when the db connection is closed with

Re: [sqlite] Column names in a UNION

Since we're on this topic, what do other databases return for these queries? sqlite> select 1 as a, 2 as b UNION ALL select 3 as b, 4 as a; a|b 1|2 3|4 sqlite> select * from (select 1 as a, 2 as b UNION ALL select 3 as b, 4 as a) where b = 3; b|a 3|4 sqlite> select * from

Re: [sqlite] Wierd between results

"Lloyd Thomas" <[EMAIL PROTECTED]> wrote: > I am not sure if I am being crazy, but I seem to be getting a wierd result > when using 'BETWEEN'. > > if use > SELECT count(call_id) as num_rows WHERE ring_time BETWEEN 6 and 10; > I get 0 results > but if I do > SELECT count(call_id) as num_rows

Re: [sqlite] Wierd between results

Go back to my original problem if I change 'between 6 and 10' to 'between 6 and 9', I get the expected results. SELECT count(call_id) as num_rows WHERE ring_time BETWEEN 6 and 9; 232 Am I finding a bug or is my syntax incorrect? - Original Message - From: "Lloyd Thomas" <[EMAIL

Re: [sqlite] Wierd between results

If i do SELECT count(call_id) as num_rows WHERE ring_time BETWEEN 10 and 6; I get 633 results. Is that normal? - Original Message - From: "Lloyd Thomas" <[EMAIL PROTECTED]> To: Sent: Saturday, March 25, 2006 3:48 PM Subject: [sqlite] Wierd between results I

Re: [sqlite] Column names in a UNION

On 3/25/06, Rob Lohman <[EMAIL PROTECTED]> wrote: > Microsoft SQL seems to use the column names from the > first select. So that would be "a, b" in your example. This seems to be the case for current stable versions of Firebird, Postgres and MySQL as well. -- Nemanja Corlija <[EMAIL PROTECTED]>

Re: [sqlite] Unaligned access in SQLite on Itanium

> Perhaps a better fix is this: > > struct Mem { >i64 i; >double r; >char *z; >int n; >u16 flags; >u8 type; >u8 enc; >void (*xDel)(void *); >union { > long double notUsed1; > char zShort[NBFS]; >}; > }; > > The compiler would then (hopefully)

[sqlite] Wierd between results

I am not sure if I am being crazy, but I seem to be getting a wierd result when using 'BETWEEN'. if use SELECT count(call_id) as num_rows WHERE ring_time BETWEEN 6 and 10; I get 0 results but if I do SELECT count(call_id) as num_rows WHERE ring_time = 7; I get 39 results SELECT count(call_id)

Re: [sqlite] Questions to autocommit mode

Markus Kolb <[EMAIL PROTECTED]> wrote: > Hello, > > I have some questions to autocommit mode of SQLite 3.3.4 and its > transactions. > > Did I understand it right that new or changed row data is only written > to disk when the db connection is closed with sqlite3_close? No. Data is guaranteed

[sqlite] Questions to autocommit mode

Hello, I have some questions to autocommit mode of SQLite 3.3.4 and its transactions. Did I understand it right that new or changed row data is only written to disk when the db connection is closed with sqlite3_close? Did I understand it right that after a transaction commit, autocommit is

Re: [sqlite] Column names in a UNION

Microsoft SQL seems to use the column names from the first select. So that would be "a, b" in your example. Rob - Original Message - From: <[EMAIL PROTECTED]> To: "Sqlite-users" Sent: Saturday, March 25, 2006 3:45 PM Subject: [sqlite] Column names in a UNION

[sqlite] Column names in a UNION

Who can tell me what the "correct" column names should be for a UNION. For example: SELECT a, b FROM t1 UNION SELECT x, y FROM t2; The result set of the query above has two columns. Should those columns be named a and b or x and y? Does anybody know what the SQL standard says? Do all the

Re: [sqlite] Unaligned access in SQLite on Itanium

"Alexei Alexandrov" <[EMAIL PROTECTED]> wrote: > > > > Perhaps the following definition of Mem would work better: > > > > struct Mem { > >u16 flags; > >u8 type; > >u8 enc; > >char *z; > >int n; > >i64 i; > >double r; > >char zShort[NBFS]; > >void (*xDel)(void

Re: [sqlite] SQLite: which platforms are supported?

"Alexei Alexandrov" <[EMAIL PROTECTED]> wrote: > > A side note: from previous experience with some other DB engines I > know that sometimes alignment issues are not minor at all. They can be > deeply in on-disk format and sometimes it is difficult to avoid them > without having to change the

Re: [sqlite] SQLite: which platforms are supported?

> > From your subsequent posts, I presume that you are figuring out > for yourself that there are no showstoppers, though perhaps some > minor alignment issues on ia64. > > I personally test each release on ix86 Linux (currently SuSE 9.2), > on Win2K (running under VMWare on the afore mentioned

Re: [sqlite] SQLite: which platforms are supported?

"Alexei Alexandrov" <[EMAIL PROTECTED]> wrote: > Hi, > > We consider using SQLite for some of our applications and I would like to a= > sk > whether there are existing cases of using SQLite on the following OSes: > > - Mac > - Windows > - Linux > > and with following architectures > > - x86 >

Re: [sqlite] SQLite tests failures on Linux IA64

"Alexei Alexandrov" <[EMAIL PROTECTED]> wrote: > I use those that come with the distribution. Since it's one of the > latest distros (Red Hat EL 4 Update 2), it's not that archaic: > > $ rpm -qa | grep tcl > tcl-devel-8.4.7-2 > tcl-8.4.7-2 That should be sufficient. > > > > > > printf-8.1... >

Re: [sqlite] Unaligned access in SQLite on Itanium

"Alexei Alexandrov" <[EMAIL PROTECTED]> wrote: > As far as I understand, this fix will take the problem away only > because SumCtx is bigger now. But the problem with > sqlite3_aggregate_context will remain: the pointer returned will not > be aligned properly if the size of the context is less or

Re: [sqlite] Unaligned access in SQLite on Itanium

As far as I understand, this fix will take the problem away only because SumCtx is bigger now. But the problem with sqlite3_aggregate_context will remain: the pointer returned will not be aligned properly if the size of the context is less or equal 32 bytes. Am I correct? On 3/25/06, [EMAIL

Re: [sqlite] Unaligned access in SQLite on Itanium

"Alexei Alexandrov" <[EMAIL PROTECTED]> wrote: > Hi, > > I don't know whether it's been already reported or not, so anyway. > There are places in SQLite where unaligned access exception is > generated on Itanium. The unaligned access means that someone tries to > read or write memory crossing

Re: [sqlite] SQLite tests failures on Linux IA64

"Alexei Alexandrov" <[EMAIL PROTECTED]> wrote: > Hi, > > I'm running SQLite 3.3.4 tests suite on Linux (Red Hat 4 Update 2) > Itanium box. I got the following test failures: > > 4 errors out of 24863 tests > Failures on these tests: printf-8.1 printf-8.2 printf-9.5 types3-1.3 > > Information on

[sqlite] Re: Unaligned access in SQLite on Itanium

Also, I've verified that this is exactly the reason of unaligned accesses by making SumCtx bigger so that it doesn't fit into NBFS bytes. After that I was able to execute all tests without unaligned access exceptions. But those printf-8.1 printf-8.2 printf-9.5 types3-1.3 still fail, so it is not

[sqlite] Unaligned access in SQLite on Itanium

Hi, I don't know whether it's been already reported or not, so anyway. There are places in SQLite where unaligned access exception is generated on Itanium. The unaligned access means that someone tries to read or write memory crossing 8-bytes boundary. Usually this occur as a result of pointers

[sqlite] SQLite tests failures on Linux IA64

Hi, I'm running SQLite 3.3.4 tests suite on Linux (Red Hat 4 Update 2) Itanium box. I got the following test failures: 4 errors out of 24863 tests Failures on these tests: printf-8.1 printf-8.2 printf-9.5 types3-1.3 Information on the console: printf-8.1... Error: integer value too large to

[sqlite] Warnings during compilation on Linux ia64

Hi, I compile SQLite 3.3.4 on Linux ia64 box and noticed the following warnings during the compilation: ./src/table.c: In function `sqlite3_get_table': ./src/table.c:149: warning: cast to pointer from integer of different size ./src/table.c: In function `sqlite3_free_table': ./src/table.c:194: