Re: [sqlite] Performance issue in different versions

[Nick]

I realized that the amount of memory used for the page cache is different.
And I found that is the root cause.
Sorry for my careless mistake.

Thank you.



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

Re: [sqlite] Performance issue in different versions

[Nick]

Yup, absolutely you are right.
I just ran a new test using the same upper bound on the amount of memory
used for the page cache, then I found a reasonable result.

Thank you, Dan.
I did notice the cache_size change before but you made me realize it.

Thanks a lot.



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

Re: [sqlite] Tee to a table

[Peter Da Silva]

Switch -glob and switch -regexp are also handy for this kind of code.

On 2/1/18, 3:07 PM, "sqlite-users on behalf of Peter Da Silva" 
 wrote:

You can do something like this, for one record per file:

Sqlite3 db file.sqlite
while {[gets stdin line] > 0} {
lappend content $line
}
set content [join $content "\n"; # or otherwise manipulate the text you got 
back.
db eval {INSERT INTO whatever (index, content) VALUES ($index, $content);}
db close

Or anything in between:

Sqlite3 db file.sqlite
while {[gets stdin line] > 0} {
if {[matches_end_of_content $line]} {
   db eval {INSERT INTO whatever (index, content) VALUES ($index, 
$content);}
   set content {}
   } elseif {[matches_start_of_content $line]} {
  get_index_from_content $content index
   } else {
  add_line_to_content $line content
   } 
}
db close

On 2/1/18, 3:00 PM, "sqlite-users on behalf of Cecil Westerhof" 
 wrote:

2018-02-01 21:49 GMT+01:00 Peter Da Silva 
:

> It's pretty easy in Tcl
>
> Sqlite3 db file.sqlite
> while {[gets stdin line] > 0} {
> parse_line_into index content; # or whatever you do to extract 
content
> from the line
> db eval {INSERT INTO whatever (index, content) VALUES ($index,
> $content);}
> }
> db close
>

​Looks promising.​ The 'problem' is that I get a record pro line. But 
that
is not a big problem I think. On the plus side it is easy to make a GUI
instead of a command line version.

Thanks.



> On 2/1/18, 2:25 PM, "sqlite-users on behalf of Cecil Westerhof" <
> sqlite-users-boun...@mailinglists.sqlite.org on behalf of
> cldwester...@gmail.com> wrote:
>
> At the moment I have a script where I send the output of a ffmpeg
> command
> to the terminal and a file. Is it possible to send the output to a
> SQLite
> table. I like to use tcl for this.
>

-- 
Cecil Westerhof
___
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] Tee to a table

[Peter Da Silva]

You can do something like this, for one record per file:

Sqlite3 db file.sqlite
while {[gets stdin line] > 0} {
lappend content $line
}
set content [join $content "\n"; # or otherwise manipulate the text you got 
back.
db eval {INSERT INTO whatever (index, content) VALUES ($index, $content);}
db close

Or anything in between:

Sqlite3 db file.sqlite
while {[gets stdin line] > 0} {
if {[matches_end_of_content $line]} {
   db eval {INSERT INTO whatever (index, content) VALUES ($index, 
$content);}
   set content {}
   } elseif {[matches_start_of_content $line]} {
  get_index_from_content $content index
   } else {
  add_line_to_content $line content
   } 
}
db close

On 2/1/18, 3:00 PM, "sqlite-users on behalf of Cecil Westerhof" 
 wrote:

2018-02-01 21:49 GMT+01:00 Peter Da Silva :

> It's pretty easy in Tcl
>
> Sqlite3 db file.sqlite
> while {[gets stdin line] > 0} {
> parse_line_into index content; # or whatever you do to extract content
> from the line
> db eval {INSERT INTO whatever (index, content) VALUES ($index,
> $content);}
> }
> db close
>

​Looks promising.​ The 'problem' is that I get a record pro line. But that
is not a big problem I think. On the plus side it is easy to make a GUI
instead of a command line version.

Thanks.



> On 2/1/18, 2:25 PM, "sqlite-users on behalf of Cecil Westerhof" <
> sqlite-users-boun...@mailinglists.sqlite.org on behalf of
> cldwester...@gmail.com> wrote:
>
> At the moment I have a script where I send the output of a ffmpeg
> command
> to the terminal and a file. Is it possible to send the output to a
> SQLite
> table. I like to use tcl for this.
>

-- 
Cecil Westerhof
___
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] Tee to a table

[Cecil Westerhof]

2018-02-01 21:49 GMT+01:00 Peter Da Silva :

> It's pretty easy in Tcl
>
> Sqlite3 db file.sqlite
> while {[gets stdin line] > 0} {
> parse_line_into index content; # or whatever you do to extract content
> from the line
> db eval {INSERT INTO whatever (index, content) VALUES ($index,
> $content);}
> }
> db close
>

​Looks promising.​ The 'problem' is that I get a record pro line. But that
is not a big problem I think. On the plus side it is easy to make a GUI
instead of a command line version.

Thanks.



> On 2/1/18, 2:25 PM, "sqlite-users on behalf of Cecil Westerhof" <
> sqlite-users-boun...@mailinglists.sqlite.org on behalf of
> cldwester...@gmail.com> wrote:
>
> At the moment I have a script where I send the output of a ffmpeg
> command
> to the terminal and a file. Is it possible to send the output to a
> SQLite
> table. I like to use tcl for this.
>

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

Re: [sqlite] Tee to a table

[Simon Slavin]

On 1 Feb 2018, at 8:55pm, Cecil Westerhof  wrote:

>> Can you use scripting commands to include it in a file which scripts the
>> SQLite command-line tool ?
> 
> ​I am not sure what you mean.

You can ignore my suggestion.  You are using Tcl and it's easier in Tcl.  
Sorry, I did not notice that part of your text.

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

Re: [sqlite] Tee to a table

[Cecil Westerhof]

2018-02-01 21:42 GMT+01:00 Simon Slavin :

> On 1 Feb 2018, at 8:25pm, Cecil Westerhof  wrote:
>
> > At the moment I have a script where I send the output of a ffmpeg command
> > to the terminal and a file. Is it possible to send the output to a SQLite
> > table. I like to use tcl for this.
>
> Is the output a string of text ?
>

​Text. It start for example with:
07:00:25: converting MVI_6580.MOV
ffmpeg version 3.2.8-1~deb9u1 Copyright (c) 2000-2017 the FFmpeg developers
  built with gcc 6.3.0 (Debian 6.3.0-18) 20170516
  configuration: --prefix=/usr --extra-version='1~deb9u1'
--toolchain=hardened --libdir=/usr/lib/x86_64-linux-gnu
--incdir=/usr/include/x86_64-linux-gnu --enable-gpl --disable-stripping
--enable-avresample --enable-avisynth --enable-gnutls --enable-ladspa
--enable-libass --enable-libbluray --enable-libbs2b --enable-libcaca
--enable-libcdio --enable-libebur128 --enable-libflite
--enable-libfontconfig --enable-libfreetype --enable-libfribidi
--enable-libgme --enable-libgsm --enable-libmp3lame --enable-libopenjpeg
--enable-libopenmpt --enable-libopus --enable-libpulse
--enable-librubberband --enable-libshine --enable-libsnappy
--enable-libsoxr --enable-libspeex --enable-libssh --enable-libtheora
--enable-libtwolame --enable-libvorbis --enable-libvpx --enable-libwavpack
--enable-libwebp --enable-libx265 --enable-libxvid --enable-libzmq
--enable-libzvbi --enable-omx --enable-openal --enable-opengl --enable-sdl2
--enable-libdc1394 --enable-libiec61883 --enable-chromaprint
--enable-frei0r --enable-libopencv --enable-libx264 --enable-shared
  libavutil  55. 34.101 / 55. 34.101
  libavcodec 57. 64.101 / 57. 64.101
  libavformat57. 56.101 / 57. 56.101
  libavdevice57.  1.100 / 57.  1.100
  libavfilter 6. 65.100 /  6. 65.100
  libavresample   3.  1.  0 /  3.  1.  0
  libswscale  4.  2.100 /  4.  2.100
  libswresample   2.  3.100 /  2.  3.100
  libpostproc54.  1.100 / 54.  1.100
Input #0, mov,mp4,m4a,3gp,3g2,mj2, from 'MVI_6580.MOV':
​


Can you use scripting commands to include it in a file which scripts the
> SQLite command-line tool ?
>

​I am not sure what you mean.

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

Re: [sqlite] Tee to a table

[Peter Da Silva]

It's pretty easy in Tcl

Sqlite3 db file.sqlite
while {[gets stdin line] > 0} {
parse_line_into index content; # or whatever you do to extract content from 
the line
db eval {INSERT INTO whatever (index, content) VALUES ($index, $content);}
}
db close

On 2/1/18, 2:25 PM, "sqlite-users on behalf of Cecil Westerhof" 
 wrote:

At the moment I have a script where I send the output of a ffmpeg command
to the terminal and a file. Is it possible to send the output to a SQLite
table. I like to use tcl for this.

-- 
Cecil Westerhof
___
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] Tee to a table

[Simon Slavin]

On 1 Feb 2018, at 8:25pm, Cecil Westerhof  wrote:

> At the moment I have a script where I send the output of a ffmpeg command
> to the terminal and a file. Is it possible to send the output to a SQLite
> table. I like to use tcl for this.

Is the output a string of text ?

Can you use scripting commands to include it in a file which scripts the SQLite 
command-line tool ?

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

[sqlite] Tee to a table

[Cecil Westerhof]

At the moment I have a script where I send the output of a ffmpeg command
to the terminal and a file. Is it possible to send the output to a SQLite
table. I like to use tcl for this.

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

Re: [sqlite] Easiest way to get day of week as a string (not a number)?

[Chris Green]

R Smith  wrote:
> The reason day-names are not part of the standard set of date-time 
> functions is that they are not standard and certainly not international.
> 
> For you it is "Mon, Tue, Wed, Thu, Fri, Sat Sun"...
> 
> But for me it might be:
> 
> "Lun, Mar, Mer, Jeu, Ven, Sam, Dim" or sometimes simply "Lu, Ma, Me, Je, 
> Ve, Sa, Di" etc.
> 
But systems have language variables which tell which set to use.

The C strftime function has just about every form of day and month
name you can imagine and presumably that works with the locale setting
to give them to you in your own language.

-- 
Chris Green
·

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

Re: [sqlite] Easiest way to get day of week as a string (not a number)?

[R Smith]

The reason day-names are not part of the standard set of date-time 
functions is that they are not standard and certainly not international.


For you it is "Mon, Tue, Wed, Thu, Fri, Sat Sun"...

But for me it might be:

"Lun, Mar, Mer, Jeu, Ven, Sam, Dim" or sometimes simply "Lu, Ma, Me, Je, 
Ve, Sa, Di" etc.



You could also JOIN against a sub-query enumerating the names, or even 
against a CTE defining it, or include in your DB another table or 
perhaps a View in which you define, well, any list to join against.


My favourite trivial UDF is an ELT(i, x1[, x2, x3...xn]) function (à la 
MySQL) that takes an Index as the first parameter and elects the i-th 
item from the subsequent list of parameters (x1..xn) to return, but if 
that's all you wish to achieve, that substr() solution is by far the 
simplest and quickest for day names.


Once the list gets long however, like enumerating Month names, or year 
names (like Chinese year names) then a table/view/cte join starts 
looking much simpler and certainly more elegant. Also, if your system 
needs to be multi-lingual in any way, using proper tables is really the 
only good option.


"Many ways to skin a cat" vs. "Right tool for the job" an' all that


Best of luck!
Ryan

PS: Isn't it horrible that at some point in our recent past, skinning a 
cat was not an atrocious thing



On 2018/02/01 3:50 PM, x wrote:

Don’t think you’ll get it any less ugly than

substr('SunMonTueWedThuFriSat',strftime('%w',Date)*3+1,3);

without defining your own functions.

From: Chris Green
Sent: 01 February 2018 13:13
To: 
sqlite-users@mailinglists.sqlite.org
Subject: [sqlite] Easiest way to get day of week as a string (not a number)?

I want to get Sun, Mon, Tue, Wed etc. from a date, what's the easiest
way of doing this in a sqlite select?

I guess I can do something (horrible?) with the numeric day of week
and substr() but is there not an easier way?



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

Re: [sqlite] Easiest way to get day of week as a string (not a number)?

[Chris Green]

x  wrote:
> Don’t think you’ll get it any less ugly than
> 
> substr('SunMonTueWedThuFriSat',strftime('%w',Date)*3+1,3);
> 
Yes, thanks, that's where I had got to!  :-)

-- 
Chris Green
·

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

Re: [sqlite] [EXTERNAL] Re: Easiest way to get day of week as a string (not a number)?

[Hick Gunter]

Waiting for someone to post a CTE solution along the lines of

WITH weekday(dayno,dayname) AS (SELECT (0,'Sun) ) .. SELECT dayname FROM 
weekday WHERE dayno = strftime(...).

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von x
Gesendet: Donnerstag, 01. Februar 2018 14:50
An: SQLite mailing list 
Betreff: [EXTERNAL] Re: [sqlite] Easiest way to get day of week as a string 
(not a number)?

Don’t think you’ll get it any less ugly than

substr('SunMonTueWedThuFriSat',strftime('%w',Date)*3+1,3);

without defining your own functions.

From: Chris Green
Sent: 01 February 2018 13:13
To: 
sqlite-users@mailinglists.sqlite.org
Subject: [sqlite] Easiest way to get day of week as a string (not a number)?

I want to get Sun, Mon, Tue, Wed etc. from a date, what's the easiest way of 
doing this in a sqlite select?

I guess I can do something (horrible?) with the numeric day of week and 
substr() but is there not an easier way?

--
Chris Green
·

___
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


___
 Gunter Hick | Software Engineer | Scientific Games International GmbH | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Performance issue in different versions

[Dan Kennedy]

On 02/01/2018 04:05 PM, Nick wrote:

I update sqlite in my Android mobile phone from 3.9.2 to 3.16.2.
And I find the default page_size in 3.9.2 is 1024 while in 3.16.2 is 4096
(changed since 3.12.0).
I think SQLITE_DEFAULT_PAGE_SIZE has great effect on the performance so I
use speedtest1.c to test it.

There are many test cases in speedtest1.c and case 270 is a DELETE case
which is the most time-consuming one.
There is a result.  (different version + different page_size)

  3.16.2+4096 3.16.2+1024  3.9.2+4096
3.9.2+1024
Case 270:   5.695s 5.908s   2.307s
6.130s
TOTAL  75.182s79.811s 58.723s
81.732s

It is easy to find 3.9.2+4096 is extremely faster than others.
And page_size has great effect on 3.9.2 but has only a small effect on
3.16.2.
But why?


I think 3.16.2 should faster than 3.9.2 according to the measurements of
sqlite (https://www.sqlite.org/cpu.html) but I guess 3.9.2 used 1024 at that
time.

Could someone give me any explanations? The result is strange but I think it
is reproducible if we set SQLITE_DEFAULT_PAGE_SIZE=4096 in 3.9.2.


Are you using the same sized cache in all tests?

For 3.9.2, the default cache was 2000 pages - 2MB with 1024 byte pages 
and 8MB with 4096 byte pages. But for 3.16.2 the default cache was 
changed to "-2000" - which means 2MB of cache regardless of page size. 
See the third paragraph of this:


  http://sqlite.org/pgszchng2016.html

Dan.


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

Re: [sqlite] Easiest way to get day of week as a string (not a number)?

[x]

Don’t think you’ll get it any less ugly than

substr('SunMonTueWedThuFriSat',strftime('%w',Date)*3+1,3);

without defining your own functions.

From: Chris Green
Sent: 01 February 2018 13:13
To: 
sqlite-users@mailinglists.sqlite.org
Subject: [sqlite] Easiest way to get day of week as a string (not a number)?

I want to get Sun, Mon, Tue, Wed etc. from a date, what's the easiest
way of doing this in a sqlite select?

I guess I can do something (horrible?) with the numeric day of week
and substr() but is there not an easier way?

--
Chris Green
·

___
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] Performance issue in different versions

[Richard Hipp]

On 2/1/18, Nick  wrote:
> I update sqlite in my Android mobile phone from 3.9.2 to 3.16.2.
>
> There are many test cases in speedtest1.c and case 270 is a DELETE case
> which is the most time-consuming one.
> There is a result.  (different version + different page_size)
>
>  3.16.2+4096 3.16.2+1024  3.9.2+4096
> 3.9.2+1024
> Case 270:   5.695s 5.908s   2.307s
>

We get very different results when comparing the performance of 3.9.2
to 3.16.2.  See the graph at https://www.sqlite.org/cpu.html for
example.

Since in your case, the 3.9.2 was compiled by Google and you compiled
3.16.2 yourself, I suggest that there might be some difference in your
compiler or compiler settings.
-- 
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] Easiest way to get day of week as a string (not a number)?

[John McKown]

On Thu, Feb 1, 2018 at 6:55 AM, Chris Green  wrote:

> I want to get Sun, Mon, Tue, Wed etc. from a date, what's the easiest
> way of doing this in a sqlite select?
>
> I guess I can do something (horrible?) with the numeric day of week
> and substr() but is there not an easier way?
>

​-- horrible way
SELECT CASE strftime("%w",DateInRow)
   WHEN 0 THEN "Sun"
   WHEN 1 THEN "Mon"
   ...
   WHEN 6 THEN "Sat"
   END AS DayOfWeek



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



-- 
I have a theory that it's impossible to prove anything, but I can't prove
it.

Maranatha! <><
John McKown
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Easiest way to get day of week as a string (not a number)?

[Chris Green]

I want to get Sun, Mon, Tue, Wed etc. from a date, what's the easiest
way of doing this in a sqlite select?

I guess I can do something (horrible?) with the numeric day of week
and substr() but is there not an easier way?

-- 
Chris Green
·

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

Re: [sqlite] Performance issue in different versions

[Nick]

Um, I am a OS application developer and we just upgraded the source code on
our developing engine.

I am sure I used the same compile-options. 
SQLITE_SECURE_DELETE is not set.



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

Re: [sqlite] Performance issue in different versions

[Clemens Ladisch]

Nick wrote:
> I update sqlite in my Android mobile phone from 3.9.2 to 3.16.2.

How?  Your own copy, or the system one?
Did you use the same configuration?  Especially SQLITE_SECURE_DELETE?


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

Re: [sqlite] missing subquery flattening

[E.Pasma]

Mark Brand wrote:




On 26/01/18 19:35, Clemens Ladisch wrote:

Mark Brand wrote:

Shouldn't we expect subquery flattening to happen in V2 below?

-- no flattening
CREATE VIEW V2 AS
SELECT * FROM X
LEFT JOIN (
SELECT * FROM X
LEFT JOIN Y ON Y.a = X.a
) Z
ON Z.a = X.a;

-- manually flattened version of V2
CREATE VIEW V2_FLATTENED AS
SELECT *
FROM X
LEFT JOIN X X2
   ON X2.a = X.a
LEFT JOIN Y
   ON Y.a = X2.a;
In the general case, left outer joins are not associative.   
Apparently,

SQLite does not try to prove the opposite for special cases.



The simplified case above does not make obvious the usefulness of  
flattening. A real world situation is where the right operand of a  
LEFT JOIN is a handy reusable view with a LEFT JOIN whose left side  
provides a useful index. For example:


  -- handy reusable view

CREATE VIEW W AS
 SELECT X.*, X.cost + IFNULL(Y.fee, 0) price
 FROM X
 LEFT JOIN Y
 ON Y.c = X.c;

   SELECT *
   FROM X
   LEFT JOIN W -- based on X
 ON W.a = X.a
   WHERE X.color = 'red';

W, by itself, might produce many more rows than the outer query and  
be expensive. In cases like this, it's critical for performance that  
the index of W be used for the LEFT JOIN in the outer query.


Without flattening, we have to go to some trouble to avoid using  
otherwise handy logic-encapsulating views on the right side of a  
LEFT JOIN. I've only recently realized this.


Would it make sense for sqlite to flatten this pattern? As far as I  
can see, it satisfies all the conditions listed in http://www.sqlite.org/optoverview.html#flattening 
.


Mark



Hello, may be I'm wrong or the documentation is wrong:
If the subquery is not the right operand of a LEFT JOIN then
the subquery may not be a join, and
the FROM clause of the subquery may not contain a virtual table, and
the outer query may not be an aggregate.

The "not" in the first line does not belong there?
Thanks.



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

[sqlite] Performance issue in different versions

[Nick]

I update sqlite in my Android mobile phone from 3.9.2 to 3.16.2.
And I find the default page_size in 3.9.2 is 1024 while in 3.16.2 is 4096
(changed since 3.12.0).
I think SQLITE_DEFAULT_PAGE_SIZE has great effect on the performance so I
use speedtest1.c to test it.

There are many test cases in speedtest1.c and case 270 is a DELETE case
which is the most time-consuming one.
There is a result.  (different version + different page_size)

 3.16.2+4096 3.16.2+1024  3.9.2+4096   
3.9.2+1024
Case 270:   5.695s 5.908s   2.307s  

6.130s
TOTAL  75.182s79.811s 58.723s   
 
81.732s

It is easy to find 3.9.2+4096 is extremely faster than others. 
And page_size has great effect on 3.9.2 but has only a small effect on
3.16.2.
But why?


I think 3.16.2 should faster than 3.9.2 according to the measurements of
sqlite (https://www.sqlite.org/cpu.html) but I guess 3.9.2 used 1024 at that
time.

Could someone give me any explanations? The result is strange but I think it
is reproducible if we set SQLITE_DEFAULT_PAGE_SIZE=4096 in 3.9.2. 

Thank you.





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