Re: [sqlite] SLOW execution: Simple Query Uses More than 1 min

[Octopus ZHANG]

I fully understand the query now. Thank you all :)


Simon Slavin  于2019年12月7日周六 下午1:23写道：

> On 7 Dec 2019, at 5:10am, Octopus ZHANG  wrote:
>
> > I received no error from the execution. Could I know how to emit the
> error message if it is over length?
>
> There was no error because the command executed successfully.  You told
> SQLite to generate a string which was
>
> 1,000,000,000,000,003
>
> bytes long, which is a many terabytes.  You operating system agreed to
> reserve enough memory for it, though it took it more than a minute to do
> so.  Had you actually tried to access all that memory you would have
> received an error.
>
> (Your operating system is allowed to do this.  Checking how much memory is
> available for every malloc takes too much time.)
>
> Everything is executing correctly.  You discovered a command which really
> does take over a minute to execute.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


-- 


Apologize to any possible reply delay for time differences.
对于任何由于时差未能及时回复邮件的情况表示抱歉。
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SLOW execution: Simple Query Uses More than 1 min

[Simon Slavin]

On 7 Dec 2019, at 5:10am, Octopus ZHANG  wrote:

> I received no error from the execution. Could I know how to emit the error 
> message if it is over length?

There was no error because the command executed successfully.  You told SQLite 
to generate a string which was

1,000,000,000,000,003

bytes long, which is a many terabytes.  You operating system agreed to reserve 
enough memory for it, though it took it more than a minute to do so.  Had you 
actually tried to access all that memory you would have received an error.

(Your operating system is allowed to do this.  Checking how much memory is 
available for every malloc takes too much time.)

Everything is executing correctly.  You discovered a command which really does 
take over a minute to execute.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SLOW execution: Simple Query Uses More than 1 min

[Octopus ZHANG]

Hi,

I received no error from the execution. Could I know how to emit the error
message if it is over length?

Richard Hipp  于2019年12月6日周五 下午9:24写道：

> On 12/6/19, Octopus ZHANG  wrote:
> > Hi all,
> >
> >
> > I'm trying to fuzz sqlite, and I found the following query was executed
> for
> > more than one minute. (./sqlite3 < query.sql)
>
> This is not a bug or a problem.  SQLite is doing exactly what you
> asked it to do, which is to generate a string that is 1003
> bytes long.  That takes time, even on a fast machine.   (Actually,
> SQLite will error-out with an over-length string error at some point,
> but it still takes some time to reach that point.)
>
> >
> >>> SELECT
> > printf('%*.*c',9||00600&66,1003)""WHERE""/"";
> >
> > I also turned on the timer, but no time was printed. So I used `time` to
> > record:
> > +--+---+
> > | real | 1m38.036s |
> > | user | 1m36.086s |
> > | sys  |  0m1.948s |
> > +--+---+
> >
> > Here is how to reproduce:
> >
> > OS: Linux 18.04.3 LTS, 4.15.0-65-generic
> > SQLite version 3.30.1 2019-10-10 20:19:45 (used default command to build)
> >
> >
> > Yushan
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
> --
> 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
>


-- 


Apologize to any possible reply delay for time differences.
对于任何由于时差未能及时回复邮件的情况表示抱歉。
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Missed index opportunities with sorting?

[Simon Slavin]

On 7 Dec 2019, at 2:26am, Shawn Wagner  wrote:

> The first one uses the index for all sorting, but the second one only uses it 
> for sorting a, not b. I feel like the descending sort could make use of the 
> index too, just reading the b sections backwards to get the right order. Is 
> there something I'm overlooking that would make this sort of optimization 
> impractical or otherwise a bad idea?

Hmm.  Try running ANALYZE and then doing the EXPLAIN QUERY PLAN lines again.

But I think that without 'chunkiness' information (how many values columns a 
and b have) it would not be worth doing the complicated programming required 
for reverse-mini-scanning of that index.  The programming is quite complicated 
and unless your index "b" is chunky it won't save you much time over the plan 
shown.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Missed index opportunities with sorting?

[Shawn Wagner]

Consider:

sqlite> CREATE TABLE test1(a, b);
sqlite> CREATE INDEX test1_idx ON test1(a, b ASC);
sqlite> EXPLAIN QUERY PLAN SELECT * FROM test1 ORDER BY a, b;
QUERY PLAN
`--SCAN TABLE test1 USING COVERING INDEX test1_idx
sqlite> EXPLAIN QUERY PLAN SELECT * FROM test1 ORDER BY a, b DESC;
QUERY PLAN
|--SCAN TABLE test1 USING COVERING INDEX test1_idx
`--USE TEMP B-TREE FOR RIGHT PART OF ORDER BY

The first one uses the index for all sorting, but the second one only uses
it for sorting a, not b. I feel like the descending sort could make use of
the index too, just reading the b sections backwards to get the right
order. Is there something I'm overlooking that would make this sort of
optimization impractical or otherwise a bad idea?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] .expert disables loaded extensions

[Jose Isaias Cabrera]

Thanks, Keith.


From: Keith Medcalf 
Sent: Friday, December 6, 2019 05:34 PM
To: SQLite mailing list 
Cc: Jose Isaias Cabrera 
Subject: RE: .expert disables loaded extensions


On Friday, 6 December, 2019 07:49. Jose Isaias Cabrera  
wrote:

>please observe the following:
> 9:45:49.39>sqlite3
>SQLite version 3.30.0 2019-10-04 15:03:17
>Enter ".help" for usage hints.
>Connected to a transient in-memory database.
>Use ".open FILENAME" to reopen on a persistent database.
>sqlite> .load c:\\PMOProjects\\libsqlite3decimal sqlite3_decimal_init
>sqlite> select decStr('1.7654');
>1.7654
>sqlite> .expert
>sqlite> select decStr('1.7654');
>Error: no such function: decStr
>sqlite>
>
>This is kind of a drag because I use expert a lot for setting indexes
>when searches are slow.  Any thoughts?  Thanks.

Actually, .expert opens another connection, and that connection does not have 
any non-autoinit extensions loaded.  You can "fix" this by applying the 
following patch to decimal.c which adds a static to keep track of the fact that 
the module has been loaded, and then adds it to the autoinit list so that it is 
activated on all subsequent connections automatically.  This means that with 
this patch you only need to load the module once and it will automatically be 
active on all subsequently created connections ... you do not need to load it 
each time, only once per process.

Index: ext/private/decimal/decimal.c
==
--- ext/private/decimal/decimal.c
+++ ext/private/decimal/decimal.c
@@ -712,17 +712,18 @@

 #endif /* SQLITE_OMIT_VIRTUALTABLE */

 #pragma mark Public interface

+static int autoinit = 1;
+
 /**
  ** \brief Entry point of the SQLite3 Decimal extension.
  **/
 #ifdef _WIN32
 __declspec(dllexport)
 #endif
-
 int sqlite3_decimal_init(sqlite3* db, char** pzErrMsg, sqlite3_api_routines 
const* pApi) {

   (void)pzErrMsg;

   int rc = SQLITE_OK;
@@ -846,9 +847,10 @@
   if (rc == SQLITE_OK) {
 rc = sqlite3_create_module_v2(db, SQLITE_DECIMAL_PREFIX "Context",
   , decimalSharedContext, 
decimalContextDestroy);
   }
 #endif
-
-  return rc;
+  if (autoinit) sqlite3_auto_extension((void*)sqlite3_decimal_init);
+  autoinit = 0;
+  return rc == SQLITE_OK ? SQLITE_OK_LOAD_PERMANENTLY : rc;
 }

--
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] last occurrence of /*

[Simon Slavin]

On 6 Dec 2019, at 11:59pm, Bart Smissaert  wrote:

> I think it can be done.
> Just dealing with the forward slash.

Then use replace(X,Y,Z) to replace '/*' with something else before you do 
whatever you were intending to do.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] last occurrence of /*

[Bart Smissaert]

I think it can be done.
Just dealing with the forward slash.

RBS

On Fri, Dec 6, 2019 at 11:49 PM Simon Slavin  wrote:

> On 6 Dec 2019, at 11:00pm, Bart Smissaert 
> wrote:
>
> > How do I select the part of this statement starting with the last /*  ?
>
> Not in SQLite.  Do it in your code, or write your own function to do it
> and load this function into SQLite.
> ___
> 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] last occurrence of /*

[Bart Smissaert]

I can do it in code. This is B4A on Android phone.
For that reason can't do UDF's or extensions.
This postcode thing was just a simple example, not to do with comment issue.

RBS

On Fri, Dec 6, 2019 at 11:48 PM Scott Robison 
wrote:

> On Fri, Dec 6, 2019, 4:31 PM Bart Smissaert 
> wrote:
>
> > I know I can do something like this:
> >
> > select replace(postcode, rtrim(postcode, replace(postcode, ' ', '')), '')
> > from addresses
> >
> > which will get the part of the postcode starting with the space.
> > Problem however is how to deal with the forward slash.
> >
>
> Do you have to do this in SQLite itself? Can you load an extension that
> provides regex? How did you go from comment strings to postcodes?
> ___
> 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] last occurrence of /*

[Simon Slavin]

On 6 Dec 2019, at 11:00pm, Bart Smissaert  wrote:

> How do I select the part of this statement starting with the last /*  ?

Not in SQLite.  Do it in your code, or write your own function to do it and 
load this function into SQLite.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] last occurrence of /*

[Scott Robison]

On Fri, Dec 6, 2019, 4:31 PM Bart Smissaert 
wrote:

> I know I can do something like this:
>
> select replace(postcode, rtrim(postcode, replace(postcode, ' ', '')), '')
> from addresses
>
> which will get the part of the postcode starting with the space.
> Problem however is how to deal with the forward slash.
>

Do you have to do this in SQLite itself? Can you load an extension that
provides regex? How did you go from comment strings to postcodes?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] last occurrence of /*

[Bart Smissaert]

I know I can do something like this:

select replace(postcode, rtrim(postcode, replace(postcode, ' ', '')), '')
from addresses

which will get the part of the postcode starting with the space.
Problem however is how to deal with the forward slash.

RBS



On Fri, Dec 6, 2019 at 11:09 PM Scott Robison 
wrote:

> On Fri, Dec 6, 2019, 4:00 PM Bart Smissaert 
> wrote:
>
> > Have table with SQL statements and these statements may have comments,
> > starting with /*
> > How do I select the part of this statement starting with the last /*  ?
> > So if the statement is:
> > select field1 /*comment 1 */ from table1 /*comment 2*/
> > I would like to get:
> > /*comment 2*/
>
>
> Is there more format to the comment than just plain text?
>
> My senior project was a SQLite helper that would read a bunch of statements
> from a sql script and create classes to automate the boilerplate code and
> ensure they were prepareable. To accomplish this I needed some new syntax
> that wouldn't interfere with SQLite, so I embedded it in comments, but they
> had to conform to a simple structure so that I could find them via regex.
>
> Perhaps, if it is only the last comment you care about, you could use a
> regex. I'm on a phone right now and don't want to try to type in valid
> regex strings, but that would be useful I could help with it when I'm near
> a real keyboard.
> ___
> 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] last occurrence of /*

[Scott Robison]

On Fri, Dec 6, 2019, 4:00 PM Bart Smissaert 
wrote:

> Have table with SQL statements and these statements may have comments,
> starting with /*
> How do I select the part of this statement starting with the last /*  ?
> So if the statement is:
> select field1 /*comment 1 */ from table1 /*comment 2*/
> I would like to get:
> /*comment 2*/


Is there more format to the comment than just plain text?

My senior project was a SQLite helper that would read a bunch of statements
from a sql script and create classes to automate the boilerplate code and
ensure they were prepareable. To accomplish this I needed some new syntax
that wouldn't interfere with SQLite, so I embedded it in comments, but they
had to conform to a simple structure so that I could find them via regex.

Perhaps, if it is only the last comment you care about, you could use a
regex. I'm on a phone right now and don't want to try to type in valid
regex strings, but that would be useful I could help with it when I'm near
a real keyboard.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] last occurrence of /*

[Bart Smissaert]

Have table with SQL statements and these statements may have comments,
starting with /*
How do I select the part of this statement starting with the last /*  ?
So if the statement is:
select field1 /*comment 1 */ from table1 /*comment 2*/
I would like to get:
/*comment 2*/

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

Re: [sqlite] .expert disables loaded extensions

[Keith Medcalf]

On Friday, 6 December, 2019 07:49. Jose Isaias Cabrera  
wrote:

>please observe the following:
> 9:45:49.39>sqlite3
>SQLite version 3.30.0 2019-10-04 15:03:17
>Enter ".help" for usage hints.
>Connected to a transient in-memory database.
>Use ".open FILENAME" to reopen on a persistent database.
>sqlite> .load c:\\PMOProjects\\libsqlite3decimal sqlite3_decimal_init
>sqlite> select decStr('1.7654');
>1.7654
>sqlite> .expert
>sqlite> select decStr('1.7654');
>Error: no such function: decStr
>sqlite>
>
>This is kind of a drag because I use expert a lot for setting indexes
>when searches are slow.  Any thoughts?  Thanks.

Actually, .expert opens another connection, and that connection does not have 
any non-autoinit extensions loaded.  You can "fix" this by applying the 
following patch to decimal.c which adds a static to keep track of the fact that 
the module has been loaded, and then adds it to the autoinit list so that it is 
activated on all subsequent connections automatically.  This means that with 
this patch you only need to load the module once and it will automatically be 
active on all subsequently created connections ... you do not need to load it 
each time, only once per process.

Index: ext/private/decimal/decimal.c
==
--- ext/private/decimal/decimal.c
+++ ext/private/decimal/decimal.c
@@ -712,17 +712,18 @@

 #endif /* SQLITE_OMIT_VIRTUALTABLE */

 #pragma mark Public interface

+static int autoinit = 1;
+
 /**
  ** \brief Entry point of the SQLite3 Decimal extension.
  **/
 #ifdef _WIN32
 __declspec(dllexport)
 #endif
-
 int sqlite3_decimal_init(sqlite3* db, char** pzErrMsg, sqlite3_api_routines 
const* pApi) {

   (void)pzErrMsg;

   int rc = SQLITE_OK;
@@ -846,9 +847,10 @@
   if (rc == SQLITE_OK) {
 rc = sqlite3_create_module_v2(db, SQLITE_DECIMAL_PREFIX "Context",
   , decimalSharedContext, 
decimalContextDestroy);
   }
 #endif
-
-  return rc;
+  if (autoinit) sqlite3_auto_extension((void*)sqlite3_decimal_init);
+  autoinit = 0;
+  return rc == SQLITE_OK ? SQLITE_OK_LOAD_PERMANENTLY : rc;
 }

-- 
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] wal mode

[Simon Slavin]

On 6 Dec 2019, at 6:39pm, MM  wrote:

> So it suffices that I run "PRAGMA journal_mode=WAL;" once from say the 
> sqlite3 cli, for all future connections from any tool will use WAL mode for 
> this database file?

Correct.

> What happens when 2 processes that have had their connection open for a 
> while, attempt a UPDATE or INSERT INTO statement at the same time?

It depends on which connections have a timeout set:



It is normal to specify a timeout of 10 seconds (or even 10 minutes) for every 
connection you open.  This means that a process which finds the database locked 
will enter a delay/retry loop for up to that amount of time before returning 
SQLITE_BUSY.

However, note that the default timeout is zero.  Which means that if you don't 
set a timeout on a connection, and it encounters a locked database, it will 
immediately return SQLITE_BUSY without retrying.

[The above explanation is simplified for clarity.]
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] wal mode

[David Raymond]

"So it suffices that I run "PRAGMA journal_mode=WAL;" once from say the
sqlite3 cli, for all future connections from any tool will use WAL mode for
this database file?"


Yup, the journal mode is stored in the database header. So the pragma will 
update the file's header, and any new connection will read the header and find 
out the mode to use from there.

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

[sqlite] wal mode

[MM]

Hello
3.26.0
From https://www.sqlite.org/wal.html, 3.3 I understand that

"The persistence of WAL mode means that applications can be converted to
using SQLite in WAL mode without making any changes to the application
itself. One has merely to run "PRAGMA journal_mode=WAL;" on the database
file(s) using the command-line shell or other utility, then restart the
application.
The WAL journal mode will be set on all connections to the same database
file if it is set on any one connection."

I use the same db file from:

   1. sqlitebrowser UI which uses C interface
   2. sqlite3 cli which uses  C interface
   3. C++ application via ODB layer which wraps C interface
   4. Python application via sqlite module which uses C interface

So it suffices that I run "PRAGMA journal_mode=WAL;" once from say the
sqlite3 cli, for all future connections from any tool will use WAL mode for
this database file?

I have say 20 processes (some C++, some python) with perhaps 16 readers and
4 writers, some python some c++.
From every single process, I open a single connection at startup and close
the connection when I don't need it anymore.
I do have concurrency between the writers too.

I suppose I am in autocommit mode as that is the default and I do nothing
to change that.

What happens when 2 processes that have had their connection open for a
while, attempt a UPDATE or INSERT INTO statement at the same time?

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

Re: [sqlite] Changes in version 3.28.0 makes sql run slower

[Radovan Antloga]

Can you explain why workaround is fast. Why one version is fast and
other slow. I'm talking about this change in where:

"and R.ID_ARHDAJ is not null"

to

"and ifnull(R.ID_ARHDAJ,0) <> 0"

For me analyze is no improvement because data is loaded from RDBMS
and would have to run always after load.

Regards Radovan

On 06.12.2019 14:20, Richard Hipp wrote:

On 12/6/19, Keith Medcalf  wrote:

Perhaps the optimizer could make a "left join" that is not actually an outer
join into a "cross join" and preserve the nesting order ... ?


It could do that, but that would kind of defeat the whole purpose of
the optimization, which was to free up the planner to choose a
different nesting order in cases where the LEFT JOIN didn't really
matter.

I suspect that ANALYZE might also solve the OP's problem.


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

[sqlite] Changes in version 3.28.0 makes sql run slower

[Radovan Antloga]

Also tested with latest version 3.30.1. For showing problem I need just 
2 tables (for join).


-- cca 30.000.000 recs
CREATE TABLE ARHDAJ(
  ID_ARHDAJ integer primary key autoincrement,
  ID_ARHPLA integer,
  R integer,
  O decimal(8,4),
  Z decimal(12,2),
  VEZA varchar(8) collate systemnocase);
CREATE UNIQUE INDEX ARHDAJ_IDX_ARHPLA on ARHDAJ (ID_ARHPLA,R,ID_ARHDAJ);
CREATE UNIQUE INDEX ARHDAJ_IDX_R on ARHDAJ (R,ID_ARHDAJ);

-- cca 100 recs
CREATE TABLE IZBPLA (
  ID_PLA    integer primary key autoincrement,
  ID_DEL    integer,
  L4MM  integer,
  OE    varchar(2) collate systemnocase,
  SM    varchar(4) collate systemnocase,
  DN    varchar(10) collate systemnocase,
  DDM   integer,
  P integer,
  U decimal(6,2),
  UN    decimal(6,2),
  O decimal(8,4),
  ZO    decimal(12,2),
  ZB    decimal(12,2),
  ZN    decimal(12,2),
  OS    decimal(8,4),
  DOH   varchar(1) collate systemnocase);
CREATE UNIQUE INDEX IZBPLA_IDX_DEL on IZBPLA(ID_DEL, ID_PLA);
CREATE UNIQUE INDEX IZBPLA_IDX_P on IZBPLA(P, ID_PLA);

Problematic sql is:

  select
    R.ID_ARHDAJ, R.ID_ARHPLA, R.R, R.O, R.Z, R.VEZA
  from IZBPLA P
  left join ARHDAJ R on R.ID_ARHPLA = P.ID_PLA
  where P.ID_PLA < 10
    and R.ID_ARHDAJ is not null;

explain query plan before 3.28.0 is:

0: SEARCH TABLE IZBPLA AS P USING INTEGER PRIMARY KEY (rowidWorkaround is to change "and R.ID_ARHDAJ is not null" to "and 
ifnull(R.ID_ARHDAJ,0) <> 0".


So sqlite goes through large table but should go through small table and 
search record in

large for every record.

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

Re: [sqlite] Changes in version 3.28.0 makes sql run slower

[Keith Medcalf]

On Friday, 6 December, 2019 08:17, radovan5  wrote:

>ANALYZE dosn't help because data is loaded from RDBMS
>for processing every time.

From this I take is that you are loading the data from somewhere else and then 
running this one query and you do not want to run ANALYZE.  If that is the case 
then simply frame the query better.  Replace the word "left" with the word 
"cross" and get rid of the superfluous "and R.ID_ARHDAJ is not null".

-- 
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] Changes in version 3.28.0 makes sql run slower

[Donald Griggs]

Hello, Radovan,

Regarding:  "ANALYZE doesn't help because data is loaded from RDBMS
for processing every time."

You can avoid the workaround, have "almost instant" analyze, and insure
against some types of inefficient queries in future by:
   1. Running analyze on a representative working database
   2. Save the data from the "sqlite_stat1" file (and also perhaps
sqlite_stat3 and sqlite_stat4 if you configure for them)
   3. When you create a new database, and BEFORE you do the long loading of
data, invoke analyze (runs very quickly)
   4. Insert the data saved from step #2 into the stat tables that analyze
just created.
   5. Load the big data, then create your indexes.
   6. Life is good.   You need to repeat the first two steps only when your
schema or data character changes significantly.

https://www.sqlite.org/optoverview.html#manctrl

I'll leave it to those more knowledgable to consider your "why" question.

P.S. don't you consider sqlite a RDBMS? ;-)

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

Re: [sqlite] Changes in version 3.28.0 makes sql run slower

[Keith Medcalf]

The first query where you constrain "R.ID_ARHDAJ is not null" in the where 
clause means that your "left join" is a mis-stated "inner join" since you are 
discarding all outer join candidate rows from the result set.  The optimizer 
recognizes this fact and "gets rid of" your superfluous use of the word "left" 
before the word "join" and processes the query as a regular inner join.  This 
means that the optimizer is free to choose which table to put in the outer 
loop.  Since it has no information about the sizes of the tables it places the 
index in the outer loop because it has the smallest size (fewer columns than 
either table) and therefore will require less disk I/O to scan.

Once you have run ANALYZE the query optimizer has better information from which 
it can build a better plan (you said the plan was correct after running 
ANALYZE).

In the second query you change the constraint to "ifnull(R.ID_ARHDAJ,0) <> 0" 
in the where clause.  The "am I excluding outer join results" optimization no 
longer recognizes that you are merely saying "R.ID_ARHDAJ is not null" (the 
parser does not do algebra) in a more convoluted way, so processes the query as 
a left join.  In a left join the RHS table must be in an inner loop compared to 
the other tables mentioned in the ON clause and this cannot be changed without 
changing the meaning of the query (ie, obtaining different results).

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

>-Original Message-
>From: sqlite-users  On
>Behalf Of radovan5
>Sent: Friday, 6 December, 2019 08:16
>To: sqlite-users@mailinglists.sqlite.org
>Subject: Re: [sqlite] Changes in version 3.28.0 makes sql run slower
>
>Can you explain why workaround is fast. Why one version is fast and
>other slow. I'm talking about this change in where:
>
>"and R.ID_ARHDAJ is not null"
>
>to
>
>"and ifnull(R.ID_ARHDAJ,0) <> 0"
>
>For me analyze is no improvement because data is loaded from RDBMS
>and would have to run always after load.
>
>Regards Radovan
>
>On 06.12.2019 14:20, Richard Hipp wrote:
>> On 12/6/19, Keith Medcalf  wrote:
>>> Perhaps the optimizer could make a "left join" that is not actually an
>outer
>>> join into a "cross join" and preserve the nesting order ... ?
>>>
>> It could do that, but that would kind of defeat the whole purpose of
>> the optimization, which was to free up the planner to choose a
>> different nesting order in cases where the LEFT JOIN didn't really
>> matter.
>>
>> I suspect that ANALYZE might also solve the OP's problem.
>
>___
>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] Changes in version 3.28.0 makes sql run slower

[David Raymond]

Those lines on their own aren't any significantly slower or faster than each 
other.

What's happening is that when you add in a function the planner has a harder 
time determining if it can use certain optimizations. The "is not null" version 
is simple enough where the planner says "ohh, I'm ok to do something nifty here 
if I think it will help". And that "something nifty" is what results in the 
join order being reversed. (Because it doesn't have any info on the sizes of 
the tables to make a more informed decision with)

In your case for example the two lines are not equivalent. If R.ID_ARGDAJ is 0, 
then the "is not null" version would include it, but the "ifnull" version would 
not. In general though the query planner "does not do algebra" to see if two 
things are functionally equivalent even if they're written slightly differently.

Some similar situation have come up here where the solution was simply to 
replace a reference of "foo" with "+foo" because simply adding the unary plus 
operator disables some optimizations.


-Original Message-
From: sqlite-users  On Behalf Of 
radovan5
Sent: Friday, December 6, 2019 10:16 AM
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] Changes in version 3.28.0 makes sql run slower

Can you explain why workaround is fast. Why one version is fast and
other slow. I'm talking about this change in where:

"and R.ID_ARHDAJ is not null"

to

"and ifnull(R.ID_ARHDAJ,0) <> 0"

For me analyze is no improvement because data is loaded from RDBMS
and would have to run always after load.

Regards Radovan

On 06.12.2019 14:20, Richard Hipp wrote:
> On 12/6/19, Keith Medcalf  wrote:
>> Perhaps the optimizer could make a "left join" that is not actually an outer
>> join into a "cross join" and preserve the nesting order ... ?
>>
> It could do that, but that would kind of defeat the whole purpose of
> the optimization, which was to free up the planner to choose a
> different nesting order in cases where the LEFT JOIN didn't really
> matter.
>
> I suspect that ANALYZE might also solve the OP's problem.

___
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] Changes in version 3.28.0 makes sql run slower

[radovan5]

ANALYZE dosn't help because data is loaded from RDBMS
for processing every time.

Why this change give different times (nobody is interested):

"and R.ID_ARHDAJ is not null"

to

"and ifnull(R.ID_ARHDAJ,0) <> 0"

Regards Radovan


On 06.12.2019 15:52, Simon Slavin wrote:

On 6 Dec 2019, at 10:05am, radovan5  wrote:


Just in my case analyze is quite slow also. So in the end I get same time. It 
is faster to use just workaround in sql.

The results of ANALYZE are stored in the database.  So although the total time 
is about the same when you do run it, once you have run ANALYZE with realistic 
data, you do not need to run it again.  Running the query will be faster every 
time in the future.

Some programmers include ANALYZE in a monthly or yearly maintenance task.
___
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] Changes in version 3.28.0 makes sql run slower

[radovan5]

Can you explain why workaround is fast. Why one version is fast and
other slow. I'm talking about this change in where:

"and R.ID_ARHDAJ is not null"

to

"and ifnull(R.ID_ARHDAJ,0) <> 0"

For me analyze is no improvement because data is loaded from RDBMS
and would have to run always after load.

Regards Radovan

On 06.12.2019 14:20, Richard Hipp wrote:

On 12/6/19, Keith Medcalf  wrote:

Perhaps the optimizer could make a "left join" that is not actually an outer
join into a "cross join" and preserve the nesting order ... ?


It could do that, but that would kind of defeat the whole purpose of
the optimization, which was to free up the planner to choose a
different nesting order in cases where the LEFT JOIN didn't really
matter.

I suspect that ANALYZE might also solve the OP's problem.


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

Re: [sqlite] Changes in version 3.28.0 makes sql run slower

[Simon Slavin]

On 6 Dec 2019, at 10:05am, radovan5  wrote:

> Just in my case analyze is quite slow also. So in the end I get same time. It 
> is faster to use just workaround in sql.

The results of ANALYZE are stored in the database.  So although the total time 
is about the same when you do run it, once you have run ANALYZE with realistic 
data, you do not need to run it again.  Running the query will be faster every 
time in the future.

Some programmers include ANALYZE in a monthly or yearly maintenance task.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] .expert disables loaded extensions

[Jose Isaias Cabrera]

Greetings.

please observe the following:
 9:45:49.39>sqlite3
SQLite version 3.30.0 2019-10-04 15:03:17
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .load c:\\PMOProjects\\libsqlite3decimal sqlite3_decimal_init
sqlite> select decStr('1.7654');
1.7654
sqlite> .expert
sqlite> select decStr('1.7654');
Error: no such function: decStr
sqlite>

This is kind of a drag because I use expert a lot for setting indexes when 
searches are slow.  Any thoughts?  Thanks.

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

Re: [sqlite] Changes in version 3.28.0 makes sql run slower

[Dominique Devienne]

On Fri, Dec 6, 2019 at 2:21 PM Richard Hipp  wrote:

> On 12/6/19, Keith Medcalf  wrote:
> >
> > Perhaps the optimizer could make a "left join" that is not actually an
> outer
> > join into a "cross join" and preserve the nesting order ... ?
>
> It could do that, but that would kind of defeat the whole purpose of
> the optimization, which was to free up the planner to choose a
> different nesting order in cases where the LEFT JOIN didn't really matter.
>
> I suspect that ANALYZE might also solve the OP's problem.
>

Yes it did (see below). But he also mentions that ANALYSE is slow though.
Any chance there could be one day a "fast-analyse" that's less precise but
still good-enough to steer the plan in the right direction? --DD

On Fri, Dec 6, 2019 at 11:06 AM radovan5  wrote:

> Yes it has and I get correct plan. Did not use analyze before [...]

analyze is quite slow also [...]
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SLOW execution: Simple Query Uses More than 1 min

[Jose Isaias Cabrera]

Octopus ZHANG, on Friday, December 6, 2019 06:18 AM, wrote...
>
> Hi all,
>
> I'm trying to fuzz sqlite, and I found the following query was executed for
> more than one minute. (./sqlite3, on
>
> >> SELECT
> printf('%*.*c',9||00600&66,1003)""WHERE""/"";
>
> I also turned on the timer, but no time was printed. So I used `time` to
> record:
> +--+---+
> | real | 1m38.036s |
> | user | 1m36.086s |
> | sys  |  0m1.948s |
> +--+---+

> Here is how to reproduce:

> OS: Linux 18.04.3 LTS, 4.15.0-65-generic
> SQLite version 3.30.1 2019-10-10 20:19:45 (used default command to build)

I actually ran out of memory...

 8:18:59.35>sqlite3
SQLite version 3.30.0 2019-10-04 15:03:17
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .timer on
sqlite>  SELECT
   ...> 
printf('%*.*c',9||00600&66,1003)""WHERE""/"";
Run Time: real 12.191 user 11.296875 sys 0.796875
Error: out of memory
sqlite>

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

Re: [sqlite] SLOW execution: Simple Query Uses More than 1 min

[Richard Hipp]

On 12/6/19, Octopus ZHANG  wrote:
> Hi all,
>
>
> I'm trying to fuzz sqlite, and I found the following query was executed for
> more than one minute. (./sqlite3 < query.sql)

This is not a bug or a problem.  SQLite is doing exactly what you
asked it to do, which is to generate a string that is 1003
bytes long.  That takes time, even on a fast machine.   (Actually,
SQLite will error-out with an over-length string error at some point,
but it still takes some time to reach that point.)

>
>>> SELECT
> printf('%*.*c',9||00600&66,1003)""WHERE""/"";
>
> I also turned on the timer, but no time was printed. So I used `time` to
> record:
> +--+---+
> | real | 1m38.036s |
> | user | 1m36.086s |
> | sys  |  0m1.948s |
> +--+---+
>
> Here is how to reproduce:
>
> OS: Linux 18.04.3 LTS, 4.15.0-65-generic
> SQLite version 3.30.1 2019-10-10 20:19:45 (used default command to build)
>
>
> Yushan
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


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

Re: [sqlite] Changes in version 3.28.0 makes sql run slower

[Richard Hipp]

On 12/6/19, Keith Medcalf  wrote:
>
> Perhaps the optimizer could make a "left join" that is not actually an outer
> join into a "cross join" and preserve the nesting order ... ?
>

It could do that, but that would kind of defeat the whole purpose of
the optimization, which was to free up the planner to choose a
different nesting order in cases where the LEFT JOIN didn't really
matter.

I suspect that ANALYZE might also solve the OP's problem.
-- 
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

Re: [sqlite] Changes in version 3.28.0 makes sql run slower

[radovan5]

Well I have to write sql like that because I must have one table in 
from. Some component I have
depend on this. This has worked fast before but I have workaround so is 
not a problem.


Regards Radovan

On 06.12.2019 11:35, Keith Medcalf wrote:

The join that you are using is not an outer join because you have constrained R.ID_ARHDAJ (which 
cannot be null in the table) to not be null, and the only way it can be null is if the left join is 
a "miss", meaning that it is really an inner join, not an outer join).  The optimizer 
spots this and does not force the nesting order implied by the "left join" operator -- 
choosing instead to process the query as an inner join.  If the reason that you are using this 
construction is specifically for the purpose of enforcing the nesting order then you should be 
phrasing your query as such:

 select R.ID_ARHDAJ, R.ID_ARHPLA, R.R, R.O, R.Z, R.VEZA
   from IZBPLA P
cross join ARHDAJ R
 on R.ID_ARHPLA = P.ID_PLA
  where P.ID_PLA < 10;

so SQLite3 knows that you *always without exception* want R to be an inner loop 
to P no matter what anyone (including the statistics say about it).

Also, now that you have run ANALYZE the information about the relative shape of 
your data has been saved for the optimizer to use when optimizing queries.  You 
do not need to run ANALYZE again unless the shape significantly changes.

Generally though you would be better served to express the select as what it 
really is, run ANALYZE from time to time, and let the optimizer do what it is 
supposed to do, which is to get what you asked for as effeciently as possible.

select R.ID_ARHDAJ, R.ID_ARHPLA, R.R, R.O, R.Z, R.VEZA
   from IZBPLA P
   join ARHDAJ R
 on R.ID_ARHPLA = P.ID_PLA
  where P.ID_PLA < 10;

Perhaps the optimizer could make a "left join" that is not actually an outer join into a 
"cross join" and preserve the nesting order ... ?



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

[sqlite] SLOW execution: Simple Query Uses More than 1 min

[Octopus ZHANG]

Hi all,


I'm trying to fuzz sqlite, and I found the following query was executed for
more than one minute. (./sqlite3 < query.sql)

>> SELECT
printf('%*.*c',9||00600&66,1003)""WHERE""/"";

I also turned on the timer, but no time was printed. So I used `time` to
record:
+--+---+
| real | 1m38.036s |
| user | 1m36.086s |
| sys  |  0m1.948s |
+--+---+

Here is how to reproduce:

OS: Linux 18.04.3 LTS, 4.15.0-65-generic
SQLite version 3.30.1 2019-10-10 20:19:45 (used default command to build)


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

Re: [sqlite] Changes in version 3.28.0 makes sql run slower

[Keith Medcalf]

The join that you are using is not an outer join because you have constrained 
R.ID_ARHDAJ (which cannot be null in the table) to not be null, and the only 
way it can be null is if the left join is a "miss", meaning that it is really 
an inner join, not an outer join).  The optimizer spots this and does not force 
the nesting order implied by the "left join" operator -- choosing instead to 
process the query as an inner join.  If the reason that you are using this 
construction is specifically for the purpose of enforcing the nesting order 
then you should be phrasing your query as such:

select R.ID_ARHDAJ, R.ID_ARHPLA, R.R, R.O, R.Z, R.VEZA
  from IZBPLA P
cross join ARHDAJ R 
on R.ID_ARHPLA = P.ID_PLA
 where P.ID_PLA < 10;

so SQLite3 knows that you *always without exception* want R to be an inner loop 
to P no matter what anyone (including the statistics say about it).

Also, now that you have run ANALYZE the information about the relative shape of 
your data has been saved for the optimizer to use when optimizing queries.  You 
do not need to run ANALYZE again unless the shape significantly changes.

Generally though you would be better served to express the select as what it 
really is, run ANALYZE from time to time, and let the optimizer do what it is 
supposed to do, which is to get what you asked for as effeciently as possible.

select R.ID_ARHDAJ, R.ID_ARHPLA, R.R, R.O, R.Z, R.VEZA
  from IZBPLA P
  join ARHDAJ R 
on R.ID_ARHPLA = P.ID_PLA
 where P.ID_PLA < 10;

Perhaps the optimizer could make a "left join" that is not actually an outer 
join into a "cross join" and preserve the nesting order ... ?

-- 
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] Changes in version 3.28.0 makes sql run slower

[Dominique Devienne]

On 06.12.2019 10:33, Shawn Wagner wrote:
> Does running an ANALYZE have any impact?
>

On Fri, Dec 6, 2019 at 11:06 AM radovan5  wrote:

> Yes it has and I get correct plan. Did not use analyze before or pragma
> optimize
> but I see I would have to. Thank you for reminding me to this. Just in my
> case
> analyze is quite slow also. So in the end I get same time. It is faster to
> use just
> workaround in sql.


I don't recall the specific, but I believe there are other ways to
influence the planning,
via hints and/or other means. If you are sure one table is always bigger
than the other,
but don't want to run ANALYSE, you can also take a generic sqlite_stat1
entries and
"inject" them "manually" into your DBs. Not sure it's good advice, but I
kinda remember
it being mentioned as well on this list in the past (I think). FWIW. --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Changes in version 3.28.0 makes sql run slower

[radovan5]

Yes it has and I get correct plan. Did not use analyze before or pragma 
optimize
but I see I would have to. Thank you for reminding me to this. Just in 
my case
analyze is quite slow also. So in the end I get same time. It is faster 
to use just

workaround in sql.

Regards Radovan

On 06.12.2019 10:33, Shawn Wagner wrote:

Does running an ANALYZE have any impact?

On Fri, Dec 6, 2019 at 12:47 AM radovan5  wrote:


Also tested with latest version 3.30.1. For showing problem I need just
2 tables (for join).

-- cca 30.000.000 recs
CREATE TABLE ARHDAJ(
ID_ARHDAJ integer primary key autoincrement,
ID_ARHPLA integer,
R integer,
O decimal(8,4),
Z decimal(12,2),
VEZA varchar(8) collate systemnocase);
CREATE UNIQUE INDEX ARHDAJ_IDX_ARHPLA on ARHDAJ (ID_ARHPLA,R,ID_ARHDAJ);
CREATE UNIQUE INDEX ARHDAJ_IDX_R on ARHDAJ (R,ID_ARHDAJ);

-- cca 100 recs
CREATE TABLE IZBPLA (
ID_PLAinteger primary key autoincrement,
ID_DELinteger,
L4MM  integer,
OEvarchar(2) collate systemnocase,
SMvarchar(4) collate systemnocase,
DNvarchar(10) collate systemnocase,
DDM   integer,
P integer,
U decimal(6,2),
UNdecimal(6,2),
O decimal(8,4),
ZOdecimal(12,2),
ZBdecimal(12,2),
ZNdecimal(12,2),
OSdecimal(8,4),
DOH   varchar(1) collate systemnocase);
CREATE UNIQUE INDEX IZBPLA_IDX_DEL on IZBPLA(ID_DEL, ID_PLA);
CREATE UNIQUE INDEX IZBPLA_IDX_P on IZBPLA(P, ID_PLA);

Problematic sql is:

select
  R.ID_ARHDAJ, R.ID_ARHPLA, R.R, R.O, R.Z, R.VEZA
from IZBPLA P
left join ARHDAJ R on R.ID_ARHPLA = P.ID_PLA
where P.ID_PLA < 10
  and R.ID_ARHDAJ is not null;

explain query plan before 3.28.0 is:

0: SEARCH TABLE IZBPLA AS P USING INTEGER PRIMARY KEY (rowid 0".

So sqlite goes through large table but should go through small table and
search record in
large for every record.

Best Regards
Radovan Antloga

___
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-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Changes in version 3.28.0 makes sql run slower

[Shawn Wagner]

Does running an ANALYZE have any impact?

On Fri, Dec 6, 2019 at 12:47 AM radovan5  wrote:

> Also tested with latest version 3.30.1. For showing problem I need just
> 2 tables (for join).
>
> -- cca 30.000.000 recs
> CREATE TABLE ARHDAJ(
>ID_ARHDAJ integer primary key autoincrement,
>ID_ARHPLA integer,
>R integer,
>O decimal(8,4),
>Z decimal(12,2),
>VEZA varchar(8) collate systemnocase);
> CREATE UNIQUE INDEX ARHDAJ_IDX_ARHPLA on ARHDAJ (ID_ARHPLA,R,ID_ARHDAJ);
> CREATE UNIQUE INDEX ARHDAJ_IDX_R on ARHDAJ (R,ID_ARHDAJ);
>
> -- cca 100 recs
> CREATE TABLE IZBPLA (
>ID_PLAinteger primary key autoincrement,
>ID_DELinteger,
>L4MM  integer,
>OEvarchar(2) collate systemnocase,
>SMvarchar(4) collate systemnocase,
>DNvarchar(10) collate systemnocase,
>DDM   integer,
>P integer,
>U decimal(6,2),
>UNdecimal(6,2),
>O decimal(8,4),
>ZOdecimal(12,2),
>ZBdecimal(12,2),
>ZNdecimal(12,2),
>OSdecimal(8,4),
>DOH   varchar(1) collate systemnocase);
> CREATE UNIQUE INDEX IZBPLA_IDX_DEL on IZBPLA(ID_DEL, ID_PLA);
> CREATE UNIQUE INDEX IZBPLA_IDX_P on IZBPLA(P, ID_PLA);
>
> Problematic sql is:
>
>select
>  R.ID_ARHDAJ, R.ID_ARHPLA, R.R, R.O, R.Z, R.VEZA
>from IZBPLA P
>left join ARHDAJ R on R.ID_ARHPLA = P.ID_PLA
>where P.ID_PLA < 10
>  and R.ID_ARHDAJ is not null;
>
> explain query plan before 3.28.0 is:
>
> 0: SEARCH TABLE IZBPLA AS P USING INTEGER PRIMARY KEY (rowid 1: SEARCH TABLE ARHDAJ AS R USING INDEX ARHDAJ_IDX_ARHPLA (ID_ARHPLA=?)
>
> explain query plan after 3.28.0 is:
>
> 0: SEARCH TABLE ARHDAJ AS R USING INDEX ARHDAJ_IDX_ARHPLA (ID_ARHPLA=?)
> 1: SEARCH TABLE IZBPLA AS P USING INTEGER PRIMARY KEY (rowid
> Workaround is to change "and R.ID_ARHDAJ is not null" to "and
> ifnull(R.ID_ARHDAJ,0) <> 0".
>
> So sqlite goes through large table but should go through small table and
> search record in
> large for every record.
>
> Best Regards
> Radovan Antloga
>
> ___
> 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] Changes in version 3.28.0 makes sql run slower

[radovan5]

Also tested with latest version 3.30.1. For showing problem I need just 
2 tables (for join).


-- cca 30.000.000 recs
CREATE TABLE ARHDAJ(
  ID_ARHDAJ integer primary key autoincrement,
  ID_ARHPLA integer,
  R integer,
  O decimal(8,4),
  Z decimal(12,2),
  VEZA varchar(8) collate systemnocase);
CREATE UNIQUE INDEX ARHDAJ_IDX_ARHPLA on ARHDAJ (ID_ARHPLA,R,ID_ARHDAJ);
CREATE UNIQUE INDEX ARHDAJ_IDX_R on ARHDAJ (R,ID_ARHDAJ);

-- cca 100 recs
CREATE TABLE IZBPLA (
  ID_PLA    integer primary key autoincrement,
  ID_DEL    integer,
  L4MM  integer,
  OE    varchar(2) collate systemnocase,
  SM    varchar(4) collate systemnocase,
  DN    varchar(10) collate systemnocase,
  DDM   integer,
  P integer,
  U decimal(6,2),
  UN    decimal(6,2),
  O decimal(8,4),
  ZO    decimal(12,2),
  ZB    decimal(12,2),
  ZN    decimal(12,2),
  OS    decimal(8,4),
  DOH   varchar(1) collate systemnocase);
CREATE UNIQUE INDEX IZBPLA_IDX_DEL on IZBPLA(ID_DEL, ID_PLA);
CREATE UNIQUE INDEX IZBPLA_IDX_P on IZBPLA(P, ID_PLA);

Problematic sql is:

  select
    R.ID_ARHDAJ, R.ID_ARHPLA, R.R, R.O, R.Z, R.VEZA
  from IZBPLA P
  left join ARHDAJ R on R.ID_ARHPLA = P.ID_PLA
  where P.ID_PLA < 10
    and R.ID_ARHDAJ is not null;

explain query plan before 3.28.0 is:

0: SEARCH TABLE IZBPLA AS P USING INTEGER PRIMARY KEY (rowidWorkaround is to change "and R.ID_ARHDAJ is not null" to "and 
ifnull(R.ID_ARHDAJ,0) <> 0".


So sqlite goes through large table but should go through small table and 
search record in

large for every record.

Best Regards
Radovan Antloga

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