[sqlite] rowid changing....

[mikeegg1]

Happy Saturday.

In my DDL I have “integer primary key not null” on my tables. When I do the 
first insert to these tables the rowid is assigned. The insert statement is a 
“insert or replace into…” so I don’t have to deal with insertion errors. 
However, I expected the rowid to not change when the row has not changed (when 
the replace is part of the clause is used). Is there a way to not have the 
rowid changed when “insert or replace into…” is used?

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

Re: [sqlite] Complicated join

[David Bicking]

EvtNbr cannot be null, and can be 0. (Though I understand if there is any 
Evtnbr > 0, there can't be an EvtNbr 0, the lowest can be one or higher.)


I need to come up with some test data, if only to test the "I'm pretty sure it 
doesn't work" solution I came up with.

One good bit of news is my boss came back saying I can skip the closest date 
bit, either the dates match or they don't, but I don't believe that decision 
will stick, so I am still trying to come up with an answer.
David


PS. I switched this to send out "plain text", but in the past I have been told 
reader end up seeing an unable to read small font on their end.


From: R Smith 
To: sqlite-users@mailinglists.sqlite.org 
Sent: Saturday, September 17, 2016 7:25 AM
Subject: Re: [sqlite] Complicated join



On 2016/09/15 5:53 PM, David Bicking wrote:
> I have two tables:
> CREATE TABLE M ( CombineKeyFields, EvtNbr, TransDate, OtherFields, PRIMARY 
> KEY(CombinedKeyFields, EvtNbr, TransDate));CREATE TABLE E  ( 
> CombineKeyFields, EvtNbr, TransDate, OtherFields, PRIMARY 
> KEY(CombinedKeyFields, EvtNbr, TransDate));
> "CombinedKeyFields" is shorthand for a combination of about a half dozen 
> fields in the primary key."TransDate" is an integer storing a proprietary 
> date sequence number, where an older date is always less than a newer date
> Now, I want to do E LEFT JOIN M
> (1) The CombinedKeyFields must always match in each table(2) Match using the 
> EvtNbr, but if no match, use the lowest M.EvtNbr that matches the 
> CombinedKeyFields
>
> (3) Match using the TransDate but if no exact match, match on the M.TransDate 
> that is less than the E.TransDate but greater than the prior E.TransDate
> For M.TransDate = 94E.TransDate = 96 will match to 94but E.TransDate = 98 
> will have no match because 94 is less than the prior trans at 96..The idea is 
> to find the closest date that matches that couldn't be matched to another 
> record.

Hi David,

I was going to do this for fun, but some anomalies exist in your 
description which needs to be understood first.

1 - Does every entry have an EvtNbr? Or can it be null? What is the 
lowest EvtNbr in every set - 1 or 0 or something else or indeterminate?  
If an EvtNbr cannot be Null, then point (3) above is moot because there 
will be no item with a date that matches CombinedKeyFields that doesn't 
also have a lowest EvtNbr as can be matched by requirement (2). If the 
lowest EvtNbr for any entry is 0 or 1 then you can simply look for that 
EvtNbr if a match is not found for the exact EvtNbr - which would always 
exist unless there are NO matches on CombinedKeyFields in which case 
there should be no matching lines at all as given by (1).

Perhaps if you could (as suggested by another poster) send a full schema 
with some data and an example of the output needed (highlighting all the 
possible check cases as described above).

If clarity on these can be had, the SQL is quite possible and not too 
convoluted even (though the jury is out on efficiency as yet).



> Saying a prayer that the email gods won't wrap all these lines together in to 
> an unreadable mess like the last time I asked for advice here...

Apparently prayer is not an effective technology :)  The e-mail Gods are 
not at fault here, perhaps the "Send as" settings in your mail 
composition client can be explored?  Many formats should accommodate 
correct formatting. I'm guessing your client has a plain-text editor 
that wraps the result into an HTML paragraph (or some other weirdness 
that you can hopefully deduce and fix by checking the settings).

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

[sqlite] sqldiff -- interpreting the data

[Kumar Suraj]

Can anyone please tell me when i use SQLdiff --summary option what does the
following row means

tablename:0 changes,0 inserts , 288 deletes, 0 unchanged..
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Complicated join

[R Smith]

On 2016/09/15 5:53 PM, David Bicking wrote:

I have two tables:
CREATE TABLE M ( CombineKeyFields, EvtNbr, TransDate, OtherFields, PRIMARY 
KEY(CombinedKeyFields, EvtNbr, TransDate));CREATE TABLE E  ( CombineKeyFields, 
EvtNbr, TransDate, OtherFields, PRIMARY KEY(CombinedKeyFields, EvtNbr, 
TransDate));
"CombinedKeyFields" is shorthand for a combination of about a half dozen fields in the 
primary key."TransDate" is an integer storing a proprietary date sequence number, where 
an older date is always less than a newer date
Now, I want to do E LEFT JOIN M
(1) The CombinedKeyFields must always match in each table(2) Match using the 
EvtNbr, but if no match, use the lowest M.EvtNbr that matches the 
CombinedKeyFields

(3) Match using the TransDate but if no exact match, match on the M.TransDate 
that is less than the E.TransDate but greater than the prior E.TransDate
For M.TransDate = 94E.TransDate = 96 will match to 94but E.TransDate = 98 will 
have no match because 94 is less than the prior trans at 96..The idea is to 
find the closest date that matches that couldn't be matched to another record.


Hi David,

I was going to do this for fun, but some anomalies exist in your 
description which needs to be understood first.


1 - Does every entry have an EvtNbr? Or can it be null? What is the 
lowest EvtNbr in every set - 1 or 0 or something else or indeterminate?  
If an EvtNbr cannot be Null, then point (3) above is moot because there 
will be no item with a date that matches CombinedKeyFields that doesn't 
also have a lowest EvtNbr as can be matched by requirement (2). If the 
lowest EvtNbr for any entry is 0 or 1 then you can simply look for that 
EvtNbr if a match is not found for the exact EvtNbr - which would always 
exist unless there are NO matches on CombinedKeyFields in which case 
there should be no matching lines at all as given by (1).


Perhaps if you could (as suggested by another poster) send a full schema 
with some data and an example of the output needed (highlighting all the 
possible check cases as described above).


If clarity on these can be had, the SQL is quite possible and not too 
convoluted even (though the jury is out on efficiency as yet).




Saying a prayer that the email gods won't wrap all these lines together in to 
an unreadable mess like the last time I asked for advice here...


Apparently prayer is not an effective technology :)  The e-mail Gods are 
not at fault here, perhaps the "Send as" settings in your mail 
composition client can be explored?  Many formats should accommodate 
correct formatting. I'm guessing your client has a plain-text editor 
that wraps the result into an HTML paragraph (or some other weirdness 
that you can hopefully deduce and fix by checking the settings).


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

Re: [sqlite] how is "pragma threads = 4" working

[Венцислав Русев]

On 08/15/2016 12:02 AM, Венцислав Русев wrote:
My computer has 4 cores. I have compile sqlite like this "gcc 
-DSQLITE_MAX_WORKER_THREADS=4 -DSQLITE_DEFAULT_WORKER_THREADS=4 
shell.c sqlite3.c -lpthread -ldl -o sqlite3". I made some tests and 
found that "pragma threads = 4" doesn't decrease runtime of the query 
that sorts 1 milion records.


SQLite version 3.8.8
sqlite> pragma threads;
4
sqlite> CREATE TABLE event (
ID INTEGER PRIMARY KEY NOT NULL,
date   INTEGER NOT NULL,
value  INTEGER NOT NULL );
sqlite> EXPLAIN QUERY PLAN SELECT ID FROM event ORDER BY date LIMIT 1;
0|0|0|SCAN TABLE event
0|0|0|USE TEMP B-TREE FOR ORDER BY
sqlite> SELECT ID FROM event ORDER BY date LIMIT 1;
4101021
Run Time: real 2.493 user 2.426000 sys 0.049000
sqlite> pragma threads = 0;
0
sqlite> SELECT ID FROM event ORDER BY date LIMIT 1;
4101021
Run Time: real 2.484 user 2.421000 sys 0.044000

To sum it up I have:
  - multi core cpu;
  - sqlite that is compiled to use "auxiliary threads";
  - a table with 1 million records;
  - a query that scans through the table, sorts all records and 
outputs the first ID.


sqlite doesn't use these "auxiliary threads" that sqlite docs talks 
about and the runtime of that query is the same with or without 
"pragma threads = 4".


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


Some clarifications:
  - OS: Ubuntu 14.04 LTS;
  - "PRAGMA compile_options" gives "SYSTEM_MALLOC  THREADSAFE=1";
  - when sqlite shell is running and no query is running at the moment 
the OS reports that it has only one thread;



On 09/16/2016 11:29 AM, Richard Hipp wrote:


The algorithm used for "ORDER BY ... LIMIT N" uses much less memory 
than a full-up "ORDER BY" because is only keeps track of the top N 
entries seen so far, discarding the rest. But it also only uses a 
single thread. If you want multiple threads to be used, you'll need to 
drop the LIMIT, though I imagine that would defeat your purpose, no?


Richard Hipp has a point. I searched the sources and found that the 
sorting algorithm uses threads only if there isn't a LIMIT clause.
To be exact the sources say "This file contains code for the VdbeSorter 
object, used in concert with a VdbeCursor to sort large numbers of keys 
for CREATE INDEX statements or by SELECT statements with ORDER BY 
clauses that cannot be satisfied using indexes and without LIMIT clauses."


But I'm wondering what if your query is like so "SELECT ID FROM event 
ORDER BY date LIMIT 1 OFFSET 100;"? Isn't it better to sort the 
results using available threads?



On 09/16/2016 12:13 PM, Adam Devita wrote:


Have you proven that the cpu is the bottleneck? Sorting a million rows 
seems like a lot, but even older single core cpus may be capable of 2 
billion ops per second. [I apologize if this has been sorted out 
already I've got about 2 days of history on the thread] regards, 
Adam DeVita


This was just a test to see the performance of "PRAGMA threads". 
Actually I expect to sort 10-30 million rows or more. But after the 
tests I may choose the index.



Just to let you know what other tests I've conducted.

sqlite> .output stdout

sqlite> pragma threads = 4;
sqlite> select ID from event order by date;
Run Time: real 2.880 user 4.469000 sys 0.147000

sqlite> pragma threads = 0;
sqlite> select ID from event order by date;
Run Time: real 3.870 user 3.771000 sys 0.075000

Besides the runtime difference I inspected the process information while 
the query is running. When "pragma threads = 4;" sqlite shell starts new 
threads for sorting and stops them after the query has finished.


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

Re: [sqlite] sqlite3 issue report: sqlite3 hung-up on DELETE

[Takasumi Iwamoto]

Hello Richard Hipp,

Thank you for the fast reply.
We've confirmed that the work-around works well.
We will remove UNIQUE from PRIMARY KEY by default.

Thank you again,

-- 
Takasumi Iwamoto


On Fri, Sep 16, 2016 at 11:17 PM, Richard Hipp  wrote:
> Your best work-around is to fix your schema.  Do *not* say
>
> UNIQUE PRIMARY KEY
>
> That is redundant.  PRIMARY KEYs are always UNIQUE.  Just say PRIMARY
> KEY and omit the UNIQUE.
>
> Of course, SQLite should be able to deal with this redundancy without
> a dramatic slowdown.  That problem will be fixed in the 3.15.0
> release.  We will also add test cases to try to prevent a recurrence
> of this or similar problems.
>
> But in the meantime, just remove the UNIQUE from all your PRIMARY KEYs
> and your performance issues should go away.  For all versions of
> SQLite.
>
> On 9/16/16, Richard Hipp  wrote:
>> On 9/16/16, Takasumi Iwamoto  wrote:
>>> Hello SQLite devs,
>>>
>>> We've found a hung-up issue in the current sqlite3.
>>> Could you please read the below issue report?
>>
>> Thanks for the bug report.  The ticket for this problem is here:
>> https://www.sqlite.org/src/tktview/0eab1ac7591f
>>
>> --
>> D. Richard Hipp
>> d...@sqlite.org
>>
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users