Quick note to self and others:
IN() and EXISTS() in all RDB's I know of are the uncle noone mentions; that
is to say, they have different compare semantics than JOIN so the naive
strategy is to evaluate them as dependent subqueries, not correlated ones,
which would be consistent with the behavior
Thanks Clemens,
I triple-checked it and it is indeed generating different execution plans,
with the queries being absolutely equivalent. I will try to produce a
minimal test case (right now the query where this occurs is a 100 lines long
monster).
However, I am a bit confused by the examples
Rowan Worth-2 wrote
> I'm not sure what you're worried about? Dropping and recreating identical
> indices within a transaction won't cause a visible structure change to
> concurrent readers -- that's the point of a transaction.
I honestly don't see how in any DB system the client process would
Keith Medcalf wrote
> If you "moved" that memory (or some of it) from Level 2 to Level 1 you
> would increase performance tremendously.
>
> pragma cache_size=262144; -- 1 GB page cache
Thanks, I will try that!
--
Sent from: http://sqlite.1065341.n5.nabble.com/
Rowan Worth-2 wrote
> The entire point of SQL transactions is to implement this magic!
I beg to differ; you proposed:
> (1) BEGIN TRANSACTION
> > (2) DROP all indexes from the table
This is by no means valid SQL semantics; in all RDBBMS I've worked with,
structure changes' relation to a
Hick Gunter wrote
> SQLite does this too
Thanks!
--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Richard Hipp-3 wrote
> DROP, CREATE, and ALTER are transactional in SQLite, just like DELETE,
> INSERT, and UPDATE. This has always been the case, and always shall
> be.
Thanks! But still, isn't this incompatible with any lower-than-DB-level
transaction locking mechanism? I.E. should you ever
Simon Slavin-3 wrote
> DELETE FROM MyTable
We have tried it and DELETE FROM table (without any WHERE) behaves exactly
the same! I reiterate there are no FKs or triggers defined. So this is no
fix...
--
Sent from: http://sqlite.1065341.n5.nabble.com/
Hick Gunter wrote
> If you are running an SQLite version 3.11.0 or newer, only pages that are
> changed for the first time in the transaction are copied to the WAL file,
> thus extending it.
>
> While progressing the transaction, the rate of "newly changed" to "changed
> again" pages will shift
Simon Slavin-3 wrote
> Once you’ve bust the cache things slow down.
I do realize that. However, not illinearly. Once I bust the cache,
throughput should drop X times and stay there. Here, the speed decreases
with the progress. Let me put it this way: say the cache size was 0. I would
expect the
Hi sub sk79,
I have so far from this thread the following suggestions:
1) Copy table -> TRUNCATE -> copy back; this doesn't work, what was supposed
to be TRUNCATE semantics (DELETE FROM without WHERE) has the same
performance as with WHERE.
2) Structure alterations; either drop table, or drop
By "one time only", I mean in my understanding, the way most DBs do on a
DELETE is this: cache the ROWIDs while deleting data rows from the main and
from the indexes, then when all ROWIDS are explored, sort the ROWID stream,
and prune the trees from a sorted stream. This is both highly efficient
For the table swap operation, that is out of the question, the DB is running
on a live system that has multiple clients. Foreign keys or not, any
structure change crashes all clients (DB structure has changed). Too high a
price to pay for a delete thay may happen routinely.
--
Sent from:
Thanks, it's something worth trying.
However, no, that can't be the only reason. Populating scriptically with the
same data takes less than 10 minutes, including side processing... So the
disproportion between insert and delete performance is huge.
--
Sent from:
Ok, so to address the size of the problem properly:
We have ~32M records out of which we are deleting about ~24M.
The raw size of the DB is about 20G out of which 15G goes away. Under these
circumstances any page cache becomes irrelevant.
The delete operation, which is a single delete run from
INSERTs are taking constant time as they should, the DELETE is quite
obviously working is a blatantly non-linear progress.
--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
SQLite 3.19.3
CentOS 7 (64 bit)
RAM: 6G total, 4.4G buff/cache, 0.6G unused
Sqlite memory: 133M RES, 0 SWAP, 0 DIRTY - don't know if it's relevant
anymore, the query seems to have entered into another execution phase, it
looks like now it's flushing the WAL.
No other PRAGMA
No FKs, no triggers.
Keith Medcalf wrote
> I had a similar problem with a multi-terabyte database once upon a time.
Na, they are not a single-time use rows, otherwise I'd have used a FIFO :)
Every now and then, a large portion of the table becomes obsolete by
external factors.
--
Sent from:
Richard Hipp-3 wrote
> Can you try this:
Thanks Richard, as mentioned earlier, any structure change is unacceptable
due to concurrent reader clients.
--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
Short sum-up:
- The -OR- alternative I provided is not semantically equivalent, I will
work on one that is :)
- The other one, without the -OR- (second post) still stands.
--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing
Richard,
Thanks for acknowledging this, you are absolutely right, that's why I stated
that no DB does perfect optimisations and that computing the alternative
-OR- based closures are probably much harder to tackle. Also E. Pasma
pointed out the -OR- queries as I wrote them are not really
Algebrically, having a non-null imperative lookup condition in the WHERE
clause means you have a stronger predicate on the same subject (ALL MUST fit
vs. ANY that fit).
--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
I think they are equivalent, if you look closer.
SELECT FROM a LEFT JOIN b ON a.x=b.x WHERE b.y=5 -is- equivalent to
SELECT FROM a JOIN b ON a.x=b.x AND b.y=5
SELECT FROM a JOIN b WHERE a.x=b.x AND b.y=5
SELECT FROM a LEFT JOIN b ON a.x=b.x AND b.y=5 WHERE b.y IS NOT NULL
All the above are
Hi all,
I've ran into an optimisation problem with a double-left join that works as
an "either" clause.
The query is as follows:
SELECT *
FROM
a
LEFT JOIN
b ON
LEFT JOIN
c ON
WHERE
b.someId IN (1,2,3) OR
c.someId IN (4,5)
This results in a bloated execution plan:
SEARCH a
SEARCH
Probably related:
Compound join with a left outer join generates different execution plans:
LEFT JOIN (
b
JOIN c ON
)
WHERE
b.something = 5
vs.
LEFT JOIN (
b
JOIN c ON AND b.something = 5
)
WHERE
b.something IS NOT NULL
--
Sent from: http://sqlite.1065341.n5.nabble.com/
Thank you for your answer, Keith. I had my problem "fixed" before I wrote the
first mail. Also with every problem I also provided the fix that worked, for
anyone that might run into the same problem.
However, it's difficult to not get a little frustrated with your answer.
At
To reiterate, Keith: to get the query to execute properly, I didn't change
the LEFT JOIN to an INNER JOIN!
Nope,
I rewrote
SELECT
FROM
a
LEFT JOIN b ON
WHERE b.c=5
to
SELECT
FROM
a
LEFT JOIN b ON AND b.c=5
WHERE b.c IS NOT NULL
So I just added a redundant predicate and it runs
Keith Medcalf wrote
> The primary difference is likely the test on b.c.
Keith Medcalf wrote
> I think the query is ill-conceived when written as an outer join.
If anything, to reflect intentionality the query should have been written as
such:
SELECT FROM (
SELECT FROM a LEFT JOIN b
) WHERE
Keith Medcalf wrote
> but cannot be re-ordered for OUTER JOINS.
Actually, I think order requirements (or rather, row grouping requirements,
as far as I can paint it the requirement is just that all outer joined rows
come in a bunch for each main join tuple) would not be violated if the join
is
Keith Medcalf wrote
> The primary difference is likely the test on b.c.
/There was some text here that seems to have been lost and my browser opened
a "super resume templates" page... maybe I've been already spectre-hacked?
Anyway, I repost:/
The primary difference from what I see is that it
Clemens Ladisch wrote
> For count(*), the database does not need the actual table rows.
I think this is not true, he has a point here: SELECT COUNT(*) WHERE
=? needs to examine every index key prefix (excluding at least
ROWID) that matches. This may mean reading in the whole index.
I think
Richard Hipp-3 wrote
> all the parent b-tree pages must be updated
Yup, no question about it, at best it could be an opt-in. But as it is a
design decision, I checked to make sure count() really is O(n) as Jonathan's
question implied.
--
Sent from: http://sqlite.1065341.n5.nabble.com/
What I do notice reading https://www.sqlite.org/fileformat.html (if I get it
right) is that index lines are in the form (for an index on a,b,c ie):
Whereas they could be represented as:
[ , , ](3)
whith [pk_list] being a subtree; reverse lookup from table record to index
record
... and the downside that it's just linear overhead for i.e. an unique index,
it works best for indexes with low cardinality... win some, lose some :)
--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
Keith Medcalf wrote
> Full Schema Tables:
Thanks for this, I will have grat use for it too, soon! Actually it's so
nice I think it could go into the documentation.
--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
If you want just 1 value (any) from lookup, you can use:
SELECT
post_processing_info,
(
SELECT
is_json
FROM
tables_lookup
WHERE
tables_lookup.content_hash=webpage_contents.content_hash
LIMIT 1
) AS is_json
FROM
Analogous to the percentile solution (it's actually the same thing), you can
use a checkpointing table. This has roughly the complexity of SQRT(n) for
both read and write.
I.E. say you expect to have 1M records and define order based on value then
id.
You then make a checkpoint table
The only way to efficiently do this would be to have counting (range) index
b-trees. Since you don't, you're stuck with a O(n) implementation, either on
reading or writing. So your solution is as good as it gets, save maybe some
implementation particularities.
However, you may consider a shift in
Another reason to do at least the LEFT JOIN + WHERE -> INNER JOIN
optimisation:
If a view is based on a LEFT JOIN, running a WHERE query on it will exhibit
the same poor behavior and here there will be no way to rewrite the query.
--
Sent from: http://sqlite.1065341.n5.nabble.com/
Could you describe your intent in more detail?
SELECT * FROM ... GROUP BY is unstable at best, so I cannot really grasp
your intention. Which lookup record's
If I guess right, you might want something in the lines of:
SELECT
w.post_processing_info,
l.is_json
FROM
I think storing index prefix counts would only make sense in a special kind
of 'statistical' index, where you would store count(x IS NOT NULL), sum(x),
sum(x^2) so that usual statistical functions can be computed optimally.
For a table count, I think it would make sense.
--
Sent from:
James,
You don't need to go all the length to creating a sqlite proxy RPC as has
been suggested.
Your service could implement one thing only - a lock to the database file
(external to the SQLite locking mechanism, of course). So it only needs to
serialize concurrency; the client does the RPC (via
If a different perspective may be helpful to you:
If moving overhead to writes is an option (ie you dont have many or time
critical writes), then the tree descendants problem can be sped up to
stellar speeds by using a path column.
IE.
add a column "path" in the nodes table that would contain
Yes, Lifepillar's way is the more orthodox approach, however I always
preferred the path-based one because:
1) One seldom runs queries only based on the descendants map; there usually
is an "AND -some other conditions-" involved; thus the ability to have one
covering index of the condition comes
Thank you all for the replies,
I will hack this problem one way or another after the hoildays and let you
know how it went.
In the mean time, I wish you all happy peaceful holidays, and a great New
Year!
Dinu
--
Sent from: http://sqlite.1065341.n5.nabble.com
Forgot to add DDL for the outer table:
CREATE TABLE materializedInstances (
provider TEXT(20),
instanceId TEXT(20),
packageId INTEGER,
date TEXT(20),
duration INTEGET,
transportType INTEGER,
CONSTRAINT MATERIALIZEDINSTANCES_PK PRIMARY KEY
t.y=j.y AND
t.cond=2
)
FROM
(SELECT DISTINCT t.y) j
Although much uglier, gets there in index search time.
3) Yeap. And as I said, the predicted result sizes by ANALYZE are pretty
accurate, the data is pretty linearly distributed. Th
Hi guys,
Sorry to be a pain, but does anyone have an idea about this issue? I
have to recommend switching DBs this week based on this, and I am
reluctant to do so, but we haven't been able to figure a fix either.
Thanks,
Dinu
Hi Peter,
From https://www.sqlite.org/datatype3.html:
"When text data is inserted into a NUMERIC column, the storage class of
the text is converted to INTEGER or REAL (in order of preference) if
such conversion is lossless and reversible"; basically any fraction you
insert into a NUMERIC
Hi Richard,
Like said, we've already tried:
CREATE TEMP TABLE x AS (...query without outer sort...); CREATE xind ON
x (price); SELECT * FROM x ORDER BY 2
but it also takes 30 seconds; for some really esoteric reason, CREATE
TABLE x AS ... takes all of those 30s, even though the execution plan
You should be also aware of a more common pitfall: unclosed result sets.
Any lock is held until you read PAST the last row or you call stmt_finalize
(or the equivalent abstraction in your DBAL). Always close select
statements.
On Mar 12, 2015 11:40 AM, "R.Smith" wrote:
>
>
> On 2015-03-12 04:38
There is also a more obscure side-effect exhibited by sqlite only: if
you issue consecutive unfinalized select statements, it never releases
the write lock (although they should be atomic), but it does trip the
deadlock mechanism; any writer in the wait will receive SQLITE_BUSY at
the very
when you have a leaked lock.
On 13.03.2015 02:17, Barry wrote:
> On 13 March 2015 at 01:21, Dinu Marina wrote:
>
>> You should be also aware of a more common pitfall: unclosed result sets.
>> Any lock is held until you read PAST the last row or you call stmt_finalize
>
7am, Barry wrote:
>
>> On 13 March 2015 at 01:21, Dinu Marina wrote:
>>
>>> You should be also aware of a more common pitfall: unclosed result sets.
>>> Any lock is held until you read PAST the last row or you call stmt_finalize
>>> (or the equivalent a
e-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-
>> bounces at mailinglists.sqlite.org] On Behalf Of Dinu Marina
>> Sent: donderdag 12 maart 2015 11:34
>> To: sqlite-users at mailinglists.sqlite.org
>> Subject: Re: [sqlite] Weird (slow) TEMP B-TREE ORDER BY
&
Update: we are also using WAL; I have noticed during a huge delete, the
WAL also grows huge. Could this also be a reason for slowness, that
SQLite duplicates every block that hosts a delete? Is there any way to
work around this?
On 17.12.2017 22:53, Dinu Marina wrote:
Hi all,
It became
future?
Thanks,
Dinu
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
have to be unique! (hope you
understand what I'm saying)
Is it possible to fix this "bug" or is there a workaround? (a VIEW does not
help either)
Thanks for any help!
Dinu
==
DAISY Archivierungssysteme GmbH
Preysingstr. 2
68526 Ladenburg
www.
"Igor Tandetnik" <itandet...@mvps.org> schrieb im Newsbeitrag
news:h6bg82$td...@ger.gmane.org...
> Dinu Scheppelmann (DAISY) wrote:
> > Unfortunately when I get the column names by function
> > sqlite3_column_origin_name(), the result columns have the names
>
59 matches
Mail list logo
