Re: [sqlite] SQL to SQLite

[Keith Medcalf]

On Tuesday, 3 December, 2019 05:39, gideo...@lutzvillevineyards.com wrote:

>My query is :

>UPDATE wbridge_history
>SET yearclass =
>(
>SELECT D.wynklas
>FROM
>(
>SELECT LidNo, PlaasNo, BlokNo, oesjaar, wynklas,
>ROW_NUMBER() OVER (PARTITION BY LidNo, PlaasNo, BlokNo, oesjaar ORDER BY
>COUNT(*) DESC, SUM(ton) DESC) AS row_num FROM wbridge_history GROUP BY
>LidNo, Plaasno, BlokNo, oesjaar, wynklas
>) D
>WHERE D.LidNo = wbridge_history.LidNo
>AND D.PlaasNo = wbridge_history.PlaasNo
>AND D.BlokNo = wbridge_history.BlokNo
>AND D.oesjaar = wbridge_history.oesjaar
>AND D.row_num = 1
>);

This prepares without error on version 3.31.0 so, as Simon says, you probably 
just have a version of SQLite3 that does not do window functions (ie, prior to 
3.25.0) in which ROW_NUMBER() would have been parsed as a function call, OVER 
as a column alias name, and the "(" as a syntax error (only a "," the keyword 
"FROM", or the end of statement ";" could validly appear after an expression in 
the select list).

-- 
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.




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

Re: [sqlite] PRAGMA for .load

[David Raymond]

I _think_ the load_extension() function is what you'll be looking for, though I 
could be wrong.

https://www.sqlite.org/lang_corefunc.html#load_extension


-Original Message-
From: sqlite-users  On Behalf Of 
Jose Isaias Cabrera
Sent: Tuesday, December 3, 2019 3:38 PM
To: sqlite-users@mailinglists.sqlite.org
Subject: [sqlite] PRAGMA for .load


Greetings.  Quick question...

I was looking into the pragmas page, and I don't see one that would do the 
function to load other libraries, such as the .load function of the CLI.  Is 
there one?  Thanks.

josé
___
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] PRAGMA for .load

[Jose Isaias Cabrera]

Greetings.  Quick question...

I was looking into the pragmas page, and I don't see one that would do the 
function to load other libraries, such as the .load function of the CLI.  Is 
there one?  Thanks.

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

Re: [sqlite] [EXTERNAL] Slow joining of tables with indexes

[Jonathan Moules]

Thanks for the suggestion.
Changing the USING to ON makes absolutely no difference. The speed is 
the same and the query plans (both EXPLAIN and EXPLAIN QUERY PLAN) are 
absolutely identical. Same for if I convert it to WHERE:


WHERE joining_table.data_id = data_table.data_id;

On 2019-12-03 14:46, Simon Slavin wrote:

On 3 Dec 2019, at 8:48am, Jonathan Moules  wrote:


   SELECT
   count(1)
   FROM
   data_table
   JOIN joining_table USING (data_id);

   SELECT
   count(1)
   FROM data_table
   JOIN joining_table
   ON joining_table.data_id = data_table.data_id;

Given the rest of the structure you gave, including the indexes, compare the 
speeds of these two.

Simon
___
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

Re: [sqlite] [EXTERNAL] Slow joining of tables with indexes

[Simon Slavin]

On 3 Dec 2019, at 8:48am, Jonathan Moules  wrote:

>   SELECT
>   count(1)
>   FROM
>   data_table
>   JOIN joining_table USING (data_id);

  SELECT
  count(1)
  FROM data_table
  JOIN joining_table
  ON joining_table.data_id = data_table.data_id;

Given the rest of the structure you gave, including the indexes, compare the 
speeds of these two.

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

Re: [sqlite] SQL to SQLite

[Warren Young]

On Dec 3, 2019, at 5:38 AM, gideo...@lutzvillevineyards.com wrote:
> 
> The squigly red line starts at  (PARTITION ..

PARTITION BY is a valid SQLite window function:

https://www.sqlite.org/windowfunctions.html#the_partition_by_clause

but that feature is only about a year old in SQLite:


https://blog.xojo.com/2018/12/18/sqlite-3-25-adds-window-functions-and-improves-alter-table/

Are you using at least SQLite 3.25?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQL to SQLite

[gideon.e]

 

Hi Simon

 

My query is : 

 

 

UPDATE wbridge_history

SET yearclass =

(

SELECT D.wynklas

FROM

(

SELECT LidNo, PlaasNo, BlokNo, oesjaar, wynklas,

ROW_NUMBER() OVER (PARTITION BY LidNo, PlaasNo, BlokNo, oesjaar ORDER BY
COUNT(*) DESC, SUM(ton) DESC) AS row_num FROM wbridge_history GROUP BY
LidNo, Plaasno, BlokNo, oesjaar, wynklas

) D

WHERE D.LidNo = wbridge_history.LidNo

AND D.PlaasNo = wbridge_history.PlaasNo

AND D.BlokNo = wbridge_history.BlokNo

AND D.oesjaar = wbridge_history.oesjaar

AND D.row_num = 1

);

 

 

It works perfectly in SQL, but in SQLite it gives an error message : Error
while executing SQL query on database 'weegbrugGeskiedenis': near "(":
syntax error

The squigly red line starts at  (PARTITION ..

 

 

Regards

 

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

Re: [sqlite] [EXTERNAL] Slow joining of tables with indexes

[Jonathan Moules]

Hi List,

So, I've altered my structure to be INTEGER primary keys, but I'm still 
seeing very slow query times when joining. The original query is faster:


  SELECT
  count(1)
  FROM
  data_table
  JOIN joining_table USING (data_id);

It takes ~2s, but if I then join on to the next table (ignore_me - only 
~200,000 records), it goes up to a whopping 27s - and this is on the SSD!


SELECT

count(1)

FROM

data_table

    JOIN joining_table USING (data_id)

    JOIN ignore_me USING (ignored_id)

;

I can see it's using the indexes, but strangely the index it's using on 
the ignore_me table isn't the PK index but a FK index (INTEGER) to the 
next table in the sequence (not included in this schema):


5    0    0    SCAN TABLE ignore_me USING COVERING INDEX 
ignored__e_id__fk_idx
7    0    0    SEARCH TABLE data_to_ignored USING COVERING INDEX 
joining_table__ignored_data_id__fk_idx (s_id=?)

11    0    0    SEARCH TABLE data USING INTEGER PRIMARY KEY (rowid=?)

Any thoughts? This seems like relational-database bread-and-butter so 
I'm sure I'm doing something wrong to be getting these slow speeds but I 
can't see what.


Thanks,

Jonathan

=

Schema now:

-- 1.7 million items

CREATE TABLE data_table (

data_id INTEGER PRIMARY KEY,

data_1 TEXT,

data_2 TEXT );


-- 1.9 million items

CREATE TABLE joining_table (

data_id INTEGER REFERENCES data_table (data_id),

ignored_id INTEGER REFERENCES ignore_me (ignored_id),

misc_col_1 TEXT,

misc_col_2 TEXT

);


-- ~200,000 items

CREATE TABLE ignore_me (

ignored_id INTEGER PRIMARY KEY,

ignored_col TEXT

);


-- Allow quick joining from data_table to ignore_me

CREATE INDEX IF NOT EXISTS joining_table__data_ignored_id__fk_idx ON 
joining_table (


data_id ASC,

ignored_id ASC

);

-- Allow quick joining from ignore_me to data_table

CREATE INDEX IF NOT EXISTS joining_table__ignored_data_id__fk_idx ON 
joining_table (


ignored_id ASC,

data_id ASC

);


-- Example data:


INSERT INTO data_table (data_id) VALUES (1); INSERT INTO data_table 
(data_id) VALUES (2);



INSERT INTO ignore_me VALUES (1, 'words'); INSERT INTO ignore_me VALUES 
(2, 'more words'); INSERT INTO ignore_me VALUES (3, 'yet more words');



INSERT INTO joining_table (data_id, ignored_id) VALUES (1, 1); INSERT 
INTO joining_table (data_id, ignored_id) VALUES (1, 2); INSERT INTO 
joining_table (data_id, ignored_id) VALUES (2, 3);




SELECT

count(1)

FROM

data_table

    JOIN joining_table USING (data_id)

    JOIN ignore_me USING (ignored_id)

;






On 2019-12-02 13:42, Jonathan Moules wrote:
Thanks for the comments. I've done some testing. Results below for 
those interested.


* Unnecessary manual indexes on the Primary Key - good spot, I'd 
forgotten SQLite does that!


* I was indeed using a Hard Disk but that was intentional - this is 
for customers and I can't know their hardware.


* INTEGERs vs WITHOUT ROW_ID vs what I have now vs full-on 64 bit 
INTEGERs - Tested below


Non-scientific Timings are below (in seconds). "HDD" = Hard drive, 
otherwise it's a SSD. "Indexes" means an index built on the FK.


==

Original structure

-- original (16 character string PK/FK) - indexes
-- 4.04
-- 4.6 (hdd)
-- 4.1
-- 4.7 (hdd)
-- 4.14
-- 5.03 (hdd)

-- original (16 character string PK/FK) - no indexes
-- 4.03
-- 5.9 (hdd)
-- 5.1
-- 11.4 (hdd)
-- 4.18
-- 9.766 (hdd)

So not much speed difference with indexes between SSD and HDD.

===

Original structure but changing to WITHOUT ROW_ID

-- original (16 character string PK/FK) - WITHOUT ROW_ID - indexes
-- 3.69
-- 2.9 (hdd)
-- 3.8
-- 5.2 (hdd)
-- 3.74
-- 5.8 (hdd)

-- original (16 character string PK/FK) - WITHOUT ROW_ID - no indexes
-- 3.45
-- 3.4 (hdd)
-- 3.4
-- 3.4 (hdd)
-- 8.47
-- 18.4 (hdd)

Curiously with the with-indexes seems to on average be slower than 
without indexes for this on the HDD.


==

Auto-incrementing INTEGER as the ID and FK

-- integer_id (autoincrement INTEGER PK/FK) - indexes
-- 1.3
-- 1.21
-- 6.9 (hdd)
-- 1.2
-- 4.4 (hdd)
-- 2.45
-- 5.2 (hdd)

-- integer_id (autoincrement INTEGER PK/FK) - no indexes
-- 1.3
-- 19.3 (hdd)
-- 4.7
-- 9.1 (hdd)
-- 5.229
-- 18.98 (hdd)

no-index speeds seem to be very random.



The last test I did was to convert the hex strings to their 64bit 
INTEGER equivalents and use those as the keys. So still using a 64bit 
INTEGER as the keys, they could be anything rather than low value


So my keys are like:
-9223326038759585676
-5012230838021194131
-3961911462337065450
3423089283580538480
9221679147258515042
...

my integer (Hex to INTEGER PK/FK - negative PKs) - index
-- 2.02
-- 2.03
-- 1.9 (hdd)
-- 1.9 (hdd)
-- 6.1
-- 1.9 (hdd)

my integer (Hex to INTEGER PK/FK - negative PKs) - no indexes
-- 2.48s
-- 2.42s
-- 2.4 (hdd)
-- 2.4 (hdd)
-- 7.5
-- 20.1 (hdd)

The HDD was consistently good with these full-size 64bit keys which 
surprised me. I've seen that there are some optimisations assuming 
positive integers -