Hi Clemens,
It works perfect and fast with a covering index!! (I didn't know this
concept but after you mentioned it, read about it here:
http://www.sqlite.org/queryplanner.html#covidx Perhaps also interesting
for others to read about)
Thank you very much!
Gunnar
On 09/29/2015 09:33 AM,
No I didn't. I will try it.
And thank you very much for your help!!
Gunnar Harms
T +31 (0)20 53 53 487
F +31 (0)20 42 08 852
I www.hiqinvest.nl
HiQ Invest
Rembrandt Tower ? 9th floor
Amstelplein 1
1096 HA Amsterdam
On 09/29/2015 09:33 AM, Clemens Ladisch wrote:
> gunnar wrote:
>> Perhaps
Hi,
I thought "distinct(column)" is somewhat same as "group by column". I
devised the query in steps and forgot to take away the distinct part.
thanks, Gunnar
On 09/29/2015 09:37 AM, Clemens Ladisch wrote:
> gunnar wrote:
>> select distinct(server_order_id), count(*) from ... group by
gunnar wrote:
> select distinct(server_order_id), count(*) from ... group by server_order_id
> ...
Please note that DISTINCT does not work this way; it always applies to
all expressions in the SELECT clause, and "(server_order_id)" is just
the same as "server_order_id". The query actually works
gunnar wrote:
> Perhaps someone knows a way how I can speed up the original query.
Did you try a covering index (add cb_seq_num to ordercallback_index3)?
Regards,
Clemens
Hi Clemens et all,
I found no difference when executing the query while inserting and not
inserting records at the same time. For one of the identical databases
the query still doesn't return.
But I think the reason is in the correlated subquery together with how
the database is filled
gunnar wrote:
> What do you exactly mean with "But in any case, as others have already
> said, it is not possible for a write transaction to lock out a read
> transaction _in the middle_."? I do see that records are being inserted
> while I made those stack traces.
The inserted records are
Hi Clemens,
We checked and there are no faulty sectors.
I do have some information on the disks, both are SSD's. Supposed that
it is a disk problem, then the problematic and non problematic ones are
as below (not sure if this then still has to be in the sqlite mailing
list).
I'm also going to
typo: should have been "around 700MB and 500MB"
On 09/25/2015 03:58 PM, gunnar wrote:
> Hi Clemens,
>
> Here are some of the settings and the integrity check that we always
> prints at start up of our process:
>
> [query:PRAGMA synchronous=OFF][changes:0][total changes:0]
> [query:PRAGMA
Hi Clemens,
Here are some of the settings and the integrity check that we always
prints at start up of our process:
[query:PRAGMA synchronous=OFF][changes:0][total changes:0]
[query:PRAGMA foreign_keys=ON][changes:0][total changes:0]
[query:PRAGMA cache_size=1][changes:0][total changes:0]
gunnar wrote:
> (select uuid from session where date = (select max(date) from session))
This can be optimized to
(select uuid from session order by date desc limit 1)
but the speed of this subquery does not matter.
> (SELECT max(cb_seq_num) FROM ordercallback WHERE
>
Hi Clemens,
See below..
sqlite> EXPLAIN QUERY PLAN SELECT count(*) FROM ordercallback cb WHERE
sessionuuid=(select uuid from session where date = (select max(date)
from session)) AND endstate=0 AND working=1 AND cb_seq_num = (SELECT
max(cb_seq_num) FROM ordercallback WHERE
gunnar wrote:
> the sqlite client is stuck with the following stack traces
According to these stack traces, the client is not stuck but is
busy searching and reading data from the database.
What is the EXPLAIN QUERY PLAN output for this query?
> Disk is not busy
And the CPU?
Regards,
Clemens
Hi!
So, we're still havng troubles with one of our sqlite databases, or more
likely one of our disks, but our system administrators cannot find
anything wrong with it.
Perhaps someone reading this list has a clue what might be the case with
this disk, or something else, when the sqlite client
Hi Simon,
If I remember well the other process has no issues inserting new records
while the sqlite shell is stuck. Tomorrow I'll double check that, am in
train to home now
It's not such a big issue anyway since being able to query from the
shell is just a nice to have so I haven't looked at
I hope not :)
It's under continuous load the whole day already (we write log files on
it that are several GB per file per day)
Isn't it strange that no other query that I execute from sqlite shell
has this , just the one error the correlated sub query?
Anyhow, I'll ask our sysadmins for a
OK, so I executed query:
sqlite> SELECT * FROM ordercallback cb WHERE
sessionuuid='42bda93e-a3a3-4fca-a5c8-dc3dcf2a663e' AND endstate=0 AND
working=1 AND cb_seq_num=(SELECT max(cb_seq_num) FROM ordercallback
WHERE server_order_id=cb.server_order_id AND sessionuuid=cb.sessionuuid
AND
Tomorrow I'll also try to make a stack trace of sqlite_shell with pstack
at the moment its stuck.
Gunnar Harms
HiQ Invest
Rembrandt Tower ? 9th floor
Amstelplein 1
1096 HA Amsterdam
On 05/27/2015 05:23 PM, gunnar wrote:
> Hello Simon,
>
> I'm not 100% sure that I understand what you mean.
On 27 May 2015, at 5:17pm, gharms wrote:
> It's under continuous load the whole day already (we write log files on
> it that are several GB per file per day)
Is it possible that the delays in SQLite execution are caused by something
writing a long log entry ?
Simon.
Hello Simon,
I'm not 100% sure that I understand what you mean. I remember I tested
that the sub query is the one that gets 'stuck' but I can't remember how
I found out (I'm already trying to remember how I found it out and let
you know once I recall it)
I also found a very bad workaround:
On 27 May 2015, at 4:42pm, gunnar wrote:
> And it got 'stuck' after displaying a couple hundred records or so. And the
> stack trace is then:
>
> #0 0x0034f020ec50 in __lseek_nocancel () from /lib64/libpthread.so.0
> #1 0x0041a42c in seekAndRead ()
> #2 0x0041a5f9 in
On 27 May 2015, at 11:00am, gunnar wrote:
> SELECT *
> FROM ordercallback cb
> WHERE sessionuuid=@SESSIONUUID
>AND endstate=0
>AND working=1
>AND cb_seq_num=(
>SELECT max(cb_seq_num)
>FROM ordercallback
>WHERE server_order_id=cb.server_order_id
>
s 1 reads
> data and sends it off to process 2 that tries to modify stuff)?
> How are you binding values to the SESSIONUUID variable using the sqlite shell
> alone?
>
> -Urspr?ngliche Nachricht-
> Von: gunnar [mailto:gharms at hiqinvest.nl]
> Gesendet: Mittwoch, 27. Ma
Hi,
Sometimes when I execute (from the sqlite_shell program) a select-query
that contains a correlated subquery that query gets 'stuck'. It outputs
part of the expected results and then doesn't come back to command
prompt and also doesn't output any more records.
I suspect it is because
n: General Discussion of SQLite Database
Betreff: [sqlite] Query containing correlated subquery gets "stuck"
Hi,
Sometimes when I execute (from the sqlite_shell program) a select-query that
contains a correlated subquery that query gets 'stuck'. It outputs part of the
expected results a
On 5/27/15, gunnar wrote:
> Hi,
>
> Sometimes when I execute (from the sqlite_shell program) a select-query
> that contains a correlated subquery that query gets 'stuck'. It outputs
> part of the expected results and then doesn't come back to command
> prompt and also doesn't output any more
26 matches
Mail list logo
