Re: [sqlite] Multi column ORDER BY across table peformance problem....

[Harold Wood]

how about
 

select Starttime
 from channel C
 inner join (select showing.startTime AS startTime,
    showing.stationId AS stationId
    FROM showing
   WHERE showing.startTime >= 123923
     ORDER BY showing.startTime
     LIMIT 8 ) AS s
   on S.StationId = C.stationId
 ORDER BY s.startTime, c.ChannelMajorNumber
 limit 8
 


--- On Wed, 4/22/09, sorka  wrote:

From: sorka 
Subject: Re: [sqlite] Multi column ORDER BY across table peformance problem
To: sqlite-users@sqlite.org
Date: Wednesday, April 22, 2009, 1:11 AM

Um, well, except that I'd have huge gaping holes. i.e. say there are 100
shows at or after the specified time all on different channels. The above
query would limit to the first 8 showings on or after that time *before*
sorting by channel number. 



Jeremy Hinegardner wrote:
> 
> How about trying:
> 
>   SELECT s.startTime
> FROM ( SELECT  showing.startTime AS startTime
>   ,showing.stationId AS stationId
>   FROM showing
>  WHERE showing.startTime >= 123923
>   ORDER BY showing.startTime
>LIMIT 8 ) AS s
> JOIN channel AS c
>   ON s.stationId = c.stationId
> ORDER BY s.startTime, c.ChannelMajorNumber
> 
> I believe this should limit your table of 100,000 rows down to just the 8
> you
> want on the inner query, and then join that against the 100 rows in the
> channel
> table.
> 
> Although, to tell you the truth, I don't see the purpose in your
original
> query:
> 
> SELECT showing.startTime 
>   FROM showing 
>   JOIN channel 
> ON showing.startTime >= 123923 
>AND showing.stationId = channel.stationId
>   ORDER BY showing.startTime, channel.ChannelMajorNumber 
>  LIMIT 8;
> 
> You are not doing anything with the channel table data other than joining
> it
> agains the showing tables.  To me that means you are joining against the
> channel
> table in order to filter out rows in the showing table that have
> stationId's
> that do not exist in the channel table.  
> 
> Is that correct?  If that is correct, then the query I gave will not do
> what you
> want.  
> 
> Can the goal you are attempting be stated as?
> 
> Find next or earliest showings from the showings table for channels in
> the
> channel table?
> 
> If this is the case, maybe this query will work:
> 
>   SELECT showing.startTime
> FROM showing
>WHERE showing.startTime >= 123923000
>  AND showing.stationId IN ( SELECT DISTINCT channel.stationId FROM
> channel )
>LIMIT 8;
> 
> enjoy,
> 
> -jeremy
> 
>  
> On Tue, Apr 21, 2009 at 08:58:56PM -0700, sorka wrote:
>> 
>> Writing the query as you said you would returns results in nearly
>> instantly,
>> within a few ms. 
>> 
>> The problem is when you add a secondary ordering field. The intended
>> indices
>> are being used.
>> 
>> The problem, as I've explained several times already is that there
is no
>> way
>> to create a multicolumn index across tables. Sqlite is using the
>> stationId
>> index on the channel table to join showings via the stationId. The
query
>> plan shows it clearly. When ordering by startTime or even startTime
and
>> stationId, the results are returned in a few ms. But when adding
channel
>> number(i.e startTime and then channel number), it jumps to two
minutes.
>> This
>> is because sqlite is bringing in nearly 100K records from the showing
>> table,
>> sorted by time, because of the startTime index, before it starts
sorting
>> by
>> channel number. It would sure be nice to be able to get sqlite to sort
on
>> the channel number for each time group. i.e. once all the 10:00 PM
>> showings
>> have been returned and the first 10:30 PM showing is about to be
>> returned,
>> sort all of the 10:00 PM showings by channel number. Sqlite is NOT
doing
>> this but should. 
>> 
>> At this point, I'm going to assume that this is just a limitation
in the
>> sqlite engine and I'll have to proceed in other ways to solve this
issue.
>> 
>> 
>> 
>> Ian Walters wrote:
>> > 
>> >> SELECT showing.startTime FROM showing JOIN channel ON  
>> >> showing.startTime >=
>> >> 123923 AND showing.stationId = channel.stationId ORDER BY
>> >> showing.startTime LIMIT 8;
>> > 
>> > I don't know if it would be faster... but I would have
written that.
>> > 
>> > SELECT showing.startTime FROM showing JOIN channel USING
(stationId)  
>> > WHERE startTime < 123923 ORDER BY startTime LIMIT 8.
>> > 
>> > Also I know in the latest version of SQLite its possible to
'hint'  
>> > what indexes should be used, which might be helpful.  There is
also  
>> > something on the contrib page that lets you check a query to see
what  
>> > indexes it does use.
>> > 
>> > Sorry if the above lacks detail, its kinda a busy day.
>> > 
>> > --
>> > Ian
>> > ___
>> > sqlite-users mailing l

Re: [sqlite] Multi column ORDER BY across table peformance problem....

[sorka]

Um, well, except that I'd have huge gaping holes. i.e. say there are 100
shows at or after the specified time all on different channels. The above
query would limit to the first 8 showings on or after that time *before*
sorting by channel number. 



Jeremy Hinegardner wrote:
> 
> How about trying:
> 
>   SELECT s.startTime
> FROM ( SELECT  showing.startTime AS startTime
>   ,showing.stationId AS stationId
>   FROM showing
>  WHERE showing.startTime >= 123923
>   ORDER BY showing.startTime
>LIMIT 8 ) AS s
> JOIN channel AS c
>   ON s.stationId = c.stationId
> ORDER BY s.startTime, c.ChannelMajorNumber
> 
> I believe this should limit your table of 100,000 rows down to just the 8
> you
> want on the inner query, and then join that against the 100 rows in the
> channel
> table.
> 
> Although, to tell you the truth, I don't see the purpose in your original
> query:
> 
> SELECT showing.startTime 
>   FROM showing 
>   JOIN channel 
> ON showing.startTime >= 123923 
>AND showing.stationId = channel.stationId
>   ORDER BY showing.startTime, channel.ChannelMajorNumber 
>  LIMIT 8;
> 
> You are not doing anything with the channel table data other than joining
> it
> agains the showing tables.  To me that means you are joining against the
> channel
> table in order to filter out rows in the showing table that have
> stationId's
> that do not exist in the channel table.  
> 
> Is that correct?  If that is correct, then the query I gave will not do
> what you
> want.  
> 
> Can the goal you are attempting be stated as?
> 
> Find next or earliest showings from the showings table for channels in
> the
> channel table?
> 
> If this is the case, maybe this query will work:
> 
>   SELECT showing.startTime
> FROM showing
>WHERE showing.startTime >= 123923000
>  AND showing.stationId IN ( SELECT DISTINCT channel.stationId FROM
> channel )
>LIMIT 8;
> 
> enjoy,
> 
> -jeremy
> 
>  
> On Tue, Apr 21, 2009 at 08:58:56PM -0700, sorka wrote:
>> 
>> Writing the query as you said you would returns results in nearly
>> instantly,
>> within a few ms. 
>> 
>> The problem is when you add a secondary ordering field. The intended
>> indices
>> are being used.
>> 
>> The problem, as I've explained several times already is that there is no
>> way
>> to create a multicolumn index across tables. Sqlite is using the
>> stationId
>> index on the channel table to join showings via the stationId. The query
>> plan shows it clearly. When ordering by startTime or even startTime and
>> stationId, the results are returned in a few ms. But when adding channel
>> number(i.e startTime and then channel number), it jumps to two minutes.
>> This
>> is because sqlite is bringing in nearly 100K records from the showing
>> table,
>> sorted by time, because of the startTime index, before it starts sorting
>> by
>> channel number. It would sure be nice to be able to get sqlite to sort on
>> the channel number for each time group. i.e. once all the 10:00 PM
>> showings
>> have been returned and the first 10:30 PM showing is about to be
>> returned,
>> sort all of the 10:00 PM showings by channel number. Sqlite is NOT doing
>> this but should. 
>> 
>> At this point, I'm going to assume that this is just a limitation in the
>> sqlite engine and I'll have to proceed in other ways to solve this issue.
>> 
>> 
>> 
>> Ian Walters wrote:
>> > 
>> >> SELECT showing.startTime FROM showing JOIN channel ON  
>> >> showing.startTime >=
>> >> 123923 AND showing.stationId = channel.stationId ORDER BY
>> >> showing.startTime LIMIT 8;
>> > 
>> > I don't know if it would be faster... but I would have written that.
>> > 
>> > SELECT showing.startTime FROM showing JOIN channel USING (stationId)  
>> > WHERE startTime < 123923 ORDER BY startTime LIMIT 8.
>> > 
>> > Also I know in the latest version of SQLite its possible to 'hint'  
>> > what indexes should be used, which might be helpful.  There is also  
>> > something on the contrib page that lets you check a query to see what  
>> > indexes it does use.
>> > 
>> > Sorry if the above lacks detail, its kinda a busy day.
>> > 
>> > --
>> > Ian
>> > ___
>> > sqlite-users mailing list
>> > sqlite-users@sqlite.org
>> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>> > 
>> > 
>> 
>> -- 
>> View this message in context:
>> http://www.nabble.com/Multi-column-ORDER-BY-across-table-peformance-problem-tp23109024p23169251.html
>> Sent from the SQLite mailing list archive at Nabble.com.
>> 
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> -- 
> 
>  Jeremy Hinegardner   

Re: [sqlite] Multi column ORDER BY across table peformance problem....

[Jeremy Hinegardner]

That last query should be:

   SELECT showing.startTime
 FROM showing
WHERE showing.startTime >= 123923000
  AND showing.stationId IN ( SELECT DISTINCT channel.stationId FROM 
channel )
 ORDER BY showing.startTime
LIMIT 8;

I forgot the order by.

enjoy,

-jeremy


On Tue, Apr 21, 2009 at 11:07:15PM -0600, Jeremy Hinegardner wrote:
> How about trying:
> 
>   SELECT s.startTime
> FROM ( SELECT  showing.startTime AS startTime
>   ,showing.stationId AS stationId
>   FROM showing
>  WHERE showing.startTime >= 123923
>   ORDER BY showing.startTime
>LIMIT 8 ) AS s
> JOIN channel AS c
>   ON s.stationId = c.stationId
> ORDER BY s.startTime, c.ChannelMajorNumber
> 
> I believe this should limit your table of 100,000 rows down to just the 8 you
> want on the inner query, and then join that against the 100 rows in the 
> channel
> table.
> 
> Although, to tell you the truth, I don't see the purpose in your original 
> query:
> 
> SELECT showing.startTime 
>   FROM showing 
>   JOIN channel 
> ON showing.startTime >= 123923 
>AND showing.stationId = channel.stationId
>   ORDER BY showing.startTime, channel.ChannelMajorNumber 
>  LIMIT 8;
> 
> You are not doing anything with the channel table data other than joining it
> agains the showing tables.  To me that means you are joining against the 
> channel
> table in order to filter out rows in the showing table that have stationId's
> that do not exist in the channel table.  
> 
> Is that correct?  If that is correct, then the query I gave will not do what 
> you
> want.  
> 
> Can the goal you are attempting be stated as?
> 
> Find next or earliest showings from the showings table for channels in the
> channel table?
> 
> If this is the case, maybe this query will work:
> 
>   SELECT showing.startTime
> FROM showing
>WHERE showing.startTime >= 123923000
>  AND showing.stationId IN ( SELECT DISTINCT channel.stationId FROM 
> channel )
>LIMIT 8;
> 
> enjoy,
> 
> -jeremy
> 
>  
> On Tue, Apr 21, 2009 at 08:58:56PM -0700, sorka wrote:
> > 
> > Writing the query as you said you would returns results in nearly instantly,
> > within a few ms. 
> > 
> > The problem is when you add a secondary ordering field. The intended indices
> > are being used.
> > 
> > The problem, as I've explained several times already is that there is no way
> > to create a multicolumn index across tables. Sqlite is using the stationId
> > index on the channel table to join showings via the stationId. The query
> > plan shows it clearly. When ordering by startTime or even startTime and
> > stationId, the results are returned in a few ms. But when adding channel
> > number(i.e startTime and then channel number), it jumps to two minutes. This
> > is because sqlite is bringing in nearly 100K records from the showing table,
> > sorted by time, because of the startTime index, before it starts sorting by
> > channel number. It would sure be nice to be able to get sqlite to sort on
> > the channel number for each time group. i.e. once all the 10:00 PM showings
> > have been returned and the first 10:30 PM showing is about to be returned,
> > sort all of the 10:00 PM showings by channel number. Sqlite is NOT doing
> > this but should. 
> > 
> > At this point, I'm going to assume that this is just a limitation in the
> > sqlite engine and I'll have to proceed in other ways to solve this issue.
> > 
> > 
> > 
> > Ian Walters wrote:
> > > 
> > >> SELECT showing.startTime FROM showing JOIN channel ON  
> > >> showing.startTime >=
> > >> 123923 AND showing.stationId = channel.stationId ORDER BY
> > >> showing.startTime LIMIT 8;
> > > 
> > > I don't know if it would be faster... but I would have written that.
> > > 
> > > SELECT showing.startTime FROM showing JOIN channel USING (stationId)  
> > > WHERE startTime < 123923 ORDER BY startTime LIMIT 8.
> > > 
> > > Also I know in the latest version of SQLite its possible to 'hint'  
> > > what indexes should be used, which might be helpful.  There is also  
> > > something on the contrib page that lets you check a query to see what  
> > > indexes it does use.
> > > 
> > > Sorry if the above lacks detail, its kinda a busy day.
> > > 
> > > --
> > > Ian
> > > ___
> > > sqlite-users mailing list
> > > sqlite-users@sqlite.org
> > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> > > 
> > > 
> > 
> > -- 
> > View this message in context: 
> > http://www.nabble.com/Multi-column-ORDER-BY-across-table-peformance-problem-tp23109024p23169251.html
> > Sent from the SQLite mailing list archive at Nabble.com.
> > 
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinf

Re: [sqlite] Multi column ORDER BY across table peformance problem....

[Jeremy Hinegardner]

How about trying:

  SELECT s.startTime
FROM ( SELECT  showing.startTime AS startTime
  ,showing.stationId AS stationId
  FROM showing
 WHERE showing.startTime >= 123923
  ORDER BY showing.startTime
   LIMIT 8 ) AS s
JOIN channel AS c
  ON s.stationId = c.stationId
ORDER BY s.startTime, c.ChannelMajorNumber

I believe this should limit your table of 100,000 rows down to just the 8 you
want on the inner query, and then join that against the 100 rows in the channel
table.

Although, to tell you the truth, I don't see the purpose in your original query:

SELECT showing.startTime 
  FROM showing 
  JOIN channel 
ON showing.startTime >= 123923 
   AND showing.stationId = channel.stationId
  ORDER BY showing.startTime, channel.ChannelMajorNumber 
 LIMIT 8;

You are not doing anything with the channel table data other than joining it
agains the showing tables.  To me that means you are joining against the channel
table in order to filter out rows in the showing table that have stationId's
that do not exist in the channel table.  

Is that correct?  If that is correct, then the query I gave will not do what you
want.  

Can the goal you are attempting be stated as?

Find next or earliest showings from the showings table for channels in the
channel table?

If this is the case, maybe this query will work:

  SELECT showing.startTime
FROM showing
   WHERE showing.startTime >= 123923000
 AND showing.stationId IN ( SELECT DISTINCT channel.stationId FROM 
channel )
   LIMIT 8;

enjoy,

-jeremy

 
On Tue, Apr 21, 2009 at 08:58:56PM -0700, sorka wrote:
> 
> Writing the query as you said you would returns results in nearly instantly,
> within a few ms. 
> 
> The problem is when you add a secondary ordering field. The intended indices
> are being used.
> 
> The problem, as I've explained several times already is that there is no way
> to create a multicolumn index across tables. Sqlite is using the stationId
> index on the channel table to join showings via the stationId. The query
> plan shows it clearly. When ordering by startTime or even startTime and
> stationId, the results are returned in a few ms. But when adding channel
> number(i.e startTime and then channel number), it jumps to two minutes. This
> is because sqlite is bringing in nearly 100K records from the showing table,
> sorted by time, because of the startTime index, before it starts sorting by
> channel number. It would sure be nice to be able to get sqlite to sort on
> the channel number for each time group. i.e. once all the 10:00 PM showings
> have been returned and the first 10:30 PM showing is about to be returned,
> sort all of the 10:00 PM showings by channel number. Sqlite is NOT doing
> this but should. 
> 
> At this point, I'm going to assume that this is just a limitation in the
> sqlite engine and I'll have to proceed in other ways to solve this issue.
> 
> 
> 
> Ian Walters wrote:
> > 
> >> SELECT showing.startTime FROM showing JOIN channel ON  
> >> showing.startTime >=
> >> 123923 AND showing.stationId = channel.stationId ORDER BY
> >> showing.startTime LIMIT 8;
> > 
> > I don't know if it would be faster... but I would have written that.
> > 
> > SELECT showing.startTime FROM showing JOIN channel USING (stationId)  
> > WHERE startTime < 123923 ORDER BY startTime LIMIT 8.
> > 
> > Also I know in the latest version of SQLite its possible to 'hint'  
> > what indexes should be used, which might be helpful.  There is also  
> > something on the contrib page that lets you check a query to see what  
> > indexes it does use.
> > 
> > Sorry if the above lacks detail, its kinda a busy day.
> > 
> > --
> > Ian
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> > 
> > 
> 
> -- 
> View this message in context: 
> http://www.nabble.com/Multi-column-ORDER-BY-across-table-peformance-problem-tp23109024p23169251.html
> Sent from the SQLite mailing list archive at Nabble.com.
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

-- 

 Jeremy Hinegardner  jer...@hinegardner.org 

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Multi column ORDER BY across table peformance problem....

[sorka]

Writing the query as you said you would returns results in nearly instantly,
within a few ms. 

The problem is when you add a secondary ordering field. The intended indices
are being used.

The problem, as I've explained several times already is that there is no way
to create a multicolumn index across tables. Sqlite is using the stationId
index on the channel table to join showings via the stationId. The query
plan shows it clearly. When ordering by startTime or even startTime and
stationId, the results are returned in a few ms. But when adding channel
number(i.e startTime and then channel number), it jumps to two minutes. This
is because sqlite is bringing in nearly 100K records from the showing table,
sorted by time, because of the startTime index, before it starts sorting by
channel number. It would sure be nice to be able to get sqlite to sort on
the channel number for each time group. i.e. once all the 10:00 PM showings
have been returned and the first 10:30 PM showing is about to be returned,
sort all of the 10:00 PM showings by channel number. Sqlite is NOT doing
this but should. 

At this point, I'm going to assume that this is just a limitation in the
sqlite engine and I'll have to proceed in other ways to solve this issue.



Ian Walters wrote:
> 
>> SELECT showing.startTime FROM showing JOIN channel ON  
>> showing.startTime >=
>> 123923 AND showing.stationId = channel.stationId ORDER BY
>> showing.startTime LIMIT 8;
> 
> I don't know if it would be faster... but I would have written that.
> 
> SELECT showing.startTime FROM showing JOIN channel USING (stationId)  
> WHERE startTime < 123923 ORDER BY startTime LIMIT 8.
> 
> Also I know in the latest version of SQLite its possible to 'hint'  
> what indexes should be used, which might be helpful.  There is also  
> something on the contrib page that lets you check a query to see what  
> indexes it does use.
> 
> Sorry if the above lacks detail, its kinda a busy day.
> 
> --
> Ian
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this message in context: 
http://www.nabble.com/Multi-column-ORDER-BY-across-table-peformance-problem-tp23109024p23169251.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Multi column ORDER BY across table peformance problem....

[Ian Walters]

> SELECT showing.startTime FROM showing JOIN channel ON  
> showing.startTime >=
> 123923 AND showing.stationId = channel.stationId ORDER BY
> showing.startTime LIMIT 8;

I don't know if it would be faster... but I would have written that.

SELECT showing.startTime FROM showing JOIN channel USING (stationId)  
WHERE startTime < 123923 ORDER BY startTime LIMIT 8.

Also I know in the latest version of SQLite its possible to 'hint'  
what indexes should be used, which might be helpful.  There is also  
something on the contrib page that lets you check a query to see what  
indexes it does use.

Sorry if the above lacks detail, its kinda a busy day.

--
Ian
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Multi column ORDER BY across table peformance problem....

[sorka]

That's completely untrue. The only reason for the difference in the name is
because I changed it recently. MajoreChannelNumber is ChannelNumber, the
name just changed.

I've included the full schema and all the indices and indicated about how
many records are in each table.

The schema example is super simple and the question is one that anyone
reasonably familiar with sqlite should be able to answer.



sorka wrote:
> 
> This should be simple but apparently it isn't.
> 
> I have two tables:
> "CREATE TABLE showing ( "
> "showingIdINTEGER PRIMARY KEY, "
> "stationId  INTEGER, "
> "startTime  INTEGER, "
> ") "
> CREATE INDEX showing_startTime on showing(startTime);
> 
> 
> AND
> "CREATE TABLE channel ( "
> "  channelIdINTEGER PRIMARY KEY, "
> "  ChannelNumber   INTEGER, "
> "  stationIdINTEGER, "
> "  ) "
> CREATE INDEX channel_ChannelNumber on channel(ChannelNumber);
> CREATE INDEX channel_stationId on channel(stationId);
> 
> When I do this select:
> SELECT showing.startTime FROM showing JOIN channel ON showing.startTime >=
> 123923 AND showing.stationId = channel.stationId ORDER BY
> showing.startTime LIMIT 8;
> 
> I get back the correct 8 results in about 3 milliseconds. 
> 
> If I throw a secondary order term in there say ChannelNumber:
> 
> SELECT showing.startTime FROM showing JOIN channel ON showing.startTime >=
> 123923 AND showing.stationId = channel.stationId ORDER BY
> showing.startTime, channel.ChannelMajorNumber LIMIT 8;
> 
> It now takes over 120 seconds!!!
> 
> I've tried various multi-column indices including one on
> channel(stationId, ChannelNumber).
> 
> No difference.
> 
> As far as I can tell, when ordering on columns that cross tables, sqlite
> will bring in all the records that match the equality or inequality search
> term on the first column before it does a secondary sort.
> 
> I have over 100,000 records in the showing table and about 100 records in
> the channel table.
> 
> Sqlite should be smart enough to do the secondary sort on ChannelNumber as
> soon as it sees that the records coming back have a later startTime than
> the previous one. i.e. Say the first 5 records have the same startTime and
> different channel numbers. Then the next 5 records have a later start time
> than the first. Sqlite should be smart enough to see this as the results
> come back and do a secondary sort on ChannelNumber on the first 5 results
> and then rinse and repeat.
> 
> What appears to be happening is that even though startTime is indexed,
> sqlite is bringing all 100K records into memory sorted by time before it
> starts to sort by channel number. 
> 
> Is there a way to get sqlite to do the right thing? If there was only a
> way to have a multi-column index that included columns from different
> tables. Oh wait, there is, it's called an intermediate table. However the
> cost of doing this is pretty high for reasons I can't go into here. 
> 
> Any ideas? Maybe I'm just doing something wrong and this should be simple.
> 
> Thanks.
> 
> 
> 
> 

-- 
View this message in context: 
http://www.nabble.com/Multi-column-ORDER-BY-across-table-peformance-problem-tp23109024p23168893.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Multi column ORDER BY across table peformance problem....

[Harold Wood]

sorry.  Ive had good results with subqueries in resolving similar performance 
issues.

--- On Tue, 4/21/09, sorka  wrote:

From: sorka 
Subject: Re: [sqlite] Multi column ORDER BY across table peformance problem
To: sqlite-users@sqlite.org
Date: Tuesday, April 21, 2009, 10:22 PM

I can't think of any reason why this would result in a faster query. But I
went ahead and tried it on the off chance that maybe specifying the
channel.ChannelMajorNumber inside an inner select might somehow trick sqlite
into doing the right thing. Unfortunately, the result was the same. A few ms
without the channel number and over 2 minutes with it. Again, sqlite is
bringing in all results, not just the limit before it sorts on the channel
number. 

I tried your query but had to alias the inner columns so they could be
selected in the outer query.

SELECT startTime FROM (SELECT showing.startTime as startTime,
channel.ChannelMajorNumber as ChannelMajorNumber FROM showing JOIN channel
ON showing.startTime >= 1240362000 AND showing.stationId = channel.stationId
) ORDER BY startTime, ChannelMajorNumber LIMIT 8;



sorka wrote:
> 
> This should be simple but apparently it isn't.
> 
> I have two tables:
> "CREATE TABLE showing ( "
> "showingIdINTEGER PRIMARY KEY, "
> "stationId  INTEGER, "
> "startTime  INTEGER, "
> ") "
> CREATE INDEX showing_startTime on showing(startTime);
> 
> 
> AND
> "CREATE TABLE channel ( "
> "  channelIdINTEGER PRIMARY KEY, "
> "  ChannelNumber   INTEGER, "
> "  stationIdINTEGER, "
> "  ) "
> CREATE INDEX channel_ChannelNumber on channel(ChannelNumber);
> CREATE INDEX channel_stationId on channel(stationId);
> 
> When I do this select:
> SELECT showing.startTime FROM showing JOIN channel ON showing.startTime
>=
> 123923 AND showing.stationId = channel.stationId ORDER BY
> showing.startTime LIMIT 8;
> 
> I get back the correct 8 results in about 3 milliseconds. 
> 
> If I throw a secondary order term in there say ChannelNumber:
> 
> SELECT showing.startTime FROM showing JOIN channel ON showing.startTime
>=
> 123923 AND showing.stationId = channel.stationId ORDER BY
> showing.startTime, channel.ChannelMajorNumber LIMIT 8;
> 
> It now takes over 120 seconds!!!
> 
> I've tried various multi-column indices including one on
> channel(stationId, ChannelNumber).
> 
> No difference.
> 
> As far as I can tell, when ordering on columns that cross tables, sqlite
> will bring in all the records that match the equality or inequality search
> term on the first column before it does a secondary sort.
> 
> I have over 100,000 records in the showing table and about 100 records in
> the channel table.
> 
> Sqlite should be smart enough to do the secondary sort on ChannelNumber as
> soon as it sees that the records coming back have a later startTime than
> the previous one. i.e. Say the first 5 records have the same startTime and
> different channel numbers. Then the next 5 records have a later start time
> than the first. Sqlite should be smart enough to see this as the results
> come back and do a secondary sort on ChannelNumber on the first 5 results
> and then rinse and repeat.
> 
> What appears to be happening is that even though startTime is indexed,
> sqlite is bringing all 100K records into memory sorted by time before it
> starts to sort by channel number. 
> 
> Is there a way to get sqlite to do the right thing? If there was only a
> way to have a multi-column index that included columns from different
> tables. Oh wait, there is, it's called an intermediate table. However
the
> cost of doing this is pretty high for reasons I can't go into here. 
> 
> Any ideas? Maybe I'm just doing something wrong and this should be
simple.
> 
> Thanks.
> 
> 
> 
> 

-- 
View this message in context:
http://www.nabble.com/Multi-column-ORDER-BY-across-table-peformance-problem-tp23109024p23168567.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Multi column ORDER BY across table peformance problem....

[John Machin]

On 22/04/2009 10:48 AM, sorka wrote:
> Wow. Anybody? I figured this would be a simple question for the gurus on this
> board. Seriously, nobody knows a better way to do this?

Seriously, consider alternative possibilities

> 
> 
> sorka wrote:
>> This should be simple but apparently it isn't.
>>
>> I have two tables:
>> "CREATE TABLE showing ( "
>> "showingIdINTEGER PRIMARY KEY, "
>> "stationId  INTEGER, "
>> "startTime  INTEGER, "
>> ") "
>> CREATE INDEX showing_startTime on showing(startTime);
>>
>>
>> AND
>> "CREATE TABLE channel ( "
>> "  channelIdINTEGER PRIMARY KEY, "
>> "  ChannelNumber   INTEGER, "
>> "  stationIdINTEGER, "
>> "  ) "
>> CREATE INDEX channel_ChannelNumber on channel(ChannelNumber);
>> CREATE INDEX channel_stationId on channel(stationId);
>>
>> When I do this select:
>> SELECT showing.startTime FROM showing JOIN channel ON showing.startTime >=
>> 123923 AND showing.stationId = channel.stationId ORDER BY
>> showing.startTime LIMIT 8;

SQLite is smart enough to recognise that the showing_startTime index 
satisfies the ORDER BY clause ... so it's just traversing that index, 
filtering out cases of bogus stationID [I presume that's the reason for 
having the JOIN at all], and stopping when it's found 8 results.

What is the result of:
SELECT COUNT(*) FROM showing JOIN channel ON showing.startTime >=
123923 AND showing.stationId = channel.stationId;
?
Is that 8 or 10 or somewhere in between?


>>
>> I get back the correct 8 results in about 3 milliseconds. 
>>
>> If I throw a secondary order term in there say ChannelNumber:
>>
>> SELECT showing.startTime FROM showing JOIN channel ON showing.startTime >=
>> 123923 AND showing.stationId = channel.stationId ORDER BY
>> showing.startTime, channel.ChannelMajorNumber LIMIT 8;

What is channel.ChannelMajorNumber? It's not in the schema that you 
posted. People who might be interested in helping you might want to try 
running your queries prefaced with EXPLAIN and EXPLAIN QUERY PLAN (which 
doesn't need any data, just the CREATE statements). Perhaps they then 
give up.

Your tables contain only presumingly meaningless integers. [You have 
given us the full schema for each table, haven't you? There aren't any 
50 Kb BLOBs in there, are there?] So you wouldn't have a problem with 
making a zipped db with the two tables (no indexes) available on the web 
somewhere for people who wanted to experiment?

>>
>> It now takes over 120 seconds!!!
>>
>> I've tried various multi-column indices including one on
>> channel(stationId, ChannelNumber).

And was that index used by the query? Did you have ChannelNumber in the 
ORDER BY clause at the time, or something else?

>>
>> No difference.
>>
>> As far as I can tell, when ordering on columns that cross tables, sqlite
>> will bring in all the records that match the equality or inequality search
>> term on the first column before it does a secondary sort.


What any SQL query engine has to do is produce results that are the same 
as those obtained by this method:
1. Produce temporary result set ignoring the ORDER BY and the 
LIMIT/OFFSET. This will include all columns from the SELECT plus any 
extra that appear only in the ORDER BY.
2. Sort the results according to the ORDER BY.
3. Read the sort output, discarding columns that appear only in the 
ORDER BY, and filtering according to the LIMIT/OFFSET.

Is that what you mean?

>>
>> I have over 100,000 records in the showing table and about 100 records in
>> the channel table.
>>
>> Sqlite should be smart enough to do the secondary sort on ChannelNumber as
>> soon as it sees that the records coming back have a later startTime than
>> the previous one. i.e. Say the first 5 records have the same startTime and
>> different channel numbers. Then the next 5 records have a later start time
>> than the first. Sqlite should be smart enough to see this as the results
>> come back and do a secondary sort on ChannelNumber on the first 5 results
>> and then rinse and repeat.

What is "the secondary sort on ChannelNumber" that you imply is already 
being done, just not soon enough?

>>
>> What appears to be happening is that even though startTime is indexed,
>> sqlite is bringing all 100K records into memory sorted by time before it
>> starts to sort by channel number. 

Apart from that being one possible explanation of what does seem a long 
time, what grounds do you have for the above statement?


>>
>> Is there a way to get sqlite to do the right thing? If there was only a
>> way to have a multi-column index that included columns from different
>> tables. Oh wait, there is, it's called an intermediate table. However the
>> cost of doing this is pretty high for reasons I can't go into here. 
>>
>> Any ideas? Maybe I'm just doi

Re: [sqlite] Multi column ORDER BY across table peformance problem....

[sorka]

I can't think of any reason why this would result in a faster query. But I
went ahead and tried it on the off chance that maybe specifying the
channel.ChannelMajorNumber inside an inner select might somehow trick sqlite
into doing the right thing. Unfortunately, the result was the same. A few ms
without the channel number and over 2 minutes with it. Again, sqlite is
bringing in all results, not just the limit before it sorts on the channel
number. 

I tried your query but had to alias the inner columns so they could be
selected in the outer query.

SELECT startTime FROM (SELECT showing.startTime as startTime,
channel.ChannelMajorNumber as ChannelMajorNumber FROM showing JOIN channel
ON showing.startTime >= 1240362000 AND showing.stationId = channel.stationId
) ORDER BY startTime, ChannelMajorNumber LIMIT 8;



sorka wrote:
> 
> This should be simple but apparently it isn't.
> 
> I have two tables:
> "CREATE TABLE showing ( "
> "showingIdINTEGER PRIMARY KEY, "
> "stationId  INTEGER, "
> "startTime  INTEGER, "
> ") "
> CREATE INDEX showing_startTime on showing(startTime);
> 
> 
> AND
> "CREATE TABLE channel ( "
> "  channelIdINTEGER PRIMARY KEY, "
> "  ChannelNumber   INTEGER, "
> "  stationIdINTEGER, "
> "  ) "
> CREATE INDEX channel_ChannelNumber on channel(ChannelNumber);
> CREATE INDEX channel_stationId on channel(stationId);
> 
> When I do this select:
> SELECT showing.startTime FROM showing JOIN channel ON showing.startTime >=
> 123923 AND showing.stationId = channel.stationId ORDER BY
> showing.startTime LIMIT 8;
> 
> I get back the correct 8 results in about 3 milliseconds. 
> 
> If I throw a secondary order term in there say ChannelNumber:
> 
> SELECT showing.startTime FROM showing JOIN channel ON showing.startTime >=
> 123923 AND showing.stationId = channel.stationId ORDER BY
> showing.startTime, channel.ChannelMajorNumber LIMIT 8;
> 
> It now takes over 120 seconds!!!
> 
> I've tried various multi-column indices including one on
> channel(stationId, ChannelNumber).
> 
> No difference.
> 
> As far as I can tell, when ordering on columns that cross tables, sqlite
> will bring in all the records that match the equality or inequality search
> term on the first column before it does a secondary sort.
> 
> I have over 100,000 records in the showing table and about 100 records in
> the channel table.
> 
> Sqlite should be smart enough to do the secondary sort on ChannelNumber as
> soon as it sees that the records coming back have a later startTime than
> the previous one. i.e. Say the first 5 records have the same startTime and
> different channel numbers. Then the next 5 records have a later start time
> than the first. Sqlite should be smart enough to see this as the results
> come back and do a secondary sort on ChannelNumber on the first 5 results
> and then rinse and repeat.
> 
> What appears to be happening is that even though startTime is indexed,
> sqlite is bringing all 100K records into memory sorted by time before it
> starts to sort by channel number. 
> 
> Is there a way to get sqlite to do the right thing? If there was only a
> way to have a multi-column index that included columns from different
> tables. Oh wait, there is, it's called an intermediate table. However the
> cost of doing this is pretty high for reasons I can't go into here. 
> 
> Any ideas? Maybe I'm just doing something wrong and this should be simple.
> 
> Thanks.
> 
> 
> 
> 

-- 
View this message in context: 
http://www.nabble.com/Multi-column-ORDER-BY-across-table-peformance-problem-tp23109024p23168567.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Multi column ORDER BY across table peformance problem....

[Harold Wood]

try this
 

select showing.startTime, from (
 SELECT showing.startTime,
   channel.ChannelMajorNumber
  FROM showing 
  JOIN channel 
   on showing.stationId = channel.stationId
  Where showing.startTime >=  123923) 
ORDER BY showing.startTime, channel.ChannelMajorNumber 
LIMIT 8;
 
 
 
--- On Tue, 4/21/09, sorka  wrote:

From: sorka 
Subject: Re: [sqlite] Multi column ORDER BY across table peformance problem
To: sqlite-users@sqlite.org
Date: Tuesday, April 21, 2009, 9:46 PM

It's in my original post above.



sorka wrote:
> 
> This should be simple but apparently it isn't.
> 
> I have two tables:
> "CREATE TABLE showing ( "
> "showingIdINTEGER PRIMARY KEY, "
> "stationId  INTEGER, "
> "startTime  INTEGER, "
> ") "
> CREATE INDEX showing_startTime on showing(startTime);
> 
> 
> AND
> "CREATE TABLE channel ( "
> "  channelIdINTEGER PRIMARY KEY, "
> "  ChannelNumber   INTEGER, "
> "  stationIdINTEGER, "
> "  ) "
> CREATE INDEX channel_ChannelNumber on channel(ChannelNumber);
> CREATE INDEX channel_stationId on channel(stationId);
> 
> When I do this select:
> SELECT showing.startTime FROM showing JOIN channel ON showing.startTime
>=
> 123923 AND showing.stationId = channel.stationId ORDER BY
> showing.startTime LIMIT 8;
> 
> I get back the correct 8 results in about 3 milliseconds. 
> 
> If I throw a secondary order term in there say ChannelNumber:
> 
> SELECT showing.startTime FROM showing JOIN channel ON showing.startTime
>=
> 123923 AND showing.stationId = channel.stationId ORDER BY
> showing.startTime, channel.ChannelMajorNumber LIMIT 8;
> 
> It now takes over 120 seconds!!!
> 
> I've tried various multi-column indices including one on
> channel(stationId, ChannelNumber).
> 
> No difference.
> 
> As far as I can tell, when ordering on columns that cross tables, sqlite
> will bring in all the records that match the equality or inequality search
> term on the first column before it does a secondary sort.
> 
> I have over 100,000 records in the showing table and about 100 records in
> the channel table.
> 
> Sqlite should be smart enough to do the secondary sort on ChannelNumber as
> soon as it sees that the records coming back have a later startTime than
> the previous one. i.e. Say the first 5 records have the same startTime and
> different channel numbers. Then the next 5 records have a later start time
> than the first. Sqlite should be smart enough to see this as the results
> come back and do a secondary sort on ChannelNumber on the first 5 results
> and then rinse and repeat.
> 
> What appears to be happening is that even though startTime is indexed,
> sqlite is bringing all 100K records into memory sorted by time before it
> starts to sort by channel number. 
> 
> Is there a way to get sqlite to do the right thing? If there was only a
> way to have a multi-column index that included columns from different
> tables. Oh wait, there is, it's called an intermediate table. However
the
> cost of doing this is pretty high for reasons I can't go into here. 
> 
> Any ideas? Maybe I'm just doing something wrong and this should be
simple.
> 
> Thanks.
> 
> 
> 
> 

-- 
View this message in context:
http://www.nabble.com/Multi-column-ORDER-BY-across-table-peformance-problem-tp23109024p23168281.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Multi column ORDER BY across table peformance problem....

[sorka]

It's in my original post above.



sorka wrote:
> 
> This should be simple but apparently it isn't.
> 
> I have two tables:
> "CREATE TABLE showing ( "
> "showingIdINTEGER PRIMARY KEY, "
> "stationId  INTEGER, "
> "startTime  INTEGER, "
> ") "
> CREATE INDEX showing_startTime on showing(startTime);
> 
> 
> AND
> "CREATE TABLE channel ( "
> "  channelIdINTEGER PRIMARY KEY, "
> "  ChannelNumber   INTEGER, "
> "  stationIdINTEGER, "
> "  ) "
> CREATE INDEX channel_ChannelNumber on channel(ChannelNumber);
> CREATE INDEX channel_stationId on channel(stationId);
> 
> When I do this select:
> SELECT showing.startTime FROM showing JOIN channel ON showing.startTime >=
> 123923 AND showing.stationId = channel.stationId ORDER BY
> showing.startTime LIMIT 8;
> 
> I get back the correct 8 results in about 3 milliseconds. 
> 
> If I throw a secondary order term in there say ChannelNumber:
> 
> SELECT showing.startTime FROM showing JOIN channel ON showing.startTime >=
> 123923 AND showing.stationId = channel.stationId ORDER BY
> showing.startTime, channel.ChannelMajorNumber LIMIT 8;
> 
> It now takes over 120 seconds!!!
> 
> I've tried various multi-column indices including one on
> channel(stationId, ChannelNumber).
> 
> No difference.
> 
> As far as I can tell, when ordering on columns that cross tables, sqlite
> will bring in all the records that match the equality or inequality search
> term on the first column before it does a secondary sort.
> 
> I have over 100,000 records in the showing table and about 100 records in
> the channel table.
> 
> Sqlite should be smart enough to do the secondary sort on ChannelNumber as
> soon as it sees that the records coming back have a later startTime than
> the previous one. i.e. Say the first 5 records have the same startTime and
> different channel numbers. Then the next 5 records have a later start time
> than the first. Sqlite should be smart enough to see this as the results
> come back and do a secondary sort on ChannelNumber on the first 5 results
> and then rinse and repeat.
> 
> What appears to be happening is that even though startTime is indexed,
> sqlite is bringing all 100K records into memory sorted by time before it
> starts to sort by channel number. 
> 
> Is there a way to get sqlite to do the right thing? If there was only a
> way to have a multi-column index that included columns from different
> tables. Oh wait, there is, it's called an intermediate table. However the
> cost of doing this is pretty high for reasons I can't go into here. 
> 
> Any ideas? Maybe I'm just doing something wrong and this should be simple.
> 
> Thanks.
> 
> 
> 
> 

-- 
View this message in context: 
http://www.nabble.com/Multi-column-ORDER-BY-across-table-peformance-problem-tp23109024p23168281.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Multi column ORDER BY across table peformance problem....

[Harold Wood]

send me your original query please.
 
thanks
 
Woody

--- On Tue, 4/21/09, sorka  wrote:

From: sorka 
Subject: Re: [sqlite] Multi column ORDER BY across table peformance problem
To: sqlite-users@sqlite.org
Date: Tuesday, April 21, 2009, 9:29 PM

No. This can't be broken down into a query within a query. Perhaps maybe if
you spell out an example of what you're thinking?

Thanks.



sorka wrote:
> 
> This should be simple but apparently it isn't.
> 
> I have two tables:
> "CREATE TABLE showing ( "
> "showingIdINTEGER PRIMARY KEY, "
> "stationId  INTEGER, "
> "startTime  INTEGER, "
> ") "
> CREATE INDEX showing_startTime on showing(startTime);
> 
> 
> AND
> "CREATE TABLE channel ( "
> "  channelIdINTEGER PRIMARY KEY, "
> "  ChannelNumber   INTEGER, "
> "  stationIdINTEGER, "
> "  ) "
> CREATE INDEX channel_ChannelNumber on channel(ChannelNumber);
> CREATE INDEX channel_stationId on channel(stationId);
> 
> When I do this select:
> SELECT showing.startTime FROM showing JOIN channel ON showing.startTime
>=
> 123923 AND showing.stationId = channel.stationId ORDER BY
> showing.startTime LIMIT 8;
> 
> I get back the correct 8 results in about 3 milliseconds. 
> 
> If I throw a secondary order term in there say ChannelNumber:
> 
> SELECT showing.startTime FROM showing JOIN channel ON showing.startTime
>=
> 123923 AND showing.stationId = channel.stationId ORDER BY
> showing.startTime, channel.ChannelMajorNumber LIMIT 8;
> 
> It now takes over 120 seconds!!!
> 
> I've tried various multi-column indices including one on
> channel(stationId, ChannelNumber).
> 
> No difference.
> 
> As far as I can tell, when ordering on columns that cross tables, sqlite
> will bring in all the records that match the equality or inequality search
> term on the first column before it does a secondary sort.
> 
> I have over 100,000 records in the showing table and about 100 records in
> the channel table.
> 
> Sqlite should be smart enough to do the secondary sort on ChannelNumber as
> soon as it sees that the records coming back have a later startTime than
> the previous one. i.e. Say the first 5 records have the same startTime and
> different channel numbers. Then the next 5 records have a later start time
> than the first. Sqlite should be smart enough to see this as the results
> come back and do a secondary sort on ChannelNumber on the first 5 results
> and then rinse and repeat.
> 
> What appears to be happening is that even though startTime is indexed,
> sqlite is bringing all 100K records into memory sorted by time before it
> starts to sort by channel number. 
> 
> Is there a way to get sqlite to do the right thing? If there was only a
> way to have a multi-column index that included columns from different
> tables. Oh wait, there is, it's called an intermediate table. However
the
> cost of doing this is pretty high for reasons I can't go into here. 
> 
> Any ideas? Maybe I'm just doing something wrong and this should be
simple.
> 
> Thanks.
> 
> 
> 
> 

-- 
View this message in context:
http://www.nabble.com/Multi-column-ORDER-BY-across-table-peformance-problem-tp23109024p23168105.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Multi column ORDER BY across table peformance problem....

[sorka]

No. This can't be broken down into a query within a query. Perhaps maybe if
you spell out an example of what you're thinking?

Thanks.



sorka wrote:
> 
> This should be simple but apparently it isn't.
> 
> I have two tables:
> "CREATE TABLE showing ( "
> "showingIdINTEGER PRIMARY KEY, "
> "stationId  INTEGER, "
> "startTime  INTEGER, "
> ") "
> CREATE INDEX showing_startTime on showing(startTime);
> 
> 
> AND
> "CREATE TABLE channel ( "
> "  channelIdINTEGER PRIMARY KEY, "
> "  ChannelNumber   INTEGER, "
> "  stationIdINTEGER, "
> "  ) "
> CREATE INDEX channel_ChannelNumber on channel(ChannelNumber);
> CREATE INDEX channel_stationId on channel(stationId);
> 
> When I do this select:
> SELECT showing.startTime FROM showing JOIN channel ON showing.startTime >=
> 123923 AND showing.stationId = channel.stationId ORDER BY
> showing.startTime LIMIT 8;
> 
> I get back the correct 8 results in about 3 milliseconds. 
> 
> If I throw a secondary order term in there say ChannelNumber:
> 
> SELECT showing.startTime FROM showing JOIN channel ON showing.startTime >=
> 123923 AND showing.stationId = channel.stationId ORDER BY
> showing.startTime, channel.ChannelMajorNumber LIMIT 8;
> 
> It now takes over 120 seconds!!!
> 
> I've tried various multi-column indices including one on
> channel(stationId, ChannelNumber).
> 
> No difference.
> 
> As far as I can tell, when ordering on columns that cross tables, sqlite
> will bring in all the records that match the equality or inequality search
> term on the first column before it does a secondary sort.
> 
> I have over 100,000 records in the showing table and about 100 records in
> the channel table.
> 
> Sqlite should be smart enough to do the secondary sort on ChannelNumber as
> soon as it sees that the records coming back have a later startTime than
> the previous one. i.e. Say the first 5 records have the same startTime and
> different channel numbers. Then the next 5 records have a later start time
> than the first. Sqlite should be smart enough to see this as the results
> come back and do a secondary sort on ChannelNumber on the first 5 results
> and then rinse and repeat.
> 
> What appears to be happening is that even though startTime is indexed,
> sqlite is bringing all 100K records into memory sorted by time before it
> starts to sort by channel number. 
> 
> Is there a way to get sqlite to do the right thing? If there was only a
> way to have a multi-column index that included columns from different
> tables. Oh wait, there is, it's called an intermediate table. However the
> cost of doing this is pretty high for reasons I can't go into here. 
> 
> Any ideas? Maybe I'm just doing something wrong and this should be simple.
> 
> Thanks.
> 
> 
> 
> 

-- 
View this message in context: 
http://www.nabble.com/Multi-column-ORDER-BY-across-table-peformance-problem-tp23109024p23168105.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Multi column ORDER BY across table peformance problem....

[Harold Wood]

did you try the subquery method i suggested?
 
Woody

--- On Tue, 4/21/09, sorka  wrote:

From: sorka 
Subject: Re: [sqlite] Multi column ORDER BY across table peformance problem
To: sqlite-users@sqlite.org
Date: Tuesday, April 21, 2009, 8:48 PM

Wow. Anybody? I figured this would be a simple question for the gurus on this
board. Seriously, nobody knows a better way to do this?




sorka wrote:
> 
> This should be simple but apparently it isn't.
> 
> I have two tables:
> "CREATE TABLE showing ( "
> "showingIdINTEGER PRIMARY KEY, "
> "stationId  INTEGER, "
> "startTime  INTEGER, "
> ") "
> CREATE INDEX showing_startTime on showing(startTime);
> 
> 
> AND
> "CREATE TABLE channel ( "
> "  channelIdINTEGER PRIMARY KEY, "
> "  ChannelNumber   INTEGER, "
> "  stationIdINTEGER, "
> "  ) "
> CREATE INDEX channel_ChannelNumber on channel(ChannelNumber);
> CREATE INDEX channel_stationId on channel(stationId);
> 
> When I do this select:
> SELECT showing.startTime FROM showing JOIN channel ON showing.startTime
>=
> 123923 AND showing.stationId = channel.stationId ORDER BY
> showing.startTime LIMIT 8;
> 
> I get back the correct 8 results in about 3 milliseconds. 
> 
> If I throw a secondary order term in there say ChannelNumber:
> 
> SELECT showing.startTime FROM showing JOIN channel ON showing.startTime
>=
> 123923 AND showing.stationId = channel.stationId ORDER BY
> showing.startTime, channel.ChannelMajorNumber LIMIT 8;
> 
> It now takes over 120 seconds!!!
> 
> I've tried various multi-column indices including one on
> channel(stationId, ChannelNumber).
> 
> No difference.
> 
> As far as I can tell, when ordering on columns that cross tables, sqlite
> will bring in all the records that match the equality or inequality search
> term on the first column before it does a secondary sort.
> 
> I have over 100,000 records in the showing table and about 100 records in
> the channel table.
> 
> Sqlite should be smart enough to do the secondary sort on ChannelNumber as
> soon as it sees that the records coming back have a later startTime than
> the previous one. i.e. Say the first 5 records have the same startTime and
> different channel numbers. Then the next 5 records have a later start time
> than the first. Sqlite should be smart enough to see this as the results
> come back and do a secondary sort on ChannelNumber on the first 5 results
> and then rinse and repeat.
> 
> What appears to be happening is that even though startTime is indexed,
> sqlite is bringing all 100K records into memory sorted by time before it
> starts to sort by channel number. 
> 
> Is there a way to get sqlite to do the right thing? If there was only a
> way to have a multi-column index that included columns from different
> tables. Oh wait, there is, it's called an intermediate table. However
the
> cost of doing this is pretty high for reasons I can't go into here. 
> 
> Any ideas? Maybe I'm just doing something wrong and this should be
simple.
> 
> Thanks.
> 
> 
> 
> 

-- 
View this message in context:
http://www.nabble.com/Multi-column-ORDER-BY-across-table-peformance-problem-tp23109024p23166621.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Multi column ORDER BY across table peformance problem....

[sorka]

Wow. Anybody? I figured this would be a simple question for the gurus on this
board. Seriously, nobody knows a better way to do this?




sorka wrote:
> 
> This should be simple but apparently it isn't.
> 
> I have two tables:
> "CREATE TABLE showing ( "
> "showingIdINTEGER PRIMARY KEY, "
> "stationId  INTEGER, "
> "startTime  INTEGER, "
> ") "
> CREATE INDEX showing_startTime on showing(startTime);
> 
> 
> AND
> "CREATE TABLE channel ( "
> "  channelIdINTEGER PRIMARY KEY, "
> "  ChannelNumber   INTEGER, "
> "  stationIdINTEGER, "
> "  ) "
> CREATE INDEX channel_ChannelNumber on channel(ChannelNumber);
> CREATE INDEX channel_stationId on channel(stationId);
> 
> When I do this select:
> SELECT showing.startTime FROM showing JOIN channel ON showing.startTime >=
> 123923 AND showing.stationId = channel.stationId ORDER BY
> showing.startTime LIMIT 8;
> 
> I get back the correct 8 results in about 3 milliseconds. 
> 
> If I throw a secondary order term in there say ChannelNumber:
> 
> SELECT showing.startTime FROM showing JOIN channel ON showing.startTime >=
> 123923 AND showing.stationId = channel.stationId ORDER BY
> showing.startTime, channel.ChannelMajorNumber LIMIT 8;
> 
> It now takes over 120 seconds!!!
> 
> I've tried various multi-column indices including one on
> channel(stationId, ChannelNumber).
> 
> No difference.
> 
> As far as I can tell, when ordering on columns that cross tables, sqlite
> will bring in all the records that match the equality or inequality search
> term on the first column before it does a secondary sort.
> 
> I have over 100,000 records in the showing table and about 100 records in
> the channel table.
> 
> Sqlite should be smart enough to do the secondary sort on ChannelNumber as
> soon as it sees that the records coming back have a later startTime than
> the previous one. i.e. Say the first 5 records have the same startTime and
> different channel numbers. Then the next 5 records have a later start time
> than the first. Sqlite should be smart enough to see this as the results
> come back and do a secondary sort on ChannelNumber on the first 5 results
> and then rinse and repeat.
> 
> What appears to be happening is that even though startTime is indexed,
> sqlite is bringing all 100K records into memory sorted by time before it
> starts to sort by channel number. 
> 
> Is there a way to get sqlite to do the right thing? If there was only a
> way to have a multi-column index that included columns from different
> tables. Oh wait, there is, it's called an intermediate table. However the
> cost of doing this is pretty high for reasons I can't go into here. 
> 
> Any ideas? Maybe I'm just doing something wrong and this should be simple.
> 
> Thanks.
> 
> 
> 
> 

-- 
View this message in context: 
http://www.nabble.com/Multi-column-ORDER-BY-across-table-peformance-problem-tp23109024p23166621.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Memory Usage

[D . Richard Hipp]

On Apr 21, 2009, at 7:12 PM, Marco Bambini wrote:

> Hello Dr. Hipp,
>
> I was finally able to track down the issue ... the problem is due to  
> the fact that after each write operation the client executes a query  
> like:
> "SELECT 123 AS changes;"  (the number 123 changes all the time)
> this select statement was performed in an in-memory database ...  
> then I tried to perform it in a disk based db ... but the behavior  
> doesn't change, the memory used by sqlite continue to increase and  
> it is never released (until the db is closed).
>
> So, my new question is... how I can return a fixed value from an  
> sqlite3 database without having this memory issue?
>

Are you sure you are calling sqlite3_finalize() on the statement once  
you are done with it?  You can use sqlite3_next_stmt() interface to  
get a listing of all unfinalized statements for a database  
connection.  Count them, and if the number keeps growing, that is your  
problem.

D. Richard Hipp
d...@hwaci.com



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Indexes questions

[Joanne Pham]

Hi All,
I have the table which has the following indexes:
CREATE INDEX Zactivate ON sig (peerid,flowid,fbid);
CREATE INDEX Zfbid ON sig (flowid,fbid);
CREATE INDEX Zsignature ON sig (peerid,Zsignature);
 
And below are where statements:
WHERE Zsignature = ? AND peerid = ?";
WHERE peerid = ?"
WHERE peerid = ?";
WHERE flowid = ? AND peerid = ?";
WHERE flowid = ? AND peerid = ?";
WHERE flowid = ? AND peerid = ?";
WHERE flowid = ? AND peerid = ?";
WHERE flowid=? AND fbid=? AND peerid=?";
WHERE peerid=? AND stale='2'";
WHERE peerid=? AND flowid=? AND stale='2'";
 
Should Ionly need two indexes. The second one should not be there. Now the 
write operation will be very slow.
Any ideas?
Thanks,
JP


  
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Difference between all the joins

[Andrey Fedorov]

Hm, this doesn't appear to be true... OUTER JOIN != LEFT OUTER JOIN. In my
version of SQLite (3.5.9), OUTER JOIN actually seems to behave either like
an INNER JOIN [2], which is very confusing (a bug?).
According to the SQL-92 spec [1], the following don't adhere to SQL syntax:
* OUTER JOIN
* NATURAL OUTER JOIN
* NATURAL CROSS JOIN

The first two, because a , in order to include OUTER, must also
include an  before it, and the last, because NATURAL can
only occur in 's, not 's.

It's probably a good idea to avoid these three...

Cheers,
Andrey

1. http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt
2. Using the data from the wikipedia page:

sqlite> SELECT * FROM employees;
name dept_id
---  ---
Rafferty 31
Jones33
Steinberg33
Robinson 34
Smith34
Jasper   NULL

sqlite> SELECT * FROM departments;
dept_id  dept_name
---  ---
31   Sales
33   Engineering
34   Clerical
35   Marketing

sqlite> SELECT * FROM employees OUTER JOIN departments USING (dept_id);
name dept_id  dept_name
---  ---  ---
Rafferty 31   Sales
Jones33   Engineering
Steinberg33   Engineering
Robinson 34   Clerical
Smith34   Clerical

sqlite> SELECT * FROM employees LEFT JOIN departments USING (dept_id);
name dept_id  dept_name
---  ---  ---
Rafferty 31   Sales
Jones33   Engineering
Steinberg33   Engineering
Robinson 34   Clerical
Smith34   Clerical
Jasper   NULL NULL


On Tue, Apr 21, 2009 at 4:32 PM, Andrey Fedorov  wrote:

> Got it, so -
>
> , == CROSS JOIN
> JOIN == INNER JOIN
> LEFT JOIN == LEFT OUTER JOIN == OUTER JOIN
>
> And NATURAL just is just shorthand for matching on same-name rows.
>
> Thanks for the prompt response and detailed explanation, it's very much
> appreciated!
>
> Cheers,
> Andrey
>
> On Tue, Apr 21, 2009 at 2:57 PM, Griggs, Donald <
> donald.gri...@allscripts.com> wrote:
>
>>
>>
>> -Original Message-
>> From: sqlite-users-boun...@sqlite.org
>> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Andrey Fedorov
>> Sent: Tuesday, April 21, 2009 1:27 PM
>> To: sqlite-users@sqlite.org
>> Subject: [sqlite] Difference between all the joins
>>
>> Hi all,
>> According to the join-op syntax [1], SQLite has 13 distinct join
>> statements:
>>
>> ,
>> JOIN
>> LEFT JOIN
>> OUTER JOIN
>> LEFT OUTER JOIN
>> INNER JOIN
>> CROSS JOIN
>> NATURAL JOIN
>> NATURAL LEFT JOIN
>> NATURAL OUTER JOIN
>> NATURAL LEFT OUTER JOIN
>> NATURAL INNER JOIN
>> NATURAL CROSS JOIN
>>
>> Are they all unique? Which are equivalent?
>>
>> - Andrey
>>
>> 1. http://sqlite.org/syntaxdiagrams.html#join-op
>> ___
>> ___
>> ___
>>
>> Hello, Andrey,
>>
>> No, many are synonyms included (I think) as part of the SQL standards or
>> to ease conversion from various other SQL dialects.
>>
>> E.g. an "unadorned" JOIN is identical to an INNER JOIN.   Inner joins
>> have no "left" or "right" so a LEFT JOIN can be used as a synonym for a
>> LEFT OUTER JOIN.  Furthermore, LEFT is a default for OUTER JOINS, so
>> OUTER JOIN == LEFT JOIN == LEFT OUTER JOIN.
>>
>> This article looks interesting:  http://en.wikipedia.org/wiki/Join_(SQL)
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Difference between all the joins

[Andrey Fedorov]

Got it, so -

, == CROSS JOIN
JOIN == INNER JOIN
LEFT JOIN == LEFT OUTER JOIN == OUTER JOIN

And NATURAL just is just shorthand for matching on same-name rows.

Thanks for the prompt response and detailed explanation, it's very much
appreciated!

Cheers,
Andrey

On Tue, Apr 21, 2009 at 2:57 PM, Griggs, Donald <
donald.gri...@allscripts.com> wrote:

>
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Andrey Fedorov
> Sent: Tuesday, April 21, 2009 1:27 PM
> To: sqlite-users@sqlite.org
> Subject: [sqlite] Difference between all the joins
>
> Hi all,
> According to the join-op syntax [1], SQLite has 13 distinct join
> statements:
>
> ,
> JOIN
> LEFT JOIN
> OUTER JOIN
> LEFT OUTER JOIN
> INNER JOIN
> CROSS JOIN
> NATURAL JOIN
> NATURAL LEFT JOIN
> NATURAL OUTER JOIN
> NATURAL LEFT OUTER JOIN
> NATURAL INNER JOIN
> NATURAL CROSS JOIN
>
> Are they all unique? Which are equivalent?
>
> - Andrey
>
> 1. http://sqlite.org/syntaxdiagrams.html#join-op
> ___
> ___
> ___
>
> Hello, Andrey,
>
> No, many are synonyms included (I think) as part of the SQL standards or
> to ease conversion from various other SQL dialects.
>
> E.g. an "unadorned" JOIN is identical to an INNER JOIN.   Inner joins
> have no "left" or "right" so a LEFT JOIN can be used as a synonym for a
> LEFT OUTER JOIN.  Furthermore, LEFT is a default for OUTER JOINS, so
> OUTER JOIN == LEFT JOIN == LEFT OUTER JOIN.
>
> This article looks interesting:  http://en.wikipedia.org/wiki/Join_(SQL)
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Difference between all the joins

[Griggs, Donald]

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Andrey Fedorov
Sent: Tuesday, April 21, 2009 1:27 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] Difference between all the joins

Hi all,
According to the join-op syntax [1], SQLite has 13 distinct join
statements:

,
JOIN
LEFT JOIN
OUTER JOIN
LEFT OUTER JOIN
INNER JOIN
CROSS JOIN
NATURAL JOIN
NATURAL LEFT JOIN
NATURAL OUTER JOIN
NATURAL LEFT OUTER JOIN
NATURAL INNER JOIN
NATURAL CROSS JOIN

Are they all unique? Which are equivalent?

- Andrey

1. http://sqlite.org/syntaxdiagrams.html#join-op
___
___
___

Hello, Andrey,

No, many are synonyms included (I think) as part of the SQL standards or
to ease conversion from various other SQL dialects.

E.g. an "unadorned" JOIN is identical to an INNER JOIN.   Inner joins
have no "left" or "right" so a LEFT JOIN can be used as a synonym for a
LEFT OUTER JOIN.  Furthermore, LEFT is a default for OUTER JOINS, so
OUTER JOIN == LEFT JOIN == LEFT OUTER JOIN.

This article looks interesting:  http://en.wikipedia.org/wiki/Join_(SQL)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Program is crashed on pager_end_transaction

[Joanne Pham]

Hi All,
My application is crashed with the stack trace as below:
            #0  pager_end_transaction (pPager=0x4031fdb8, hasMaster=0)
            at ../src/pager.c:1420
            1420    ../src/pager.c: No such file or directory.
   in ../src/pager.c
            (gdb) where
            #0  pager_end_transaction (pPager=0x4031fdb8, hasMaster=0)
            at ../src/pager.c:1420
            #1  0xb702375c in sqlite3PagerCommitPhaseTwo (pPager=0x4031fdb8)
            at ../src/pager.c:4811
            #2  0xb700761e in sqlite3BtreeCommitPhaseTwo (p=0x4031e1c0)
            at ../src/btree.c:2427
            #3  0xb70436e8 in sqlite3VdbeHalt (p=0x40f695f8) at 
../src/vdbeaux.c:1269
        #4  0xb703c145 in sqlite3VdbeExec (p=0x40f695f8) at ../src/vdbe.c:806
        #5  0xb7041948 in sqlite3_step (pStmt=0x40f695f8) at 
../src/vdbeapi.c:477

and below is the codes:

ret = sqlite3_bind_text(pstmt_is, 1, s, -1, SQLITE_STATIC); 
..
  ret = sqlite3_bind_int(pstmt_is, 9, ts);
  rc = sqlite3_step (pstmt_is);
  if (rc == SQLITE_DONE) {
    rc = 0;
  } else {
  }
  sqlite3_reset(pstmt_is);
  ret = sqlite3_exec(sqlite, "END", NULL, NULL, NULL);
  ret = sqlite3_exec(sqlite, "COMMIT", NULL, NULL, NULL);
Is that the problem with the "END" end of transaction following the "COMMIT" 
and should not have the sqlite3_reset before "END"?
Thanks,
JP


  
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite crashing on iPhone (or so says Apple)

[Shane Harrelson]

Version 3.6.13 fixed some potential alignment issues that could occur on
SPARC (and potentially other) architectures.  I don't know how you or Apple
are testing your app, but if you (or they) are using a device emulator for
the testing, the emulator might not be testing alignment conditions the
same.

HTH.
-Shane

On Tue, Apr 21, 2009 at 11:27 AM, D. Richard Hipp  wrote:

>
> On Apr 21, 2009, at 11:10 AM, Mark Spiegel wrote:
>
> > I'm a bit confused by the following:
> >
> > "The assign 100K or so to each database connection's lookaside memory
> > allocator using sqlite3_db_config(SQLITE_DBCONFIG_LOOKASIDE, ...)
> > immediately after it is opened."
> >
> > If memory is at a premium, why would you reserve a large amount of it
> > for SQLite's "look aside allocator"?  (It's really a zone allocator.)
> > This SQLite mechanism ostensibly attempts to trade memory for
> > speed.  If
> > memory is at a premium, in this case a fixed upper bound, that trade
> > off
> > doesn't seem to make sense.  I would think in a case where memory is
> > tight, zero bytes should be reserved.
> >
>
> This is a reasonable observation.
>
> On the other hand, the lookaside memory allocator (which is just a
> zone allocator, as you observe) makes a big performance difference.
> And if you only have a single database connection, it doesn't really
> matter if the memory goes into lookaside or is in the global heap.  If
> you have multiple database connections, you might get increased memory
> efficiency by sharing between those two connections - which cannot
> happen with lookaside.
>
> The page cache is going to be the biggest user of memory.  The page
> cache memory will probably be measured in megabytes.  Memory used by
> lookaside is measured in kilobytes.  A few dozen KB of additional
> memory assigned to lookaside won't make that much difference in your
> overall memory usage, but it will make a difference in performance.
> So it seems to me to be worth the tradeoff, even if memory is tight.
>
> The reason I suggested using sqltie3_db_config() to assign a static
> buffer for lookaside is so that the lookaside subsystem will not go to
> the heap to get its (default) 50K allocation.  The MEMSYS5 memory
> allocator is a first-fit power-of-two memory allocator specifically
> designed to avoid memory fragmentation and hence allow applications to
> be designed that are guaranteed to never fail a memory allocation.
> But doing large heap allocations (more than 2K or 4K) tends to defeat
> the anti-fragmentation properties of MEMSYS5.   Hence, we desire to
> avoid the 50K heap allocation for the initial lookaside buffer.  One
> could, as you observe, achieve the same result by turning lookaside
> off all together, but then you take a performance hit.
>
> D. Richard Hipp
> d...@hwaci.com
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database Corruption

[D. Richard Hipp]

On Apr 21, 2009, at 1:21 PM, PS SP wrote:

> I am considering SQLite for a project that would create a fairly large
> database (order of 50 million rows, max 100 million rows). I'll be  
> using the
> C API. The DB is mostly used for queries with relatively fewer  
> inserts and
> updates. All updates and inserts will be done in batches and will be
> scheduled once or twice per day.
> While discussing this with other folks at work who have used SQLite  
> in the
> past, I heard someone say that they have experienced sporadic database
> corruptions in the field. Unfortunately more details are not  
> available at
> this point. The only other information is that the environment is  
> Windows.
> This is obviously a source of concern for me.
>
> Has anyone else experienced DB corruptions? Are there any things  
> that could
> trigger DB corruption -- for example, updates to indices on large  
> data sets?
> For example, in my use case because I can do batch updates, dropping  
> indices
> before updates and inserts and recreating them later is an option  
> (if it
> removes the possibility of DB corruption). Any input on this would  
> be very
> valuable to me and the community.


Background material on this is at http://www.sqlite.org/atomiccommit.html 
  and especially section 9.0:  Things that can go wrong.

There have been bugs in SQLite in the past that would result in  
database corruption.  But they have been very obscure and they are now  
all fixed as far as we know.  And we do test for this kind of thing  
very carefully.   http://www.sqlite.org/testing.html

Most database corruption problems come about when hardware loses power  
in the middle of a transaction and the disk synchronization primitives  
in the operating system or disk controller or disk hardware do not  
work as advertised.



D. Richard Hipp
d...@hwaci.com



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Difference between all the joins

[Andrey Fedorov]

Hi all,
According to the join-op syntax [1], SQLite has 13 distinct join statements:

,
JOIN
LEFT JOIN
OUTER JOIN
LEFT OUTER JOIN
INNER JOIN
CROSS JOIN
NATURAL JOIN
NATURAL LEFT JOIN
NATURAL OUTER JOIN
NATURAL LEFT OUTER JOIN
NATURAL INNER JOIN
NATURAL CROSS JOIN

Are they all unique? Which are equivalent?

- Andrey

1. http://sqlite.org/syntaxdiagrams.html#join-op
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Database Corruption

[PS SP]

I am considering SQLite for a project that would create a fairly large
database (order of 50 million rows, max 100 million rows). I'll be using the
C API. The DB is mostly used for queries with relatively fewer inserts and
updates. All updates and inserts will be done in batches and will be
scheduled once or twice per day.
While discussing this with other folks at work who have used SQLite in the
past, I heard someone say that they have experienced sporadic database
corruptions in the field. Unfortunately more details are not available at
this point. The only other information is that the environment is Windows.
This is obviously a source of concern for me.

Has anyone else experienced DB corruptions? Are there any things that could
trigger DB corruption -- for example, updates to indices on large data sets?
For example, in my use case because I can do batch updates, dropping indices
before updates and inserts and recreating them later is an option (if it
removes the possibility of DB corruption). Any input on this would be very
valuable to me and the community.

Thanks,
PS
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite syntax railroad diagrams

[Kees Nuyt]

On Tue, 21 Apr 2009 12:04:58 -0400, Andrey Fedorov
 wrote:

>Hi All,
>
>Does anyone know how the railroad-style syntax diagrams on this page were
>made?
>
>http://sqlite.org/syntaxdiagrams.html

According to 
From: "D. Richard Hipp" 
Date: Mon, 20 Apr 2009 06:53:37 -0400 :

http://wiki.tcl.tk/21708

>They're very nice :)
>
>Cheers,
>Andrey
-- 
  (  Kees Nuyt
  )
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ResultSetMetaData with javasqlite

[Justin Deoliveira]

Hi Christian,

Thanks for the quick response. I am not sure if this will be of any help 
or not but I wrote this (very crude) patch to get around the problem:

http://files.opengeo.org/sqlite_jni.c.patch

I say "crude" because my C hacking skills leave something to be desired. 
That and it only handles the HAVE_SQLITE3 path, not the HAVE_SQLITE2 or 
HAVE_BOTH paths.

-Justin

Christian Werner wrote:
> Justin Deoliveira wrote:
> 
> Justin,
> 
>> I have ran into an issue while using the javasqlite jdbc wrapper.The
>> issue can be illustrated with the following code:
>> ...
> 
> you're right, I've verified that behaviour with SQLite 3.x databases.
> The problem cannot be observed with SQLite 2.x databases.
> 
>> Basically it seems that the result set metdata is not properly
>> initialized when a query returns no rows.
>>
>> I guess my question is is this a bug or is it intended behavior?
> 
> Unintended consequences but present since the initial support
> for SQLite 3.x in 2004. Thus let's call it a misfeature. I'm
> working on a solution.
> 
> Best regards,
> Christian
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


-- 
Justin Deoliveira
OpenGeo - http://opengeo.org
Enterprise support for open source geospatial.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite syntax railroad diagrams

[Andrey Fedorov]

Thank you kindly!

On Tue, Apr 21, 2009 at 12:12 PM, D. Richard Hipp  wrote:

>
> On Apr 21, 2009, at 12:04 PM, Andrey Fedorov wrote:
>
> > Hi All,
> >
> > Does anyone know how the railroad-style syntax diagrams on this page
> > were
> > made?
> >
> > http://sqlite.org/syntaxdiagrams.html
> >
>
>
> I just put up a new FAQ for this.  http://www.sqlite.org/faq.html#q25
>
>
> D. Richard Hipp
> d...@hwaci.com
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite syntax railroad diagrams

[D. Richard Hipp]

On Apr 21, 2009, at 12:04 PM, Andrey Fedorov wrote:

> Hi All,
>
> Does anyone know how the railroad-style syntax diagrams on this page  
> were
> made?
>
> http://sqlite.org/syntaxdiagrams.html
>


I just put up a new FAQ for this.  http://www.sqlite.org/faq.html#q25


D. Richard Hipp
d...@hwaci.com



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLite syntax railroad diagrams

[Andrey Fedorov]

Hi All,

Does anyone know how the railroad-style syntax diagrams on this page were
made?

http://sqlite.org/syntaxdiagrams.html


They're very nice :)

Cheers,
Andrey
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite crashing on iPhone (or so says Apple)

[D. Richard Hipp]

On Apr 21, 2009, at 11:10 AM, Mark Spiegel wrote:

> I'm a bit confused by the following:
>
> "The assign 100K or so to each database connection's lookaside memory
> allocator using sqlite3_db_config(SQLITE_DBCONFIG_LOOKASIDE, ...)
> immediately after it is opened."
>
> If memory is at a premium, why would you reserve a large amount of it
> for SQLite's "look aside allocator"?  (It's really a zone allocator.)
> This SQLite mechanism ostensibly attempts to trade memory for  
> speed.  If
> memory is at a premium, in this case a fixed upper bound, that trade  
> off
> doesn't seem to make sense.  I would think in a case where memory is
> tight, zero bytes should be reserved.
>

This is a reasonable observation.

On the other hand, the lookaside memory allocator (which is just a  
zone allocator, as you observe) makes a big performance difference.   
And if you only have a single database connection, it doesn't really  
matter if the memory goes into lookaside or is in the global heap.  If  
you have multiple database connections, you might get increased memory  
efficiency by sharing between those two connections - which cannot  
happen with lookaside.

The page cache is going to be the biggest user of memory.  The page  
cache memory will probably be measured in megabytes.  Memory used by  
lookaside is measured in kilobytes.  A few dozen KB of additional  
memory assigned to lookaside won't make that much difference in your  
overall memory usage, but it will make a difference in performance.   
So it seems to me to be worth the tradeoff, even if memory is tight.

The reason I suggested using sqltie3_db_config() to assign a static  
buffer for lookaside is so that the lookaside subsystem will not go to  
the heap to get its (default) 50K allocation.  The MEMSYS5 memory  
allocator is a first-fit power-of-two memory allocator specifically  
designed to avoid memory fragmentation and hence allow applications to  
be designed that are guaranteed to never fail a memory allocation.   
But doing large heap allocations (more than 2K or 4K) tends to defeat  
the anti-fragmentation properties of MEMSYS5.   Hence, we desire to  
avoid the 50K heap allocation for the initial lookaside buffer.  One  
could, as you observe, achieve the same result by turning lookaside  
off all together, but then you take a performance hit.

D. Richard Hipp
d...@hwaci.com



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite crashing on iPhone (or so says Apple)

[Mark Spiegel]

I'm a bit confused by the following:

"The assign 100K or so to each database connection's lookaside memory 
allocator using sqlite3_db_config(SQLITE_DBCONFIG_LOOKASIDE, ...) 
immediately after it is opened."

If memory is at a premium, why would you reserve a large amount of it 
for SQLite's "look aside allocator"?  (It's really a zone allocator.)  
This SQLite mechanism ostensibly attempts to trade memory for speed.  If 
memory is at a premium, in this case a fixed upper bound, that trade off 
doesn't seem to make sense.  I would think in a case where memory is 
tight, zero bytes should be reserved.

Jason Boehle wrote:
 I have written an application for the iPhone called Grocery iQ that
 uses SQLite.  I don't link to or use the built-in SQLite library on
 the iPhone.  Instead, I compile the SQLite amalgamation into the
 executable.  The SQLite version currently being used in our app is
 3.6.7.
 
>>> I sent instructions to Brian Killen on how you can download the latest
>>> version of SQLite+CEROD.  Perhaps recompiling will help.
>>>   
>
> Are there any particular bug fixes or changes that you know of that
> might address my problem?  I'm all for upgrading the SQLite version,
> it's just that we will have to do several days of testing to verify it
> works well, resubmit to Apple, then wait 5+ days to hear from them if
> it works or not.  Although given their tech support response times, we
> may have all of that done before I ever hear back from them.
>
>   
 * before opening the database, the only other SQLite API calls are:
   sqlite3_config(SQLITE_CONFIG_HEAP, &mSqliteMemory[0], 3145728,
 512); // mSqliteMemory is declared as: unsigned char
 mSqliteMemory[3145728];
 
>>> You will probably do better to allocate most of that 3MB to page cache
>>> using sqlite3_config(SQLITE_CONFIG_PAGECHACHE, ...).  The assign 100K
>>> or so to each database connection's lookaside memory allocator using
>>> sqlite3_db_config(SQLITE_DBCONFIG_LOOKASIDE, ...) immediately after it
>>> is opened.  With the above, usually a 100K or so is enough heap,
>>> though more might be required if you are holding many prepared
>>> statements or if you are using unusually big prepared statements.
>>>
>>> Oops.  I'm late for meeting.  More to follow later tonight.
>>>   
>> As I was saying
>>
>> Use sqlite3_status() to actually measure your memory usage.  Make
>> adjustments once you know how the memory is being used.  Don't guess;
>> measure. Also remember that later versions of SQLite use less memory
>> for storing prepared statements, so you might want to upgrade if
>> memory is an issue.  Limit your cache sizes using the cache_size
>> pragma.  Make use of sqlite3_soft_heap_limit() if you need to.  Or
>> right a custom pcache implementation that limits the amount of memory
>> used for the page cache.
>> 
>
> Thank you for the tips on tuning the memory usage.  I will definitely
> use this advice when working on Grocery iQ 2.0.  The way I have it
> working now though, I shouldn't be experiencing any problems like
> Apple has reported, right?  If SQLite fails any allocations, it should
> return an error and fail gracefully, correct?
>
> -Jason
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>   
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Memory Usage

[D. Richard Hipp]

On Apr 21, 2009, at 10:54 AM, Marco Bambini wrote:

> and the read lock is released when the virtual machine is finalized  
> right? ... even if the writer is inside a BEGIN IMMEDIATE transaction?
>

Correct.

D. Richard Hipp
d...@hwaci.com



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Memory Usage

[D. Richard Hipp]

On Apr 21, 2009, at 9:25 AM, Marco Bambini wrote:

> cache_size is set to default 2000, page size is 1K...
>
> here you go the output of sqlite3_status:
> 2009-04-21 15:24:25   SQLITE_STATUS_MEMORY_USED current: 106704136 high:
> 109873952
> 2009-04-21 15:24:25   SQLITE_STATUS_PAGECACHE_USED current: 0 high: 0
> 2009-04-21 15:24:25   SQLITE_STATUS_PAGECACHE_OVERFLOW current: 4816736
> high: 4819808

Dan tells me that if another process has a read lock on the database  
file while your process is running the large transaction, then the  
size of the page cache can grow without bound.  That seem likely what  
is happening here.

>
> 2009-04-21 15:24:25   SQLITE_STATUS_SCRATCH_USED current: 0 high: 0
> 2009-04-21 15:24:25   SQLITE_STATUS_SCRATCH_OVERFLOW current: 0 high:  
> 6664
> 2009-04-21 15:24:25   SQLITE_STATUS_MALLOC_SIZE current: 1014 high:  
> 52000
> 2009-04-21 15:24:25   SQLITE_STATUS_PARSER_STACK current: 0 high: 0
> 2009-04-21 15:24:25   SQLITE_STATUS_PAGECACHE_SIZE current: 0 high: 0
> 2009-04-21 15:24:25   SQLITE_STATUS_SCRATCH_SIZE current: 4480 high:  
> 6664
>
> -- 
> Marco Bambini
> http://www.sqlabs.com
> http://www.creolabs.com/payshield/
>
>
>
>
>
> On Apr 21, 2009, at 3:12 PM, D. Richard Hipp wrote:
>
>>
>> On Apr 21, 2009, at 9:07 AM, Marco Bambini wrote:
>>
>>> The database is on-disk ... does huge not committed transactions  
>>> uses
>>> memory?
>>>
>>
>> It should do writes to disk periodically to free up memory, once you
>> hit your cache_size limit.
>>
>> What is cache_size set to.  What are the output from sqlite3_status()
>> telling you about memory usage?
>>
>> D. Richard Hipp
>> d...@hwaci.com
>>
>>
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

D. Richard Hipp
d...@hwaci.com



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Memory Usage

[D. Richard Hipp]

On Apr 21, 2009, at 9:25 AM, Marco Bambini wrote:

> cache_size is set to default 2000, page size is 1K...
>
> here you go the output of sqlite3_status:
> 2009-04-21 15:24:25   SQLITE_STATUS_MEMORY_USED current: 106704136 high:
> 109873952
> 2009-04-21 15:24:25   SQLITE_STATUS_PAGECACHE_USED current: 0 high: 0
> 2009-04-21 15:24:25   SQLITE_STATUS_PAGECACHE_OVERFLOW current: 4816736
> high: 4819808

OK.  I'll see if I can reproduce the problem here


>
> 2009-04-21 15:24:25   SQLITE_STATUS_SCRATCH_USED current: 0 high: 0
> 2009-04-21 15:24:25   SQLITE_STATUS_SCRATCH_OVERFLOW current: 0 high:  
> 6664
> 2009-04-21 15:24:25   SQLITE_STATUS_MALLOC_SIZE current: 1014 high:  
> 52000
> 2009-04-21 15:24:25   SQLITE_STATUS_PARSER_STACK current: 0 high: 0
> 2009-04-21 15:24:25   SQLITE_STATUS_PAGECACHE_SIZE current: 0 high: 0
> 2009-04-21 15:24:25   SQLITE_STATUS_SCRATCH_SIZE current: 4480 high:  
> 6664
>
> -- 
> Marco Bambini
> http://www.sqlabs.com
> http://www.creolabs.com/payshield/
>
>
>
>
>
> On Apr 21, 2009, at 3:12 PM, D. Richard Hipp wrote:
>
>>
>> On Apr 21, 2009, at 9:07 AM, Marco Bambini wrote:
>>
>>> The database is on-disk ... does huge not committed transactions  
>>> uses
>>> memory?
>>>
>>
>> It should do writes to disk periodically to free up memory, once you
>> hit your cache_size limit.
>>
>> What is cache_size set to.  What are the output from sqlite3_status()
>> telling you about memory usage?
>>
>> D. Richard Hipp
>> d...@hwaci.com
>>
>>
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

D. Richard Hipp
d...@hwaci.com



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Memory Usage

[Marco Bambini]

cache_size is set to default 2000, page size is 1K...

here you go the output of sqlite3_status:
2009-04-21 15:24:25 SQLITE_STATUS_MEMORY_USED current: 106704136 high:  
109873952
2009-04-21 15:24:25 SQLITE_STATUS_PAGECACHE_USED current: 0 high: 0
2009-04-21 15:24:25 SQLITE_STATUS_PAGECACHE_OVERFLOW current: 4816736  
high: 4819808
2009-04-21 15:24:25 SQLITE_STATUS_SCRATCH_USED current: 0 high: 0
2009-04-21 15:24:25 SQLITE_STATUS_SCRATCH_OVERFLOW current: 0 high: 6664
2009-04-21 15:24:25 SQLITE_STATUS_MALLOC_SIZE current: 1014 high: 52000
2009-04-21 15:24:25 SQLITE_STATUS_PARSER_STACK current: 0 high: 0
2009-04-21 15:24:25 SQLITE_STATUS_PAGECACHE_SIZE current: 0 high: 0
2009-04-21 15:24:25 SQLITE_STATUS_SCRATCH_SIZE current: 4480 high: 6664

-- 
Marco Bambini
http://www.sqlabs.com
http://www.creolabs.com/payshield/





On Apr 21, 2009, at 3:12 PM, D. Richard Hipp wrote:

>
> On Apr 21, 2009, at 9:07 AM, Marco Bambini wrote:
>
>> The database is on-disk ... does huge not committed transactions uses
>> memory?
>>
>
> It should do writes to disk periodically to free up memory, once you
> hit your cache_size limit.
>
> What is cache_size set to.  What are the output from sqlite3_status()
> telling you about memory usage?
>
> D. Richard Hipp
> d...@hwaci.com
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Memory Usage

[D. Richard Hipp]

On Apr 21, 2009, at 9:07 AM, Marco Bambini wrote:

> The database is on-disk ... does huge not committed transactions uses
> memory?
>

It should do writes to disk periodically to free up memory, once you  
hit your cache_size limit.

What is cache_size set to.  What are the output from sqlite3_status()  
telling you about memory usage?

D. Richard Hipp
d...@hwaci.com



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Memory Usage

[Marco Bambini]

The database is on-disk ... does huge not committed transactions uses  
memory?

-- 
Marco Bambini
http://www.sqlabs.com
http://www.creolabs.com/payshield/





On Apr 21, 2009, at 2:27 PM, D. Richard Hipp wrote:

>
> On Apr 21, 2009, at 8:22 AM, Marco Bambini wrote:
>
>> Yes, executing sqlite3_memory_used () after 183,000 INSERT statement
>> returns: 106,766,848.
>> Database is never closed during application lifetime.
>> Each statement is prepared, stepped and properly finalized.
>>
>
> We do that kind of test all the time but we never get huge memory
> usage like this.  Are you sure you have opened on on-disk database and
> not an in-memory database?
>
> D. Richard Hipp
> d...@hwaci.com
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] call PRAGMA page_size twice?

[P Kishor]

On Mon, Apr 20, 2009 at 11:07 PM, julian qian  wrote:
> HI,
> call PRAGMA page_size =xxx twice immediately, only first time it has
> effect, immediately call it second times, the value can't be changed.
> is this predefined? http://www.sqlite.org/pragma.html#pragma_page_size
> only say "The page_size pragma will only cause an immediate change in
> the page size if it is issued while the database is still empty, prior
> to the first CREATE TABLE statement"
>


[12:58 PM] ~/Projects/model_example/results$sqlite3 foo.db
SQLite version 3.6.11
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> pragma page_size;
1024
sqlite> pragma page_size = 2048;
sqlite> pragma page_size;
2048
sqlite> pragma page_size = 4096;
sqlite> pragma page_size;
4096
sqlite>

-- 
Puneet Kishor http://www.punkish.org/
Carbon Model http://carbonmodel.org/
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org/
Science Commons Fellow, Geospatial Data http://sciencecommons.org
Nelson Institute, UW-Madison http://www.nelson.wisc.edu/
---
collaborate, communicate, compete
===
Sent from Madison, WI, United States
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Memory Usage

[D. Richard Hipp]

On Apr 21, 2009, at 8:22 AM, Marco Bambini wrote:

> Yes, executing sqlite3_memory_used () after 183,000 INSERT statement
> returns: 106,766,848.
> Database is never closed during application lifetime.
> Each statement is prepared, stepped and properly finalized.
>

We do that kind of test all the time but we never get huge memory  
usage like this.  Are you sure you have opened on on-disk database and  
not an in-memory database?

D. Richard Hipp
d...@hwaci.com



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Memory Usage

[Marco Bambini]

Yes, executing sqlite3_memory_used () after 183,000 INSERT statement  
returns: 106,766,848.
Database is never closed during application lifetime.
Each statement is prepared, stepped and properly finalized.

P.S. the database is encrypted using your modules.
-- 
Marco Bambini
http://www.sqlabs.com
http://www.creolabs.com/payshield/





On Apr 21, 2009, at 2:11 PM, D. Richard Hipp wrote:

>
> On Apr 21, 2009, at 8:08 AM, Marco Bambini wrote:
>
>> Hello guys,
>>
>> I am trying to write you again about a simple question... how can I
>> limit sqlite memory usage during insert commands? It seems that the
>> amount of memory usage increases when the number of objects inserted
>> into the database is increased and memory is never freed.
>>
>> I tried to set PRAGMA cache_size with no luck ... I tried also to
>> recompile with SQLITE_ENABLE_MEMORY_MANAGMENT and use
>> sqlite3_soft_heap_limit but nothing seems to change.
>
>
> How do you know that memory usage is increasing?  Are you using
> sqlite3_status() and/or sqlite3_memory_used() to measure memory usage?
>
>
> D. Richard Hipp
> d...@hwaci.com
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Memory Usage

[D. Richard Hipp]

On Apr 21, 2009, at 8:08 AM, Marco Bambini wrote:

> Hello guys,
>
> I am trying to write you again about a simple question... how can I
> limit sqlite memory usage during insert commands? It seems that the
> amount of memory usage increases when the number of objects inserted
> into the database is increased and memory is never freed.
>
> I tried to set PRAGMA cache_size with no luck ... I tried also to
> recompile with SQLITE_ENABLE_MEMORY_MANAGMENT and use
> sqlite3_soft_heap_limit but nothing seems to change.


How do you know that memory usage is increasing?  Are you using  
sqlite3_status() and/or sqlite3_memory_used() to measure memory usage?


D. Richard Hipp
d...@hwaci.com



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Memory Usage

[Marco Bambini]

Hello guys,

I am trying to write you again about a simple question... how can I  
limit sqlite memory usage during insert commands? It seems that the  
amount of memory usage increases when the number of objects inserted  
into the database is increased and memory is never freed.

I tried to set PRAGMA cache_size with no luck ... I tried also to  
recompile with SQLITE_ENABLE_MEMORY_MANAGMENT and use  
sqlite3_soft_heap_limit but nothing seems to change.

Please help.
Thanks.
-- 
Marco Bambini


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] which tool do they use to generate the SQL Syntax diagrams?

[Jens Miltner]

FWIW - you can still get a textual description at 
...



Am 20.04.2009 um 15:59 schrieb J. King:

> On Mon, 20 Apr 2009 08:59:02 -0400, Jean-Denis Muys  >
> wrote:
>
>>> It's a shame: I far preferred the BNF: more compact, not to  
>>> mention you
>>> could copy and paste as well as search the text of the syntax  
>>> itself.
>>
>> "shame"? Are you sure that's the word you wanted to use?
>
> Quite sure, yes.
>
>> - compactness: very weak argument, maybe even a strawman.
>
> Merely poorly articulated; my apologies.
>
>> - copy & paste: the last time I wanted to copy & paste the formal  
>> syntax
>> of
>> any language was... about right around when dinosaurs went extinct.
>
> I've found it useful in helping (remote) colleagues and  
> acquaintances less
> familiar with SQLite.
>
>> - search: I don't think there is any valid or interesting search of  
>> BNF
>> except for the keywords of the language, which *are* searchable  
>> with the
>> syntax diagrams.
>
> I've grown very used to using inline searching with my Web browser:  
> it was
> very useful for me to highlight and isolate specific keywords in a  
> given
> syntax definition, as they appear in context.
>
>> In all these "read-only" situations, you want the grammar to be very
>> fast to read and understand.
>>
>> This is where syntax diagrams excel: the cognitive load to understand
>> them is far less than BNF (for the majority of people).
>
> I must be unusual, then, because I find that a compact representation
> allows me to see the whole very quickly, while also being able to  
> focus on
> a single part.  Perhaps what trips me up about the current syntax  
> diagrams
> is their two-dimentional nature: thereas the BNF was read linearly,
> options in the syntax diagrams are arranged perpendicular to the  
> flow of
> the syntax, and once one runs out of horizontal space for the diagram
> (which seems to happen faster with such images and cannot be  
> alleviated by
> increasing one's line length) the directions of the lines and arrows  
> can
> get fairly difficult to decypher.
>
> That the BNF was expressed in colour-coded text helped considerably  
> also:
> could this not be done with the syntax diagrams (assuming it can be  
> done
> in such a way that they don't end up looking gaudy)?
>
> Obviously this is all a matter of personal preference, and my aim  
> was not
> to start an argument by expressing mine, Jean-Denis.  If I am among  
> the
> minority, so be it.  Fortunately I am by now sufficiently familiar  
> with
> SQLite's syntax that the diagrams suffice when I need a refresher. :)
>
> -- 
> J. King
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] which tool do they use to generate the SQL Syntax diagrams?

[ntr]

Very thanks!

2009/4/20 D. Richard Hipp 

>
> On Apr 20, 2009, at 12:38 AM, ntr wrote:
>
>  Hi all,
>>
>> Is any one knows what tool do they use to generate the SYNTAX (railroad)
>> diagrams
>> that describing the  SQL Syntax ??
>>
>> (on http://www.sqlite.org/syntaxdiagrams.html)
>>
>
> http://wiki.tcl.tk/21708
>
> D. Richard Hipp
> d...@hwaci.com
>
>
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] retrieval of blobs stored in sqlite

[Kees Nuyt]

On Tue, 21 Apr 2009 08:02:45 + (GMT), Martin Pfeifle
 wrote:

>Hi,
>I have a question regarding the retrieval of BLOBs.
>Assume you have a table mytable(id, blob) and the page size is 1k.
>If we now carry out an sql-query like "select blob from mytable
>where id=4711" and the blob size is 100k.
>Am I then correct that the pager asks 100 times for a page of
>size 1k (going through the linked list of overflow pages)
>and that in whatever virtual file system, we do 100 times
>a seek operation to the currently requested page.
>Is this correct or am I here mistaken?
>Best Martin

That's correct, though the number of seeks might be lower
because some (many) of those overflow pages will be
sequential, depending on the fragmentation rate.

I think you would benefit from a larger page size.
Run benchmarks to find the optimum for your environment and
application.
-- 
  (  Kees Nuyt
  )
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ResultSetMetaData with javasqlite

[Christian Werner]

Justin Deoliveira wrote:

Justin,

> I have ran into an issue while using the javasqlite jdbc wrapper.The
> issue can be illustrated with the following code:
> ...

you're right, I've verified that behaviour with SQLite 3.x databases.
The problem cannot be observed with SQLite 2.x databases.

> Basically it seems that the result set metdata is not properly
> initialized when a query returns no rows.
> 
> I guess my question is is this a bug or is it intended behavior?

Unintended consequences but present since the initial support
for SQLite 3.x in 2004. Thus let's call it a misfeature. I'm
working on a solution.

Best regards,
Christian
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] retrieval of blobs stored in sqlite

[Martin Pfeifle]

Hi,
I have a question regarding the retrieval of BLOBs.
Assume you have a table mytable(id, blob) and the page size is 1k.
If we now carry out an sql-query like "select blob from mytable where id=4711" 
and the blob size is 100k.
Am I then correct that the pager asks 100 times for a page of size 1k (going 
through the linked list of overflow pages)
and that in whatever virtual file system, we do 100 times a seek operation to 
the currently requested page.
Is this correct or am I here mistaken?
Best Martin



  
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users