[sqlite] upgrade from 2 to 3

[Cronos]

I've just upgraded my program from using v2.8.16 of sqlite to using v3.2.1
of sqlite. So I've change the wrapper class that I was using to use the new
prepare() instead of compile() etc. The problem I have is that my program is
now running slower - a lot slower, as in one fiftieth of its previous speed.
Is there something obvious that I have overlooked in the changes ? I've
added indexes to tables and synchronous is now off but its made no
improvement.

[sqlite] Re: upgrade from 2 to 3

[Cronos]

Nevermind, it seems I have found the cause, I was doing:
sqlthread->exec("PRAGMA synchronous = 0;",NULL,NULL);
sqlthread->exec("PRAGMA cache_size = 4000;",NULL,NULL);

but changing it to this has sorted the problem out:
sqlthread->exec("PRAGMA cache_size = 4000;",NULL,NULL);
sqlthread->exec("PRAGMA synchronous = 0;",NULL,NULL);

It seems the value of synchronous gets changed back to full if you change
the cache_size.

RE: [sqlite] qualified names in WHERE clause

[Cronos]

It seems to me that MySQL and PostgreSQL are exhibitting some dubious
guessing behaviour as to which column it refers to, or perhaps they are
making some requirement of the order by to contain a column that is in the
resultset ??? If name were only in test11 then what would MySQL and
PostgreSQL do ?

-Original Message-
From: Will Leshner [mailto:[EMAIL PROTECTED]
Sent: 26 May 2005 15:18
To: Forum SQLite
Subject: [sqlite] qualified names in WHERE clause


I guess I never really noticed this before (since I only use SQLite,
of course :) ). But consider a query like this:

SELECT test2.* FROM test2,test11 WHERE test2.id=test11.id ORDER BY name

If the 'name' column happens to be a column in both test2 and test11,
then SQLite will return an error. You need to qualify 'name' with
'test2.' to make the query acceptable. Apparently MySQL and
PostgreSQL are able to recognize that 'name' refers to the 'name' in
the result set and are able to disambiguate it.

RE: [sqlite] Changing default PRAGMA SYNCHRONOUS at compile time

[Cronos]

>> While I can send 'set pragma synchronous=normal' at every write ...
>
> You don't have to execute the pragma for every write.
> Once you've set synchronous=normal, it stays normal
> until you close and reopen the database.

Not strictly true, in 3.2.1 it gets reset if you change the cache_size - see
ticket 1260, just been fixed I think.

RE: [sqlite] group by to return correlated results

[Cronos]

Shouldn't it be:

   select name, day, distance
   from (
 select name as max_name,max(distance) as max_distance
 from t
 group by name
   ) as foo inner join t on t.distance = foo.max_distance and
t.name=foo.max_name

and this still returns two rows when someone has run the same distance on
more than one day - perhaps this is or is not what you want?

-Original Message-
From: Darren Duncan [mailto:[EMAIL PROTECTED]
Sent: 22 June 2005 07:14
To: sqlite-users@sqlite.org; Al Danial
Subject: Re: [sqlite] group by to return correlated results


A simple answer is to use a subquery:

   select name, day, distance
   from (
 select max(distance) as max_distance
 from t
 group by name
   ) as foo inner join t on t.distance = foo.max_distance

My exact syntax may be off (eg, the second 'as' may need removing)
but otherwise you should be able to use that.

-- Darren Duncan

At 1:35 AM -0400 6/22/05, Al Danial wrote:
>This table keeps track of how far two people ran in
>a given week:
>
>create table t(name, day, distance);
>
>insert into  t values("al", "monday"   ,  4.0);
>insert into  t values("al", "tuesday"  ,  4.1);
>insert into  t values("al", "wednesday",  5.5);
>insert into  t values("al", "thursday" ,  2.3);
>insert into  t values("al", "friday"   ,  8.1);
>insert into  t values("al", "saturday" ,  2.2);
>insert into  t values("ed", "thursday" , 14.0);
>insert into  t values("ed", "sunday"   , 18.4);
>
>I want to show the name and day that corresponds to
>each person's maximum distance.  I know I can use
>'group by' like so:
>
>sqlite> select name,max(distance) from t group by name;
>namemax(distance)
>--  --
>al  8.1
>ed  18.4
>
>which is fine.  But if I also want to know the day of
>the week when the maximum occurred for each person
>then things go haywire:
>
>sqlite> select name,day,max(distance) from t group by name;
>nameday max(distance)
>--  --  --
>al  monday  8.1
>ed  thursday18.4
>
>which isn't right because the day shown here isn't the day
>when the maximum occurred.  How would I rewrite the query
>so that I'd see this result:
>
>nameday max(distance)
>--  --  --
>al  friday   8.1
>ed  sunday 18.4
>
>   ?-- Al

RE: [sqlite] Multiple inner join confusion

[Cronos]

I am also having difficulty getting to grips with the (awkward) join
notation. In 3.0 how about support for Oracle type joins with (+) which
seems far simpler to me :)

-Original Message-
From: D. Richard Hipp [mailto:[EMAIL PROTECTED]
Sent: 12 April 2004 13:00
Cc: [EMAIL PROTECTED]
Subject: Re: [sqlite] Multiple inner join confusion


Rob Duncan wrote:
> I'm a novice SQL user, and I'm confused by an apparently arbitrary
> limitation on multiple inner joins.
>
> select * from aa inner join bb using (i) inner join cc using (j);
> SQL error: cannot join using column j - column not present in both tables
>

I suggest you work around the problem by coding the select like this:

select * from aa, bb, cc where aa.i=bb.i and cc.j=aa.j;


--
D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565



-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]