Re: [sqlite] Query locking up SQLite
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
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
Re: [sqlite] Query on multiple tables
Martin Gagnon wrote: Hi all, Using sqlite3 on QNX 6.3.0. I need to do a select query on 3 tables by binding them by their ID's. Something like: Select tbl1.ID, tbl1.fld1, tbl1.fld2 /*(15 fields total, all from tbl1)*/ from tbl1, tbl2, tbl3, where tbl1.ID=4 AND tbl1.ID=tbl2.ParentID AND tbl2.ID=tbl3.ParentID This returns the expected row instantly but the sqlite3 process takes more that 10 seconds to give back a prompt, taking all the CPU time. Is there a way to accomplish this task better? Thank you, Martin Gagnon Martin, Pardon me if I missed something, but if your query only returns fields from table tbl1 (as it says in your comment), why do you need to join with the other tables? Your query doesn't reference fields from the other tables in where clauses that might restrict the rows that are returned, it only gives the join conditions. From what you have said, this should equivalent to your query; Select tbl1.ID, tbl1.fld1, tbl1.fld2 /*(15 fields total, all from tbl1)*/ from tbl1 where tbl1.ID=4 Dennis Cote
RE: [sqlite] Query on multiple tables
Hi Marc-André, That's a much better way of doing this. Thanks! Martin P.S. Tes salutations sont rendues! -Original Message- From: Marc-Andre Gosselin [mailto:[EMAIL PROTECTED] Sent: Thursday, June 16, 2005 19:47 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Query on multiple tables Hi Martin, You should try to use a JOIN instead of the WHERE clause. Like this : SELECT tbl1.ID, tbl1.fld1, tbl1.fld2 ... FROM tbl1 JOIN tbl2 ON tbl2.ParentID = tbl1.ID JOIN tbl3 ON tbl3.ParentID = tbl2.ID WHERE tbl1.ID = 4 Try that and tell me if that work for you. I've got a similar problem and that's how I was able to make it work. Probably it has something to do with the way SQLite parse the request. Regards Marc-Andre Gosselin P.S.: Si tu es du même bureau tu diras salut à Sébastien Rancourt de ma part! Martin Gagnon a écrit : > Hi all, > Using sqlite3 on QNX 6.3.0. > I need to do a select query on 3 tables by binding them by their ID's. > Something like: > Select tbl1.ID, tbl1.fld1, tbl1.fld2 /*(15 fields total, all from > tbl1)*/ from tbl1, tbl2, tbl3, where tbl1.ID=4 AND tbl1.ID=tbl2.ParentID > AND tbl2.ID=tbl3.ParentID > This returns the expected row instantly but the sqlite3 process takes > more that 10 seconds to give back a prompt, taking all the CPU time. > Is there a way to accomplish this task better? > Thank you, > Martin Gagnon > > >
Re: [sqlite] Query on multiple tables
Hi Martin, You should try to use a JOIN instead of the WHERE clause. Like this : SELECT tbl1.ID, tbl1.fld1, tbl1.fld2 ... FROM tbl1 JOIN tbl2 ON tbl2.ParentID = tbl1.ID JOIN tbl3 ON tbl3.ParentID = tbl2.ID WHERE tbl1.ID = 4 Try that and tell me if that work for you. I've got a similar problem and that's how I was able to make it work. Probably it has something to do with the way SQLite parse the request. Regards Marc-Andre Gosselin P.S.: Si tu es du même bureau tu diras salut à Sébastien Rancourt de ma part! Martin Gagnon a écrit : Hi all, Using sqlite3 on QNX 6.3.0. I need to do a select query on 3 tables by binding them by their ID's. Something like: Select tbl1.ID, tbl1.fld1, tbl1.fld2 /*(15 fields total, all from tbl1)*/ from tbl1, tbl2, tbl3, where tbl1.ID=4 AND tbl1.ID=tbl2.ParentID AND tbl2.ID=tbl3.ParentID This returns the expected row instantly but the sqlite3 process takes more that 10 seconds to give back a prompt, taking all the CPU time. Is there a way to accomplish this task better? Thank you, Martin Gagnon
Re: [sqlite] Query select....
And now tell us, which part of your problem relates to SQLite? :)
--
Martins
From: SKORPIO-INFO [EMAIL PROTECTED]
Date: Fri, 1 Apr 2005 15:17:56 +0300
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Query select
> ok :)
>
> I have resolved this problem:
>
> SLOT_SEARCH = BSS.execute("SELECT * FROM slot WHERE
> data_slot='01-04-2005' AND ((num_slot > "+str(contatore[1])+") AND
> (num_slot < "+str(contatore[0])+"))")
>
> Thanks
>
> SKORPIO-INFO ha scritto:
>
> > Hi to all,
> > I have the necessity to carry out a query that it must find an
> > occurrence to me in which the date is x and that a data field is
> > comprised between y and z
> >
> > This is the example:
> >
> > contatore = [20,33]
> >
> > SLOT_SEARCH = BSS.execute("SELECT * FROM slot WHERE
> > data_slot='01-04-2005' AND ((num_slot > contatore[1]) AND (num_slot <
> > contatore[0]))")
> >
> >
> > thi is the table:
> >
> > CREATE TABLE slot( id_slot INTEGER(11) PRIMARY KEY, univocal_number
> > VARCHAR(12), data_slot VARCHAR(10), num_slot VARCHAR(5) )
> >
> > the error reporting is:
> >
> > DatabaseError: near "[1]": syntax error
> >
> >
> > Help Me :)
> >
> >
> >
>
Re: [sqlite] Query select....
ok :)
I have resolved this problem:
SLOT_SEARCH = BSS.execute("SELECT * FROM slot WHERE
data_slot='01-04-2005' AND ((num_slot > "+str(contatore[1])+") AND
(num_slot < "+str(contatore[0])+"))")
Thanks
SKORPIO-INFO ha scritto:
Hi to all,
I have the necessity to carry out a query that it must find an
occurrence to me in which the date is x and that a data field is
comprised between y and z
This is the example:
contatore = [20,33]
SLOT_SEARCH = BSS.execute("SELECT * FROM slot WHERE
data_slot='01-04-2005' AND ((num_slot > contatore[1]) AND (num_slot <
contatore[0]))")
thi is the table:
CREATE TABLE slot( id_slot INTEGER(11) PRIMARY KEY, univocal_number
VARCHAR(12), data_slot VARCHAR(10), num_slot VARCHAR(5) )
the error reporting is:
DatabaseError: near "[1]": syntax error
Help Me :)
Re: [sqlite] Query on sorting (grouping, actually)
This question comes up every couple of months or so. It's a basic misunderstanding of how grouping works. The answer in your case is select Visitor_ID, Action, Message, Timestamp from av2, (select Visitor_ID vid, max(Timestamp) mts from av2 where Action='I' or Action='O' group by vid) where Visitor_ID = vid and Timestamp = mts; If you want to see why this works, you can look at http://www.mail-archive.com/[EMAIL PROTECTED]/msg02154.html and http://www.mail-archive.com/[EMAIL PROTECTED]/msg01118.html and follow the threads backward and forward. Regards - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] Query optimization help
The advice from Dr. Richard Hipp did the trick. I added a multi column index and now the query takes less than a tenth of a seconds. Thanks a lot for the help you guys! I was going to send this acknowledgement by replying to the message by Dr. Hipp, but for some reason I didn't receive it, so I'll just send it like this instead. -- Rickard Andersson arpen_at_home_dot_se - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] Query optimization help
Rickard Andersson wrote: I'm having some performance problems with queries looking like the following: SELECT DISTINCT p.poster_id AS has_posted, t.id, t.subject, t.poster, t.posted, t.last_post, t.last_post_id, t.last_poster, t.num_views, t.num_replies, t.closed, t.sticky, t.moved_to FROM topics AS t LEFT JOIN posts AS p ON t.id=p.topic_id AND p.poster_id=2 WHERE t.id IN(3082, 2467, 3076, 3055, 3016, 3019, 2509, 2788, 2804, 2396, 2761, 2745) The above query takes a full second to complete on my P3-450. The database contains approx. 1200 topics and 8000 posts. All relevant columns have indices (t.id, p.topic_id, p.poster_id). Do you believe there is a way to speed it up? If you have separate indices on p.topic_id and p.poster_id, SQLite will only use one or the other, and it has a 50% chance of choosing the wrong one. I suggest you try it with a single index that includes both p.topic_id and p.poster_id. Like this: CREATE TABLE p_idx ON p(topic_id, poster_id); -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565 - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] Query optimization help
Hi Richard, try this SELECT DISTINCT p.poster_id AS has_posted, t.id, t.subject, t.poster, t.posted, t.last_post, t.last_post_id, t.last_poster, t.num_views, t.num_replies, t.closed, t.sticky, t.moved_to FROM topics AS t , posts AS p WHERE t.id IN(3082, 2467, 3076, 3055, 3016, 3019, 2509, 2788, 2804, 2396,2761, 2745) and t.id=p.topic_id AND p.poster_id=2 There a post from Dr R about how SQLite works out joins. The above should be the final result it I read it right. You might want to also try using group by instead of DISTINCT In other DB it is faster. http://www.sqlite.org/cvstrac/wiki?p=PerformanceTuning regards Greg - Original Message - From: Rickard Andersson To: [EMAIL PROTECTED] Sent: Monday, February 02, 2004 2:00 PM Subject: [sqlite] Query optimization help I'm having some performance problems with queries looking like the following: SELECT DISTINCT p.poster_id AS has_posted, t.id, t.subject, t.poster, t.posted, t.last_post, t.last_post_id, t.last_poster, t.num_views, t.num_replies, t.closed, t.sticky, t.moved_to FROM topics AS t LEFT JOIN posts AS p ON t.id=p.topic_id AND p.poster_id=2 WHERE t.id IN(3082, 2467, 3076, 3055, 3016, 3019, 2509, 2788, 2804, 2396, 2761, 2745) The above query takes a full second to complete on my P3-450. The database contains approx. 1200 topics and 8000 posts. All relevant columns have indices (t.id, p.topic_id, p.poster_id). Do you believe there is a way to speed it up? -- Rickard Andersson arpen_at_home_dot_se - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] Query problem
D. Richard Hipp wrote:
Simon Berthiaume wrote:
For those of you that tends to write complex queries, I noted that
SQLite doesn't like when a table name follows a opening parenthesis in
the FROM clause.
The simplest fix for this would be to insert "SELECT * FROM" right after
the "(" in the FROM list. So, if the original query was like this:
SELECT * FROM tab1 LEFT JOIN (tab2 LEFT JOIN tab3);
The query could be rewritten as follows:
SELECT * FROM tab1 LEFT JOIN (SELECT * FROM tab2 LEFT JOIN tab3);
The 2nd form would be correctly understood by SQLite. It wouldn't be
very difficult to get the SQLite parser to do this automatically, I
expect. Then the first form would work just like the second without
any need for human intervention.
Note a recent similar change to SQLite in check-in [1180]
http://www.sqlite.org/cvstrac/chngview?cn=1180
The IN operator for SQLite used to require a fully-formed SELECT statement
on the right. Like this:
column IN (SELECT * FROM table)
But after check-in [1180], you can now use the following shorthand:
column IN table
The change was to automatically insert the "SELECT * FROM" in the
parser. I'm guessing that a changes to allow parentheses in the
FROM clause would be along the same lines. So if anybody is interested
in working on a patch, I suggest you have a look at [1180] first
to see if you can get any ideas from it.
--
D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] Query problem
Simon Berthiaume wrote:
For those of you that tends to write complex queries, I noted that
SQLite doesn't like when a table name follows a opening parenthesis in
the FROM clause.
The simplest fix for this would be to insert "SELECT * FROM" right after
the "(" in the FROM list. So, if the original query was like this:
SELECT * FROM tab1 LEFT JOIN (tab2 LEFT JOIN tab3);
The query could be rewritten as follows:
SELECT * FROM tab1 LEFT JOIN (SELECT * FROM tab2 LEFT JOIN tab3);
The 2nd form would be correctly understood by SQLite. It wouldn't be
very difficult to get the SQLite parser to do this automatically, I
expect. Then the first form would work just like the second without
any need for human intervention.
--
D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] Query problem
For those of you that tends to write complex queries, I noted that SQLite doesn't like when a table name follows a opening parenthesis in the FROM clause. For example, the following works under Access ans Oracle, but not in SQLite: SELECT T0.OBJECTS_ID , T0.OBJECTS_REFNO, T8.OBJECTS_LOCATION_LOCATION FROM ( OBJECTS AS T0 LEFT JOIN ( SELECT OBJECTS_LOCATION_FOREIGN, MAX(OBJECTS_LOCATION_DATE) A LATEST_DATE FROM OBJECTS_LOCATION GROUP BY OBJECTS_LOCATION_FOREIGN ) AS T9 ON T9.OBJECTS_LOCATION_FOREIGN = T0.OBJECTS_ID ) LEFT JOIN OBJECTS_LOCATION AS T8 ON (T8.OBJECTS_LOCATION_FOREIGN = T9.OBJECTS_LOCATION_FOREIGN AN T8.OBJECTS_LOCATION_DATE = T9.LATEST_DATE) WHERE T0.OBJECTS_FLAG = 3 AND T0.OBJECTS_DELETED = 0 ORDER BY 2 ASC; To make it work, I have to write the following instead: SELECT T0.OBJECTS_ID , T0.OBJECTS_REFNO, T8.OBJECTS_LOCATION_LOCATION FROM OBJECTS AS T0 LEFT JOIN ( SELECT OBJECTS_LOCATION_FOREIGN, MAX(OBJECTS_LOCATION_DATE) AS LATEST_DATE FROM OBJECTS_LOCATION GROUP BY OBJECTS_LOCATION_FOREIGN ) AS T9 ON T9.OBJECTS_LOCATION_FOREIGN = T0.OBJECTS_ID LEFT JOIN OBJECTS_LOCATION AS T8 ON (T8.OBJECTS_LOCATION_FOREIGN = T9.OBJECTS_LOCATION_FOREIGN AN T8.OBJECTS_LOCATION_DATE = T9.LATEST_DATE) WHERE T0.OBJECTS_FLAG = 0 AND T0.OBJECTS_DELETED = 0 ORDER BY 2 ASC; I hope I will make someone save the trouble I went through finding that out. Simon Berthiaume On Mon, 2004-01-19 at 16:37, Simon Berthiaume wrote: > Maybe I used a too simplistic example. I have code that generates SQL > queries so users can request reports with various fields, various > criterias and various sorting orders. So technicaly there can be 1,2,3,N > inner queries with various statements in them. Here is an example of a > more complex query: > SELECT t0.OBJECTS_BRIEF, t2.OBJECTS_ALPHANUMERIC_VALUE, > t3.OBJECTS_DATE_VALUE > FROM > ( > ( > ( > SELECT * > FROM OBJECTS > WHERE OBJECTS_COLTYPE='Jewelry' > ) AS t0 > LEFT JOIN > ( > SELECT * > FROM OBJECTS_ALPHANUMERIC > WHERE OBJECTS_ALPHANUMERIC_FIELD_ID='GRADE' > AND (OBJECTS_ALPHANUMERIC_VALUE='Excellent' > OR OBJECTS_ALPHANUMERIC_VALUE IS NULL) > ) AS t1 > ON t0.OBJECTS_ID = t1.OBJECTS_ID > ) > LEFT JOIN > ( > SELECT * > FROM OBJECTS_ALPHANUMERIC > WHERE OBJECTS_ALPHANUMERIC_FIELD_ID='OWNER' > AND NOT OBJECTS_ALPHANUMERIC_VALUE='John Smith' > ) AS t2 > ON t0.OBJECTS_ID = t2.OBJECTS_ID > ) > LEFT JOIN > ( > SELECT * > FROM OBJECTS_DATE > WHERE OBJECTS_DATE_FIELD_ID='EXHIBIT_END_DATE' > ) AS t3 > ON t0.OBJECTS_ID = t3.OBJECTS_ID > ORDER BY 3,2; > > In the previous example, the user would have asked for > 1- the item's description, it's owner it's exhibit end date > 2- Objects that are Jewelry > 3- Objects in excellent condition (grade) or unspecified > 4- Objects not owned by John Smith > 5- Sorted by exhibit end date and owner name > > The "relationship" between OBJECTS and OBJECTS_ALPHANUMERIC is 0,N and > the "relationship" between OBJECTS and OBJECTS_DATE is also 0,N. > > The previous query worked just fine in Oracle and Access, but not in > SQLite. SQLite might simply not be support it, that is what I want to > know. If it's a bug, a feature SQLite will support in the future (how > close in the future) or a feature that will never be supported. > > Thank you all for your time > > > Simon Berthiaume > > > On Mon, 2004-01-19 at 14:24, Kurt Welgehausen wrote: > > > If you look at the SQLite grammar in lang.html, you'll see > > that parentheses are not allowed around a table-list. That's > > why you're getting an error. > > > > If you remove either of the first 2 left parens (and its > > corresponding right paren), the query will work, but the > > outer select and the first subselect serve no purpose. It's > > hard to predict performance just by reading a query, but this > > looks pretty inefficient. How about > > > > select * from > > INSCLAIMS t0 > > left join > > (select * from INSCLAIMS_CONCAT > > where INSCLAIMS_CONCAT_FIELD_ID = 'INSCLAIMS_POLICYNO') t1 > > on t1.INSCLAIMS_ID = t0.INSCLAIMS_ID; > > > > - > > To unsubscribe, e-mail: [EMAIL PROTECTED] > > For additional commands, e-mail: [EMAIL PROTECTED] > >
Re: [sqlite] Query problem
If you look at the SQLite grammar in lang.html, you'll see that parentheses are not allowed around a table-list. That's why you're getting an error. If you remove either of the first 2 left parens (and its corresponding right paren), the query will work, but the outer select and the first subselect serve no purpose. It's hard to predict performance just by reading a query, but this looks pretty inefficient. How about select * from INSCLAIMS t0 left join (select * from INSCLAIMS_CONCAT where INSCLAIMS_CONCAT_FIELD_ID = 'INSCLAIMS_POLICYNO') t1 on t1.INSCLAIMS_ID = t0.INSCLAIMS_ID; - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] Query problem
Actualy none, there was an error in the statement I copied. But the following works on Access (ADO) and Oracle as well, but not on SQLite. Since I don't have access to any other RDBMS, I couldn't test others. SELECT * FROM ( ( SELECT * from INSCLAIMS ) AS T0 LEFT JOIN ( SELECT * FROM INSCLAIMS_CONCAT WHERE ( INSCLAIMS_CONCAT_FIELD_ID = 'INSCLAIMS_POLICYNO' ) ) AS T1 ON T1.INSCLAIMS_ID = T0.INSCLAIMS_ID ) ; Simon Berthiaume On Mon, 2004-01-19 at 12:52, D. Richard Hipp wrote: > Simon Berthiaume wrote: > > SELECT * > > FROM > >( > > ( > > SELECT * from INSCLAIMS > > ) AS T0 > > LEFT JOIN > > ( > > SELECT * FROM INSCLAIMS_CONCAT WHERE > > ( > > INSCLAIMS_CONCAT_FIELD_ID = 'INSCLAIMS_POLICYNO' > > ) > > ) AS T1 > > ON T1.INSCLAIMS_ID = T0.INSCLAIMS_ID > >) AS foo > > ; > > > > On what RDBMSes does the above query actually work? >
Re: [sqlite] Query problem
Simon Berthiaume wrote: SELECT * FROM ( ( SELECT * from INSCLAIMS ) AS T0 LEFT JOIN ( SELECT * FROM INSCLAIMS_CONCAT WHERE ( INSCLAIMS_CONCAT_FIELD_ID = 'INSCLAIMS_POLICYNO' ) ) AS T1 ON T1.INSCLAIMS_ID = T0.INSCLAIMS_ID ) AS foo ; On what RDBMSes does the above query actually work? -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565 - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
