[sqlite] Index generation efficiency

[Mathieu Blondel]

Hi everyone,
I'm using sqlite3 for my project, Nihongo Benkyo, which is a japanese 
dictionary and learning tool. 

This program can import data from files in various formats in the sqlite 
database. Generally speaking, one import does about 500,000 INSERT queries 
in a single transaction and it is working fine. 

The main benefit from using a transaction is to reduce the number of disc 
accesses if my understanding is correct. However, if I put indexes on some 
columns, the import becomes very slow. I don't know the sqlite code but it 
sounds to me like the indexes are generated on the fly which seems to 
require a lot of work. 

I found out it is much more efficient to drop all the indexes, do my big 
transaction and then recreate all the indexes. Dropping indexes is not a 
really long process and just creating the indexes after the end of the 
transaction seems quicker. But if think it is quite dirty to do so everytime 
I import new data in the database. 

I guess there may be good reasons not to generate the indexes all at once at 
the end of the transaction. So first I would be glad to have some 
explanation about this. Then, do you have a better solution than mine to 
speed up the indexes generation ? Is it for example possible to ask 
explicitly sqlite not to update an index and then ask explicitly "by hand" 
to update the indexes. 


Thanks,
Mathieu. 

[sqlite] F.W.A. van Leeuwen is out of the office.

[F.W.A. van Leeuwen]

I will be out of the office starting  2005-07-25 and will not return until
2005-08-19.

I will respond to your message when I return.

[sqlite] No Timeout during commit?

[R S]

Hi,
   I have 2 process accessing the DB, one reading and the other
writing. Often the process reading the DB could take long and could
block the other process from committing a bunch of records to the DB.
I noticed that when the reader process has the lock and the writer
process tries to commit a bunch of records in the DB, the writer
blocks forever till it obtains the lock? Is this intended? I could
also always try to commit a little later and the approach works well
for my particular app. Can the behavior be changed?
Thanks.

[sqlite] problems to install pysqlite

[SKORPIO-INFO]

SKORPIO-INFO ha scritto:

  
Accidents!! I do not succeed re-install pysqlite!!! they give always
this error to me!!
  
  
  
You can help me?
  
p.s.  with all the versions, also the previous ones!!

Re: [sqlite] Limit how much data to read

[Kurt Welgehausen]

> Where in the documentation that explains how to use the sqlite substr() 
> function?

Go to the SQLite Syntax page, and click on 'expression'.

RE: [sqlite] Limit how much data to read - substr syntax

[Griggs, Donald]

Regarding: Where in the documentation that explains how to use the sqlite
substr() function?

Hi Shawn,

>From the main sqlite.org page, take the SYNTAX link, then EXPRESSIONS

http://www.sqlite.org/lang_expr.html



substr(X,Y,Z)   Return a substring of input string X that begins with the
Y-th character and which is Z characters long. The left-most character of X
is number 1. If Y is negative the the first character of the substring is
found by counting from the right rather than the left. If SQLite is
configured to support UTF-8, then characters indices refer to actual UTF-8
characters, not bytes.


Donald Griggs

Opinions are not necessarily those of Misys Healthcare Systems nor its board
of directors.

Re: [sqlite] Iterating a result from a query

[Jay Sprenkle]

> > I cache the items I'm displaying in an STL vector (C++). If the user moves
> > to a page where I have no data I load it from the database as needed. I was
> > considering loading data in the background in case the user might need it
> > since I can predict what they might do relatively easily.
> 
> Prefetching sounds like a wonderfull idea, it is definitely in a phase +2 from
> current phase, but if the concept is working it might make the flow a lot
> smoother.

On one project they demanded response time be below 3 seconds.
I had to do all of these things to get it that fast. Good luck!

Re: [sqlite] Iterating a result from a query

[Kim Bendtsen]

>
> I cache the items I'm displaying in an STL vector (C++). If the user moves
> to a page where I have no data I load it from the database as needed. I was
> considering loading data in the background in case the user might need it
> since I can predict what they might do relatively easily.

Prefetching sounds like a wonderfull idea, it is definitely in a phase +2 from 
current phase, but if the concept is working it might make the flow a lot 
smoother. 

Cheers
Kim B.

Re: [sqlite] Iterating a result from a query

[Kim Bendtsen]

Thank you for your thoughts. 

> You can build the result set into an array, then step through, back and
> around the array.
>
> SQLite already provides this functionality:
> http://www.sqlite.org/capi3ref.html#sqlite3_get_table

Having the entire result in memory would be an easy solution but unfortunately 
it will take up to much memory. I think I will keep an array of rowIds for 
the entries in the result, sort of a cache. Whenever the user scrolls to a 
position where I don't have the data, then I will fetch it for him and cache 
the id. That way it is easy to create random access via the cached rowIds and 
I only need to store. 

> The only downside is that you'll have the full result set in memory, even
> if you are only using a small portion of it.
>
> If you want to limit the amount of data at any time, you can use the:
>
>  SELECT ... LIMIT n OFFSET m;
>
> form of select, where n is the number of rows you want, and m is the
> offset you want to start from. For a table view, this may be appropriate,
> but you have to recompute the results for each change of page, potentially
> slowing your app down for large queries.

For the relatively simple tables that we have at the moment, I don't think its 
an issue to make one lookup per item when scrolling. But tests will show 
that. 

> >Cheers
> >Kim B.

Re: [sqlite] Limit how much data to read

[Shawn Walker]

Where in the documentation that explains how to use the sqlite substr() 
function?


Cory Nelson wrote:

Try the substr() function

On 7/25/05, Shawn Walker <[EMAIL PROTECTED]> wrote:


Is there a way to tell sqlite to read up to X bytes?  For example, there
are some data that can be quite large, but I don't need all of them,
just a little bit of it to show the user some of the data and they can
select that data to get the rest of the data from the DB.

Re: [sqlite] Query locking up SQLite

[David Fowler]

> SQLite Version 3.2.2.
> Is this a bug, or is my SQL that bad?
> Query 1:
> SELECT * FROM table1, table2
> WHERE (table1.value LIKE "%value%" AND table1.table2_id = table2.id);
> This query works perfectly, can't fault it.
> But when I do this, SQLite locks out (Command line interface, and PHP5)
> Query 2:
> SELECT * FROM table1, table2
> WHERE ((table1.value LIKE "%value%" AND table1.table2_id = table2.id);
> OR (table1.value LIKE "%different_value%" AND table1.table2_id =
> table2.id));
> This query (and even more complex versions of it) works in MySQL (Haven't
> tried another DB yet) and I'm trying to migrate to SQLite, this is really
> holding me back.

The like clause is going to force it to do a full table scan (it will
have to read the
whole database every time). If you can find a way around that it will
help a lot.
Is there an index on the id column(s)? That might help some too.
==
I don't think the LIKE is the problem, I used = there too. All the id 
columns a primary keys, so I assume that means they're indexed well enough.

Update on 5 table query:
I can now do the query with INNER JOINs, and it returns instantly with the 
correct results. The problem appears to be the order of the tables in the 
JOINs. The first table is large (4000 odd rows) and it has absolutely no 
conditions on it (the second doesn't help either). What I should have done 
was have the tables that result in not many rows first, then add others as I 
go.

Thanks for the ideas, I think I have it now (think).

- Very odd, this email was originally addressed to the poster, not the list 
e-mail address

Re: [sqlite] Iterating a result from a query

[Jay Sprenkle]

On 7/25/05, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:

> Any thoughts/brainstorming would be great from anyone :)
> 
> Personally I don't have much experience with programming a database and
> the theory in it. But I suppose that in order to be able to go backward,
> the results already iterated through must be kept(at least a reference to
> it, something like rowId). If setToLast is used, I must use the
> sqlite3_step in order to find the last entry and then cache the other
> results.
> 
> Is there an easier way??

I cache the items I'm displaying in an STL vector (C++). If the user moves
to a page where I have no data I load it from the database as needed. I was
considering loading data in the background in case the user might need it
since I can predict what they might do relatively easily.

Re: [sqlite] Multi-threading.

[Jay Sprenkle]

> Computer _Science_ is just that: Science. It's not opinion.

What they taught me at university was the current collection of "best
practices" for solving problems. "Here's a common problem, here are
the known good algorithms for solving it. Here are the techniques we
use for creating 'good' programs. You are now educated". They didn't
teach us the scientific method for solving problems.

The theory has been proposed that threads aren't better than separate
processes, or application implemented context switching. Does anyone
have an experiment that will prove the point either way? It will have
to be OS specific though, since I'm sure not all thread
implementations are equal.

Re: [sqlite] Query locking up SQLite

[Jay Sprenkle]

> SQLite Version 3.2.2.
> Is this a bug, or is my SQL that bad?
> Query 1:
> SELECT * FROM table1, table2
> WHERE (table1.value LIKE "%value%" AND table1.table2_id = table2.id);
> This query works perfectly, can't fault it.
> But when I do this, SQLite locks out (Command line interface, and PHP5)
> Query 2:
> SELECT * FROM table1, table2
> WHERE ((table1.value LIKE "%value%" AND table1.table2_id = table2.id);
> OR (table1.value LIKE "%different_value%" AND table1.table2_id =
> table2.id));
> This query (and even more complex versions of it) works in MySQL (Haven't
> tried another DB yet) and I'm trying to migrate to SQLite, this is really
> holding me back.

The like clause is going to force it to do a full table scan (it will
have to read the
whole database every time). If you can find a way around that it will
help a lot.
Is there an index on the id column(s)? That might help some too.

Re: [sqlite] Limit how much data to read

[Cory Nelson]

Try the substr() function

On 7/25/05, Shawn Walker <[EMAIL PROTECTED]> wrote:
> Is there a way to tell sqlite to read up to X bytes?  For example, there
> are some data that can be quite large, but I don't need all of them,
> just a little bit of it to show the user some of the data and they can
> select that data to get the rest of the data from the DB.
> 


-- 
Cory Nelson
http://www.int64.org

Re: [sqlite] Query locking up SQLite

[David Fowler]

Might I suggest the following --

make your query in steps, and note where it actually starts slowing down. 
For example,


Step 1.
SELECT COUNT(*)
FROM t1

(took a fraction of a pico second... good)

Step 2.
SELECT COUNT(*)
FROM t1
WHERE t1.c1 = 'c1'

(took a couple of fractions of a pico second... still good)

Step 3.
SELECT COUNT(*)
FROM t1 INNER JOIN t2 ON t1.c2 = t2.c2
WHERE t1.c1 = 'c1'

(still just a couple of fractions of a pico second... still good)

and so on... you get the picture. SQL gets complicated very rapidly, and 
only by building it step by step can we optimize it best.


That will pin-point for you where the query goes haywire. Then you can 
concentrate on fixing that. Perhaps you are not using your indexes, perhaps 
you don't have indexes, whatever. Then you can come back and ask for help on 
the specific problem. Maybe the SQLite designers might even discover a 
legitimate place for improvement in their code, and then everyone will 
benefit.


Comparing two databases is pointless because they all do things so 
differently internally even though we as users expect the same external 
results.



--
Puneet Kishor

=
Thanks for the pointer, I was just doing this myself (not intentionally 
though) by constructing my query out of nested SELECTs rather than JOINs and 
I noticed that the inner SELECTs were taking fractions of seconds, what I 
was doing in the next level up was taking a lot longer. I think the reason 
for it is table1 and table2s JOIN created alot of rows, no matter what 
conditions I placed in the ON statement, because table1 was so large and 
none of the conditions applied to it in a manner that made it more 
efficient. I guess MySQLs optimizer is really doing its job, and I need to 
do the optomising myself for SQLite.

Thanks for the input Puneet.

[sqlite] Limit how much data to read

[Shawn Walker]

Is there a way to tell sqlite to read up to X bytes?  For example, there 
are some data that can be quite large, but I don't need all of them, 
just a little bit of it to show the user some of the data and they can 
select that data to get the rest of the data from the DB.

Re: [sqlite] Query locking up SQLite

[Puneet Kishor]

On Jul 25, 2005, at 6:06 AM, David Fowler wrote:


On 2005-07-25 at 10:58:04 [+0200], David Fowler
..
===
Thanks for the pointer Charlie, but I was only using * for my example, 
and I normaly use fully named columns (table.column) when writing 
queries.


My new query, looks like this (generified):
SELECT count(*) FROM table1
INNER JOIN table2 ON (table1.id = table2.rel_id)
INNER JOIN table3 ON (table3.rel_id = table2.id)
INNER JOIN table4 ON (table3.id = table4.rel_id)
INNER JOIN table5 ON (table5.rel_id = table4.id)
WHERE table1.value = 'value1'
AND ((table3.value LIKE '%value3%' AND table5.value = 'value5')
OR (table3.value LIKE '%value3%' AND table5.value = 'value5'));

This query executes in 0.02s on MySQL, but about 14s on SQLite (to 
return 34 rows).
I think the problem is the size of the temporary table the JOIN 
creates (37,434) which is probably the issue. There is (7,579) records 
in my largest table. I'm not sure what my best option would be to get 
this size down. But I'm thinking along the lines of some nested 
SELECTs to get needed rows in stages rather than all in one go at the 
end. My application is probably going to get much larger data wise 
than it already is and MySQL is not really an option anymore. Could 
possibly go back to UNIONs also if the OR operation isn't as efficient 
as two SELECTs (though I highly doubt that is the case).



Might I suggest the following --

make your query in steps, and note where it actually starts slowing 
down. For example,


Step 1.
SELECT COUNT(*)
FROM t1

(took a fraction of a pico second... good)

Step 2.
SELECT COUNT(*)
FROM t1
WHERE t1.c1 = 'c1'

(took a couple of fractions of a pico second... still good)

Step 3.
SELECT COUNT(*)
FROM t1 INNER JOIN t2 ON t1.c2 = t2.c2
WHERE t1.c1 = 'c1'

(still just a couple of fractions of a pico second... still good)

and so on... you get the picture. SQL gets complicated very rapidly, 
and only by building it step by step can we optimize it best.


That will pin-point for you where the query goes haywire. Then you can 
concentrate on fixing that. Perhaps you are not using your indexes, 
perhaps you don't have indexes, whatever. Then you can come back and 
ask for help on the specific problem. Maybe the SQLite designers might 
even discover a legitimate place for improvement in their code, and 
then everyone will benefit.


Comparing two databases is pointless because they all do things so 
differently internally even though we as users expect the same external 
results.



--
Puneet Kishor

Re: [sqlite] Query locking up SQLite

[David Fowler]

I think if you try this with MySQL MyISAM you will have the same result and 
I

think this is to do with the SELECT *

I remember I used to have something similar back in the days when I tried
MySQL.

Charlie
==
I just tried adding extra conditions in the ON clause, but its not helping, 
I think SQLite is still putting the tables all together before doing the 
conditions which is whats taking so long. Never mind though, I think I can 
manage it with subqueries and UNIONs faster, don't really wanna switch to 
somethin else, though access would be possible, I don't know if it would be 
at all efficient with all this data.

Re: [sqlite] Query locking up SQLite

[Charlie Clark]

On 2005-07-25 at 14:51:13 [+0200], David Fowler <[EMAIL PROTECTED]> 
wrote:
> Well the query isn't that strange, I have 3 values that need matching
> per-record returned, all in different tables (takes care of 3 tables) the
> actual data I want is stored in another related table, and 2 of the values
> that need to match are not related directly, so a 5th table is required for
> that relationship.
> Anyway, my original query does infact work, however it takes 10 minutes to
> run. I'm guessing this is because it generates 1,530,200,100 rows of data to
> do the WHERE clause on (just did a count(*) on it in mySQL) . Why MySQL can
> do this so much faster, 1/10th of a second I dont know (though I am using
> InnoDB not the default MyISAM engine. I image the BDB engine would be even
> faster).
> Just MySQL is to heavy to install with my application. Anyway, back to the
> drawing board for an efficient way to get my data. Thanks again.

I think if you try this with MySQL MyISAM you will have the same result and I 
think this is to do with the SELECT *

I remember I used to have something similar back in the days when I tried 
MySQL.

Charlie

Re: [sqlite] Iterating a result from a query

[Christian Smith]

On Sun, 24 Jul 2005, Kim Bendtsen wrote:

>Hi There
>
>After executing a query I get the result back and can traverse it using
>sqlite3_step. This is fine for most systems, however for what I wish to use
>SQLite for, I would need something similar to stepForward, stepBackward,
>stepToFirst, stepToLast.
>
>The application where I'm going to use it is when showing a list items from a
>database table in a window. If the window can display 5 items, there is no
>need to load more than 5 rows in the beginning. Here sqlite3_step is
>sufficient. But
>if the users press up arrow, it should take them to the bottom of the list,
>and I would like to use something similar to setToLast. If the user keep
>pressing up, I would need to stepBackward.
>I know this might not be something for the sqlite database. How would you go
>about wrapping this interface? I know it is a broad question and your
>suggestion is very much appriciated! :)


You can build the result set into an array, then step through, back and
around the array.

SQLite already provides this functionality:
http://www.sqlite.org/capi3ref.html#sqlite3_get_table

The only downside is that you'll have the full result set in memory, even
if you are only using a small portion of it.

If you want to limit the amount of data at any time, you can use the:

 SELECT ... LIMIT n OFFSET m;

form of select, where n is the number of rows you want, and m is the
offset you want to start from. For a table view, this may be appropriate,
but you have to recompute the results for each change of page, potentially
slowing your app down for large queries.


>
>
>Cheers
>Kim B.
>

Christian

-- 
/"\
\ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
 X   - AGAINST MS ATTACHMENTS
/ \

Re: [sqlite] Query locking up SQLite

[David Fowler]

On 2005-07-25 at 13:06:42 [+0200], David Fowler 
<[EMAIL PROTECTED]>

wrote:
> SELECT count(*) FROM table1
> INNER JOIN table2 ON (table1.id = table2.rel_id)
> INNER JOIN table3 ON (table3.rel_id = table2.id)
> INNER JOIN table4 ON (table3.id = table4.rel_id)
> INNER JOIN table5 ON (table5.rel_id = table4.id)
> WHERE table1.value = 'value1'
> AND ((table3.value LIKE '%value3%' AND table5.value = 'value5')
> OR (table3.value LIKE '%value3%' AND table5.value = 'value5'));
>
> This query executes in 0.02s on MySQL, but about 14s on SQLite (to return 
34

> rows).
> I think the problem is the size of the temporary table the JOIN creates
> (37,434) which is probably the issue. There is (7,579) records in my 
largest
> table. I'm not sure what my best option would be to get this size down. 
But

> I'm thinking along the lines of some nested SELECTs to get needed rows in
> stages rather than all in one go at the end. My application is probably
> going to get much larger data wise than it already is and MySQL is not
> really an option anymore. Could possibly go back to UNIONs also if the OR
> operation isn't as efficient as two SELECTs (though I highly doubt that 
is

> the case).

Run this through the analyser (EXPLAIN) which will show where the time is
spent. I would also suggest trying PostgreSQL as my experience with MySQL is
that performance degrades significantly with lots of JOINs as it just
generates lots of Cartesian products and I think SQLite is the same, ie. a
huge table is generated in memory and the conditions are applied to this row
by row.

Certainly seems a strange query, though.

Charlie

Well the query isn't that strange, I have 3 values that need matching 
per-record returned, all in different tables (takes care of 3 tables) the 
actual data I want is stored in another related table, and 2 of the values 
that need to match are not related directly, so a 5th table is required for 
that relationship.
Anyway, my original query does infact work, however it takes 10 minutes to 
run. I'm guessing this is because it generates 1,530,200,100 rows of data to 
do the WHERE clause on (just did a count(*) on it in mySQL) . Why MySQL can 
do this so much faster, 1/10th of a second I dont know (though I am using 
InnoDB not the default MyISAM engine. I image the BDB engine would be even 
faster).
Just MySQL is to heavy to install with my application. Anyway, back to the 
drawing board for an efficient way to get my data. Thanks again.

Re: [sqlite] Multi-threading.

[Ben Clewett]

Steve O'Hara wrote:

I think the point about multi-threaded apps is not that there might be a few
instances where they are useful, but how easy is it to debug and support
them and is it worth the price for a little more concurrency?


Good point.  The ones I have used are terrible.  I hope things will 
improve in time.  This is hopefully not a reason to give up.



In my experience, most debugging IDE's can't handle multiple threads and
actually turn the application into a pseudo threaded version prior to
running it.  I wouldn't mind betting that's what's going on in Java (it's
worth pointing out that Java didn't used to even support native threads and
I bet most Java programmers have no idea whether they are actually using
Native or pseudo threads)

Because of this, you get the frustrating situation where an application
works fine in debug but fails sporadically in production.  Another good real
world example of this is the IIS/ASP server side debugging.  IIS allocates a
new thread per request which are autonomous but do some semaphoring to share
the Session object (I think).  There's a handy switch you can set on your
IIS virtual directory that turns on server side debugging - great! .
only, not so great!! Suddenly IIS starts allocating the same thread to every
request i.e. it queues your requests and the very problem you were trying to
solve goes away!


:)


The moral of the story is as Richard says, if there isn't a burning need for
multiple threads then don't use them.


I have noticed something.  There are two lines of thought here.  The two 
ideas may not be too different.  (Here is where I am sure I will be 
flamed, but I am sure I'll learn something from it :)


- The single-thread multi-task option.
- The multiple-thread single thread-task.

The first option involves building a context scheduler into our own 
programs.  So GUI events, TCP listen, and everything else can occur 
without blocking.  A virtual thread environment.  As you say, some Java 
and ISS can sometimes be this.


The second option involves letting the OS do the context changes. 
Letting the programmer just code each thread in isolation.  (Programming 
in a thread-safe way.)


So in essence, the two become the same.  A switch could even be added to 
use own context switch, or use the OS.  The OS could be virtual or 
physical.


Externally it's not important: any option should yields the same function.

Therefore, programming ones own context scheduler is a bit like not 
using SQL and accessing the data file directly.  It may be faster, you 
may have more control.  But you may just be giving your self a lot of 
work where SQL does it all for you.  This is why we all like SQLite so much.


I do not know if this is the case today.  My assumption is that thread 
safe programming is easy, by using good tools and good methodology.  I 
truly believe this, and I have not experienced some of the thread 
problems other people have reported.


Considering the future of processor design, should we be getting more 
excited about threads and how we can make them work for us?




By the way, Mrs Brisby is being facetious when he says that he doesn't
regard Windows as a "modern" GUI - there's no such thing!  They've all got
their origins in concepts put forward over 20 years ago.


:)

Ben.




Steve


-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]
rg]On Behalf Of Ben Clewett
Sent: 25 July 2005 09:01
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Multi-threading.


Dear Mrs Brisby,

Thanks for your passionate replies to my original posting.  You have
much information here.  It's obvious I don't know everything about
threading.

I like what you say about computer science being a Science.  This is
exactly my point.  A science is a collection of theories which hold true
within a domain until otherwise dissproven.  For instance Isac Newtons's
law of gravety and Einstain's law.  Both are true within a fixed domain.
  Both are used today.  Neither are truelly corrrect.  There will be
another more complex theroy in time.

This is the same with Threading.  There may be places where this is
useful.  There may be places where it should not be used.  Both are
theories within computer science which hold true in a limited domain.
So when I said this was my opinion, I should have stated this is my
theory within a fixed domain.  However when you stated that I was wrong,
I don't think this is compatible of your statement that Computer Science
is a Science.  Nothing in any science is either right or wrong.  That's
a Religion.

I don't want to spend ages on the argument, I can see there are
passionate views.  I only want to explore the idea that threading is a
viable strategy for a modern high level language and can produce stable
applications with low development time.

For instance a call to a TCP blocking Wait.  It's perfectly possible for
one thread to cycle round handing GUI events, then checking on the port
for new 

Re: [sqlite] Iterating a result from a query

[kim]

> Hi There
>
> After executing a query I get the result back and can traverse it using
> sqlite3_step. This is fine for most systems, however for what I wish to
> use
> SQLite for, I would need something similar to stepForward, stepBackward,
> stepToFirst, stepToLast.
>
> The application where I'm going to use it is when showing a list items
> from a
> database table in a window. If the window can display 5 items, there is no
> need to load more than 5 rows in the beginning. Here sqlite3_step is
> sufficient. But
> if the users press up arrow, it should take them to the bottom of the
> list,
> and I would like to use something similar to setToLast. If the user keep
> pressing up, I would need to stepBackward.
> I know this might not be something for the sqlite database. How would you
> go
> about wrapping this interface? I know it is a broad question and your
> suggestion is very much appriciated! :)

Any thoughts/brainstorming would be great from anyone :)

Personally I don't have much experience with programming a database and
the theory in it. But I suppose that in order to be able to go backward,
the results already iterated through must be kept(at least a reference to
it, something like rowId). If setToLast is used, I must use the
sqlite3_step in order to find the last entry and then cache the other
results.

Is there an easier way??


Cheers
Kim B.

Re: [sqlite] Query locking up SQLite

[Charlie Clark]

On 2005-07-25 at 13:06:42 [+0200], David Fowler <[EMAIL PROTECTED]> 
wrote:
> SELECT count(*) FROM table1
> INNER JOIN table2 ON (table1.id = table2.rel_id)
> INNER JOIN table3 ON (table3.rel_id = table2.id)
> INNER JOIN table4 ON (table3.id = table4.rel_id)
> INNER JOIN table5 ON (table5.rel_id = table4.id)
> WHERE table1.value = 'value1'
> AND ((table3.value LIKE '%value3%' AND table5.value = 'value5')
> OR (table3.value LIKE '%value3%' AND table5.value = 'value5'));
> 
> This query executes in 0.02s on MySQL, but about 14s on SQLite (to return 34
> rows).
> I think the problem is the size of the temporary table the JOIN creates
> (37,434) which is probably the issue. There is (7,579) records in my largest
> table. I'm not sure what my best option would be to get this size down. But
> I'm thinking along the lines of some nested SELECTs to get needed rows in
> stages rather than all in one go at the end. My application is probably
> going to get much larger data wise than it already is and MySQL is not
> really an option anymore. Could possibly go back to UNIONs also if the OR
> operation isn't as efficient as two SELECTs (though I highly doubt that is
> the case).

Run this through the analyser (EXPLAIN) which will show where the time is 
spent. I would also suggest trying PostgreSQL as my experience with MySQL is 
that performance degrades significantly with lots of JOINs as it just 
generates lots of Cartesian products and I think SQLite is the same, ie. a 
huge table is generated in memory and the conditions are applied to this row 
by row.

Certainly seems a strange query, though.

Charlie

Re: [sqlite] Query locking up SQLite

[David Fowler]

On 2005-07-25 at 10:58:04 [+0200], David Fowler
<[EMAIL PROTECTED]> wrote:
> Thanks Charlie, thats exactly how I should be doing it. I would imagine
> there are some performance benefits from doing it this way too. Now I've
> just got to make it work for my select that involves six not two tables!
> Looks like I'll be getting my thick SQL book out for a while.

SQL in a Nutshell is pretty good.

There shouldn't be any performance benefits if the engine is working
properly but it is emminently more readable. You may well hit problems with
your SELECT * approach with names colliding, especially when you start
adding UNIONs. It is always advisable to use explicit relational variable
names (columns).

Charlie
===
Thanks for the pointer Charlie, but I was only using * for my example, and I 
normaly use fully named columns (table.column) when writing queries.


My new query, looks like this (generified):
SELECT count(*) FROM table1
INNER JOIN table2 ON (table1.id = table2.rel_id)
INNER JOIN table3 ON (table3.rel_id = table2.id)
INNER JOIN table4 ON (table3.id = table4.rel_id)
INNER JOIN table5 ON (table5.rel_id = table4.id)
WHERE table1.value = 'value1'
AND ((table3.value LIKE '%value3%' AND table5.value = 'value5')
OR (table3.value LIKE '%value3%' AND table5.value = 'value5'));

This query executes in 0.02s on MySQL, but about 14s on SQLite (to return 34 
rows).
I think the problem is the size of the temporary table the JOIN creates 
(37,434) which is probably the issue. There is (7,579) records in my largest 
table. I'm not sure what my best option would be to get this size down. But 
I'm thinking along the lines of some nested SELECTs to get needed rows in 
stages rather than all in one go at the end. My application is probably 
going to get much larger data wise than it already is and MySQL is not 
really an option anymore. Could possibly go back to UNIONs also if the OR 
operation isn't as efficient as two SELECTs (though I highly doubt that is 
the case).

Re: [sqlite] Query locking up SQLite

[Charlie Clark]

On 2005-07-25 at 10:58:04 [+0200], David Fowler 
<[EMAIL PROTECTED]> wrote:
> Thanks Charlie, thats exactly how I should be doing it. I would imagine
> there are some performance benefits from doing it this way too. Now I've
> just got to make it work for my select that involves six not two tables!
> Looks like I'll be getting my thick SQL book out for a while.

SQL in a Nutshell is pretty good.

There shouldn't be any performance benefits if the engine is working 
properly but it is emminently more readable. You may well hit problems with 
your SELECT * approach with names colliding, especially when you start 
adding UNIONs. It is always advisable to use explicit relational variable 
names (columns).

Charlie
-- 
Charlie Clark
Communications Manager
yellowTAB GmbH
Tel: +49-211-600-3657
http://www.yellowtab.com
mailto: [EMAIL PROTECTED]

RE: [sqlite] Multi-threading.

[Steve O'Hara]

I think the point about multi-threaded apps is not that there might be a few
instances where they are useful, but how easy is it to debug and support
them and is it worth the price for a little more concurrency?

In my experience, most debugging IDE's can't handle multiple threads and
actually turn the application into a pseudo threaded version prior to
running it.  I wouldn't mind betting that's what's going on in Java (it's
worth pointing out that Java didn't used to even support native threads and
I bet most Java programmers have no idea whether they are actually using
Native or pseudo threads)

Because of this, you get the frustrating situation where an application
works fine in debug but fails sporadically in production.  Another good real
world example of this is the IIS/ASP server side debugging.  IIS allocates a
new thread per request which are autonomous but do some semaphoring to share
the Session object (I think).  There's a handy switch you can set on your
IIS virtual directory that turns on server side debugging - great! .
only, not so great!! Suddenly IIS starts allocating the same thread to every
request i.e. it queues your requests and the very problem you were trying to
solve goes away!

The moral of the story is as Richard says, if there isn't a burning need for
multiple threads then don't use them.

By the way, Mrs Brisby is being facetious when he says that he doesn't
regard Windows as a "modern" GUI - there's no such thing!  They've all got
their origins in concepts put forward over 20 years ago.

Steve


-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]
rg]On Behalf Of Ben Clewett
Sent: 25 July 2005 09:01
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Multi-threading.


Dear Mrs Brisby,

Thanks for your passionate replies to my original posting.  You have
much information here.  It's obvious I don't know everything about
threading.

I like what you say about computer science being a Science.  This is
exactly my point.  A science is a collection of theories which hold true
within a domain until otherwise dissproven.  For instance Isac Newtons's
law of gravety and Einstain's law.  Both are true within a fixed domain.
  Both are used today.  Neither are truelly corrrect.  There will be
another more complex theroy in time.

This is the same with Threading.  There may be places where this is
useful.  There may be places where it should not be used.  Both are
theories within computer science which hold true in a limited domain.
So when I said this was my opinion, I should have stated this is my
theory within a fixed domain.  However when you stated that I was wrong,
I don't think this is compatible of your statement that Computer Science
is a Science.  Nothing in any science is either right or wrong.  That's
a Religion.

I don't want to spend ages on the argument, I can see there are
passionate views.  I only want to explore the idea that threading is a
viable strategy for a modern high level language and can produce stable
applications with low development time.

For instance a call to a TCP blocking Wait.  It's perfectly possible for
one thread to cycle round handing GUI events, then checking on the port
for new packets.

But an alternate method is to partition the code space into two
autonomous threads using their own variable set.  Two simple sets of
code which are not coupled, and remain highly cohesive and encapsulated.
  Communicating through a thread-safe object.  Such code is fast to
write, easy to read, robust and does the job.  I can complete this in c#
in ten minutes, and it will not break.  With large amounts of time,
better methods may be available.  But this theory is viable within its
domain.

 > I wasn't about to consider Windows a modern GUI system :)

Are you saying it's not modern, or not a GUI?  It may not be prefect and
it is very complex.  But it's not about to go away, and it's installed
on modern computers.  More importantly, my views on threads remain.  If
you give the GUI it's own thread, you have implemented a simple solution
to ensure that the correct cursor and mouse events are honoured.  Then a
worker thread is free to do what ever it likes without being coupled to
the GUI.  Simple robust coding using thread-safe objects.

I am also interested in peoples views that threading is slower.  Since
all processors are likely to be hyperthreaded, multicore, or both.  I
can see no place where this will be true in the future.

 > Java uses references, not pointers.

Is there any important difference?  My point was not about language.  It
was a question to Dr Hipp about what he feels is missing from the Java
pointer (reference) model.  Perhaps I should have explained better.

Yes, I understand that 'c' can make use of goto, and that goto is fast.
  There are also very very bad places to use goto.  Exceptions, breaks
and continue statements are linear code, easy to follow and more robust
to code changes.  Goto is a legacy of assembler 

Re: [sqlite] I still think it's a bug, 0 rows, no fieldnames

[Edwin Knoppert]

Just tested, seems to work :)

Thanks!

- Original Message - 
From: "Nuno Lucas" <[EMAIL PROTECTED]>

To: 
Sent: Monday, July 25, 2005 10:38 AM
Subject: Re: [sqlite] I still think it's a bug, 0 rows, no fieldnames


I'm not understanding what's the problem here. I always get the column 
names

even on empty result sets (so I can show them in a grid header).

I confess I haven't been using the latest sqlite versions to be sure it
is still returned, but isn't this what you want?

My guess is that maybe you are using the callback API, that need
"PRAGMA empty_result_callbacks = 1" to return the columns on empty result
sets, but I haven't used that API for a long time.

Regards,
~Nuno Lucas


[25-07-2005 9:08, Edwin Knoppert escreveu]

I might be mistaken but you should never expect row-0 for data.
What i meant is that the fieldnames are present when there is data.
And not when there is no data.
In either case, row-0 should never be threated as data row.
So one can not expect data in there.

The point is when there is data, SQLite returns the fieldnames from a 
(joined) query.

For what reason it should not return the names when there was no data?
(And not having an error of course)

I'm pretty certain this is only a technical matter, not a logical one.

schema table?
I'm not familiar with that, i don't think it is realted to fields from 
join-ed queries.


This question has not much to do with my earlier post, i know how to 
obtain the fields properly.

Re: [sqlite] I still think it's a bug, 0 rows, no fieldnames

[Edwin Knoppert]

That's good news!
Thanks, will try.

:)


- Original Message - 
From: "Nuno Lucas" <[EMAIL PROTECTED]>

To: 
Sent: Monday, July 25, 2005 10:38 AM
Subject: Re: [sqlite] I still think it's a bug, 0 rows, no fieldnames


I'm not understanding what's the problem here. I always get the column 
names

even on empty result sets (so I can show them in a grid header).

I confess I haven't been using the latest sqlite versions to be sure it
is still returned, but isn't this what you want?

My guess is that maybe you are using the callback API, that need
"PRAGMA empty_result_callbacks = 1" to return the columns on empty result
sets, but I haven't used that API for a long time.

Regards,
~Nuno Lucas


[25-07-2005 9:08, Edwin Knoppert escreveu]

I might be mistaken but you should never expect row-0 for data.
What i meant is that the fieldnames are present when there is data.
And not when there is no data.
In either case, row-0 should never be threated as data row.
So one can not expect data in there.

The point is when there is data, SQLite returns the fieldnames from a 
(joined) query.

For what reason it should not return the names when there was no data?
(And not having an error of course)

I'm pretty certain this is only a technical matter, not a logical one.

schema table?
I'm not familiar with that, i don't think it is realted to fields from 
join-ed queries.


This question has not much to do with my earlier post, i know how to 
obtain the fields properly.

Re: [sqlite] Query locking up SQLite

[David Fowler]

On 2005-07-25 at 10:15:14 [+0200], David Fowler
<[EMAIL PROTECTED]> wrote:
> SELECT * FROM table1, table2
> WHERE (table1.value LIKE "%value%" AND table1.table2_id = table2.id);
> This query works perfectly, can't fault it.
> But when I do this, SQLite locks out (Command line interface, and PHP5)
> Query 2:
> SELECT * FROM table1, table2
> WHERE ((table1.value LIKE "%value%" AND table1.table2_id = table2.id);
> OR (table1.value LIKE "%different_value%" AND table1.table2_id =
> table2.id));

I think this is a good example of why I use explicit JOINS.

SELECT * FROM table1
INNER JOIN table2 ON
(table2.id = table1.table2_id)
WHERE
table1.value LIKE '%value%' OR table1.value LIKE '%value%')

Doesn't this do what you want?
--
Charlie Clark
Communications Manager
yellowTAB GmbH
Tel: +49-211-600-3657
http://www.yellowtab.com
mailto: [EMAIL PROTECTED]


Thanks Charlie, thats exactly how I should be doing it. I would imagine 
there are some performance benefits from doing it this way too. Now I've 
just got to make it work for my select that involves six not two tables! 
Looks like I'll be getting my thick SQL book out for a while.
Though my new code did look rather promising, I think this way will probably 
be better for my multiple tables.

New Code:
SELECT * FROM table1, table2
WHERE (table1.value LIKE "%value1%" AND table1.table2_id = table2.id) GROUP 
BY table2.id

UNION
SELECT * FROM table1, table2
WHERE (table1.value LIKE "%value2%" AND table1.table2_id = table2.id) GROUP 
BY table2.id;


Now I can fix this problem and move on. Thanks again.
Dave.

Re: [sqlite] Query locking up SQLite

[Charlie Clark]

On 2005-07-25 at 10:15:14 [+0200], David Fowler 
<[EMAIL PROTECTED]> wrote:
> SELECT * FROM table1, table2
> WHERE (table1.value LIKE "%value%" AND table1.table2_id = table2.id);
> This query works perfectly, can't fault it.
> But when I do this, SQLite locks out (Command line interface, and PHP5)
> Query 2:
> SELECT * FROM table1, table2
> WHERE ((table1.value LIKE "%value%" AND table1.table2_id = table2.id);
> OR (table1.value LIKE "%different_value%" AND table1.table2_id =
> table2.id));

I think this is a good example of why I use explicit JOINS.

SELECT * FROM table1
INNER JOIN table2 ON
(table2.id = table1.table2_id)
WHERE 
table1.value LIKE '%value%' OR table1.value LIKE '%value%')

Doesn't this do what you want?
-- 
Charlie Clark
Communications Manager
yellowTAB GmbH
Tel: +49-211-600-3657
http://www.yellowtab.com
mailto: [EMAIL PROTECTED]

Re: [sqlite] I still think it's a bug, 0 rows, no fieldnames

[Nuno Lucas]

I'm not understanding what's the problem here. I always get the column names
even on empty result sets (so I can show them in a grid header).

I confess I haven't been using the latest sqlite versions to be sure it
is still returned, but isn't this what you want?

My guess is that maybe you are using the callback API, that need
"PRAGMA empty_result_callbacks = 1" to return the columns on empty result
sets, but I haven't used that API for a long time.

Regards,
~Nuno Lucas


[25-07-2005 9:08, Edwin Knoppert escreveu]

I might be mistaken but you should never expect row-0 for data.
What i meant is that the fieldnames are present when there is data.
And not when there is no data.
In either case, row-0 should never be threated as data row.
So one can not expect data in there.

The point is when there is data, SQLite returns the fieldnames from a 
(joined) query.

For what reason it should not return the names when there was no data?
(And not having an error of course)

I'm pretty certain this is only a technical matter, not a logical one.

schema table?
I'm not familiar with that, i don't think it is realted to fields from 
join-ed queries.


This question has not much to do with my earlier post, i know how to 
obtain the fields properly.

Re: [sqlite] Query locking up SQLite

[David Fowler]

this statement has an extra ; which may be the error. Another thought, when 
quoting string literals, it is better to use single quotes('), since double 
quotes(") means identifier --column name-- first, string literal second.


John
==
Thanks for the very fast reply John. The extra (;) was a copying and pasting 
error on my part when writing out the email and unfortunatly swapping (") 
for (') has had no effect on the result of running the query (SQLite still 
locks up at 100% CPU usage). The problem appears to lie within the use of 
the OR operator when setting conditions accross two tables. So its either 
something to do with the JOIN or OR.

Thanks again for the ideas.

Re: [sqlite] Query locking up SQLite

[John LeSueur]

David Fowler wrote:


SQLite Version 3.2.2.
Is this a bug, or is my SQL that bad?
Query 1:
SELECT * FROM table1, table2
WHERE (table1.value LIKE "%value%" AND table1.table2_id = table2.id);
This query works perfectly, can't fault it.
But when I do this, SQLite locks out (Command line interface, and PHP5)
Query 2:
SELECT * FROM table1, table2
WHERE ((table1.value LIKE "%value%" AND table1.table2_id = table2.id);


this statement has an extra ; which may be the error. Another thought, 
when quoting string literals, it is better to use single quotes('), 
since double quotes(") means identifier --column name-- first, string 
literal second.


OR (table1.value LIKE "%different_value%" AND table1.table2_id = 
table2.id));
This query (and even more complex versions of it) works in MySQL 
(Haven't tried another DB yet) and I'm trying to migrate to SQLite, 
this is really holding me back.


Any ideas, I'm totaly stuck, currently trying to see if I can get the 
same results using another piece of SQL that SQLite will like, 
probably going to end up with a compound statement (UNION most likely 
I think).




John

[sqlite] Query locking up SQLite

[David Fowler]

SQLite Version 3.2.2.
Is this a bug, or is my SQL that bad?
Query 1:
SELECT * FROM table1, table2
WHERE (table1.value LIKE "%value%" AND table1.table2_id = table2.id);
This query works perfectly, can't fault it.
But when I do this, SQLite locks out (Command line interface, and PHP5)
Query 2:
SELECT * FROM table1, table2
WHERE ((table1.value LIKE "%value%" AND table1.table2_id = table2.id);
OR (table1.value LIKE "%different_value%" AND table1.table2_id = 
table2.id));
This query (and even more complex versions of it) works in MySQL (Haven't 
tried another DB yet) and I'm trying to migrate to SQLite, this is really 
holding me back.


Any ideas, I'm totaly stuck, currently trying to see if I can get the same 
results using another piece of SQL that SQLite will like, probably going to 
end up with a compound statement (UNION most likely I think).

Re: [sqlite] I still think it's a bug, 0 rows, no fieldnames

[Edwin Knoppert]

I might be mistaken but you should never expect row-0 for data.
What i meant is that the fieldnames are present when there is data.
And not when there is no data.
In either case, row-0 should never be threated as data row.
So one can not expect data in there.

The point is when there is data, SQLite returns the fieldnames from a 
(joined) query.

For what reason it should not return the names when there was no data?
(And not having an error of course)

I'm pretty certain this is only a technical matter, not a logical one.

schema table?
I'm not familiar with that, i don't think it is realted to fields from 
join-ed queries.


This question has not much to do with my earlier post, i know how to obtain 
the fields properly.





- Original Message - 
From: "Charlie Clark" <[EMAIL PROTECTED]>

To: 
Sent: Sunday, July 24, 2005 4:26 PM
Subject: Re: [sqlite] I still think it's a bug, 0 rows, no fieldnames




On 2005-07-24 at 16:11:26 [+0200], Edwin Knoppert <[EMAIL PROTECTED]>
wrote:

Why defending it?
It's prob. a simple issue.
Besides, why i want the names, why should a system need to parse a query?
You approach this way to 'static' imo (what you enter is your result).
You can easily say to also remove the fieldnames when we *have* data.


An 0-row containing the relational variables is a *special* case and
non-relational and causes problems for results which do not expect the
names of the values to be included in the result set.

.schema(table) gives you all you need.

Charlie

Re: [sqlite] Multi-threading.

[Ben Clewett]

Dear Mrs Brisby,

Thanks for your passionate replies to my original posting.  You have 
much information here.  It's obvious I don't know everything about 
threading.


I like what you say about computer science being a Science.  This is 
exactly my point.  A science is a collection of theories which hold true 
within a domain until otherwise dissproven.  For instance Isac Newtons's 
law of gravety and Einstain's law.  Both are true within a fixed domain. 
 Both are used today.  Neither are truelly corrrect.  There will be 
another more complex theroy in time.


This is the same with Threading.  There may be places where this is 
useful.  There may be places where it should not be used.  Both are 
theories within computer science which hold true in a limited domain. 
So when I said this was my opinion, I should have stated this is my 
theory within a fixed domain.  However when you stated that I was wrong, 
I don't think this is compatible of your statement that Computer Science 
is a Science.  Nothing in any science is either right or wrong.  That's 
a Religion.


I don't want to spend ages on the argument, I can see there are 
passionate views.  I only want to explore the idea that threading is a 
viable strategy for a modern high level language and can produce stable 
applications with low development time.


For instance a call to a TCP blocking Wait.  It's perfectly possible for 
one thread to cycle round handing GUI events, then checking on the port 
for new packets.


But an alternate method is to partition the code space into two 
autonomous threads using their own variable set.  Two simple sets of 
code which are not coupled, and remain highly cohesive and encapsulated. 
 Communicating through a thread-safe object.  Such code is fast to 
write, easy to read, robust and does the job.  I can complete this in c# 
in ten minutes, and it will not break.  With large amounts of time, 
better methods may be available.  But this theory is viable within its 
domain.


> I wasn't about to consider Windows a modern GUI system :)

Are you saying it's not modern, or not a GUI?  It may not be prefect and 
it is very complex.  But it's not about to go away, and it's installed 
on modern computers.  More importantly, my views on threads remain.  If 
you give the GUI it's own thread, you have implemented a simple solution 
to ensure that the correct cursor and mouse events are honoured.  Then a 
worker thread is free to do what ever it likes without being coupled to 
the GUI.  Simple robust coding using thread-safe objects.


I am also interested in peoples views that threading is slower.  Since 
all processors are likely to be hyperthreaded, multicore, or both.  I 
can see no place where this will be true in the future.


> Java uses references, not pointers.

Is there any important difference?  My point was not about language.  It 
was a question to Dr Hipp about what he feels is missing from the Java 
pointer (reference) model.  Perhaps I should have explained better.


Yes, I understand that 'c' can make use of goto, and that goto is fast. 
 There are also very very bad places to use goto.  Exceptions, breaks 
and continue statements are linear code, easy to follow and more robust 
to code changes.  Goto is a legacy of assembler programming.  I don't 
think it's time to teach new coders about goto.  Which is my theory 
within a fixed domain.


Regards,

Ben.


Mrs. Brisby wrote:

On Wed, 2005-07-20 at 17:26 +0100, Ben Clewett wrote:


Dr Hipp,

I am just playing devils advocate here because I have completed much
Java programming in a multi-threaded application. :)

I understand the problems of multi-threading.  I am reminded that it
took nearly 20 years of development to get multi-processor support in a
modern OS stable.  Much success for this can be attributed to Semaphore
Flags.  With CPU hardware support to ensure that the semaphore it's self
cannot be controlled by more than one process.



ITC in 1970 supported multiple threads trivially.



Multi-thread applications suffer the same problems.  Without semaphore
flags or 20 years of development.  A novice programmer can happily
create a second thread and quickly create terribly applications.







However the need for multi-threads is compelling.  Especially in a GUI
environment.  For instance a Mail reader.  Where one thread is needed to
ensure the GUI is drawn correctly and respond to GUI events.  Another to
download and dispatch mail.  (My Thunderbird has 10 threads.  This may
be a bit of overkill :)



No. Threads are not a need. They allow you to use blocking system calls
in parallel without extra page table loads.

History has demonstrated that programmers building multithreaded
applications tend to produce buggier code, and code that touches more
pages than a non-threaded version. As a result, the non-threaded version
is easier to write, safer, and runs faster.




As another user also mentioned, a Windows system works better with few