subject:"Re\: \[sqlite\] change ORDER BY slowly"

Re: [sqlite] change ORDER BY slowly

2017-03-27 Thread Keith Medcalf


If you really want to filter as you have specified, then it will take a while.  
This is because the entire query must be performed (up to the point you are 
applying the order), then sorted, then the top rows selected to match you 
limit, then any remaining outer joins performed.

Or you can order by the "other side" of your join constraint (bm_ppal.nbmCalle) 
but then you are not guaranteed to get results with no match first (since NULL 
sorts before any value).  You can do this by putting an exists in the where 
clause to select records that DO NOT match the join constraint -- ie, where not 
exists) (for your limit of records), UNION with those that do (ie, an equijoin) 
(for your limit of records), and then sort (order by) and limit the result.  
This should only take about twice (or maybe three) times as long since you are 
in effect running the same query twice with different criterion placed on the 
outer table only.  Some additional time will be used for the extra exists test 
and for the final sort and limit.  But you will not be generating a 750,000 row 
table to sort and filter either -- you will only be generating one twice the 
size of what you want then sorting and limiting it even more.

eg:

select a, b
from
(
   select a, b
 from x
left join y on x.a = y.a
where not exists (select 1 from y where a = x.a)
order by x.a
limit 57
)
UNION
(
   select a, b
 from x
 join y on x.a = y.a
order by x.a
limit 57
)
order by b
limit 57;

> move the join to immediately follow the FROM clause
> remove the word "left"
> 
> > -Original Message-
> > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> > On Behalf Of MONSTRUO Hugo González
> > Sent: Monday, 27 March, 2017 09:35
> > To: sqlite-users@mailinglists.sqlite.org
> > Subject: [sqlite] change ORDER BY slowly
> >
> > Hi,
> >
> > I have a phone book (bm_ppal), 726.000 rows, 10 columns
> >
> > This phone book have this columns
> > Name Declared Type Type Size
> > nbmId INTEGER INTEGER
> > nbmCodigo VARCHAR (6) VARCHAR 6
> > abmNombre VARCHAR (320) VARCHAR 320
> > abmNombrePlano VARCHAR (320) VARCHAR 320
> > nbmCiudad INTEGER INTEGER
> > nbmTelefono VARCHAR (9) VARCHAR 9
> > nbmCalle INTEGER INTEGER
> > nbmNroPuerta VARCHAR (5) VARCHAR
> > nbmLongitud VARCHAR (5) VARCHAR
> > nbmLatitud VARCHAR (5) VARCHAR
> >
> > I use c# winform datagridview virtualmode
> >
> > The file.sqlite is in: https://1drv.ms/u/s!Ar9mkZ1CPa2WhkUSJLxLI0WKV_TP
> >
> > I have this sqlite sentence for read the file, very fast: 157 ms
> >
> > SELECT bm_ppal.nbmId, bm_ppal.nbmCodigo, bm_ppal.abmNombre,
> > bm_ppal.nbmCiudad, ciudad.a47Nombre, ciudad.n47Depart, depto.a46Nombre,
> >
> > bm_ppal.nbmTelefono, bm_ppal.nbmCalle, bm_calles.abmNombre,
> > bm_ppal.nbmNroPuerta, bm_ppal.nbmLongitud, bm_ppal.nbmLatitud
> >  FROM bm_ppal
> >  LEFT JOIN ciudad ON ciudad.n47Id = bm_ppal.nbmCiudad
> >  LEFT JOIN bm_calles ON bm_calles.nbmId = bm_ppal.nbmCalle
> >  LEFT JOIN depto ON depto.n46Id = ciudad.n47Depart
> >  ORDER BY bm_ppal.nbmId
> >  LIMIT 512
> >  OFFSET 0
> > 157ms
> >
> > I have this sqlite sentence for read the file, change the ORDER BY, very
> > fast: 135ms
> >
> > SELECT bm_ppal.nbmId, bm_ppal.nbmCodigo, bm_ppal.abmNombre,
> > bm_ppal.nbmCiudad, ciudad.a47Nombre, ciudad.n47Depart, depto.a46Nombre,
> >
> > bm_ppal.nbmTelefono, bm_ppal.nbmCalle, bm_calles.abmNombre,
> > bm_ppal.nbmNroPuerta, bm_ppal.nbmLongitud, bm_ppal.nbmLatitud
> >  FROM bm_ppal
> >  LEFT JOIN ciudad ON ciudad.n47Id = bm_ppal.nbmCiudad
> >  LEFT JOIN bm_calles ON bm_calles.nbmId = bm_ppal.nbmCalle
> >  LEFT JOIN depto ON depto.n46Id = ciudad.n47Depart
> >  ORDER BY bm_ppal.abmNombre, bm_ppal.nbmId
> >  LIMIT 512
> >  OFFSET 0
> > 135ms
> >
> > but when I change the ORDER BY with an attributt of other table is very
> > slowly: 5699 ms
> >
> > SELECT bm_ppal.nbmId, bm_ppal.nbmCodigo, bm_ppal.abmNombre,
> > bm_ppal.nbmCiudad, ciudad.a47Nombre, ciudad.n47Depart, depto.a46Nombre,
> >
> > bm_ppal.nbmTelefono, bm_ppal.nbmCalle, bm_calles.abmNombre,
> > bm_ppal.nbmNroPuerta, bm_ppal.nbmLongitud, bm_ppal.nbmLatitud
> >  FROM bm_ppal
> >  LEFT JOIN ciudad ON ciudad.n47Id = bm_ppal.nbmCiudad
> >  LEFT JOIN bm_calles ON bm_calles.nbmId = bm_ppal.nbmCalle
> >  LEFT JOIN depto ON depto.n46Id = ciudad.n47Depart
> >  ORDER BY bm_calles.abmNombre
> >  LIMIT 512
> >  OFFSET 0
> > 5699ms ??
> >
> > I create the index with:
> >
> > CREATE INDEX IF NOT EXISTS idxbm_calles2 ON bm_calles(abmNombre);
> >
> > where I create an index in the table bm_ppal for bm_calles(abmNombre)
> with
> > the LEFT JOIN bm_calles ON bm_calles.nbmId = bm_ppal.nbmCalle
> >
> > How I can optimize it ?
> > ___
> > 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

Re: [sqlite] change ORDER BY slowly

2017-03-27 Thread Keith Medcalf


move the join to immediately follow the FROM clause
remove the word "left"

> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of MONSTRUO Hugo González
> Sent: Monday, 27 March, 2017 09:35
> To: sqlite-users@mailinglists.sqlite.org
> Subject: [sqlite] change ORDER BY slowly
> 
> Hi,
> 
> I have a phone book (bm_ppal), 726.000 rows, 10 columns
> 
> This phone book have this columns
> Name Declared Type Type Size
> nbmId INTEGER INTEGER
> nbmCodigo VARCHAR (6) VARCHAR 6
> abmNombre VARCHAR (320) VARCHAR 320
> abmNombrePlano VARCHAR (320) VARCHAR 320
> nbmCiudad INTEGER INTEGER
> nbmTelefono VARCHAR (9) VARCHAR 9
> nbmCalle INTEGER INTEGER
> nbmNroPuerta VARCHAR (5) VARCHAR
> nbmLongitud VARCHAR (5) VARCHAR
> nbmLatitud VARCHAR (5) VARCHAR
> 
> I use c# winform datagridview virtualmode
> 
> The file.sqlite is in: https://1drv.ms/u/s!Ar9mkZ1CPa2WhkUSJLxLI0WKV_TP
> 
> I have this sqlite sentence for read the file, very fast: 157 ms
> 
> SELECT bm_ppal.nbmId, bm_ppal.nbmCodigo, bm_ppal.abmNombre,
> bm_ppal.nbmCiudad, ciudad.a47Nombre, ciudad.n47Depart, depto.a46Nombre,
> 
> bm_ppal.nbmTelefono, bm_ppal.nbmCalle, bm_calles.abmNombre,
> bm_ppal.nbmNroPuerta, bm_ppal.nbmLongitud, bm_ppal.nbmLatitud
>  FROM bm_ppal
>  LEFT JOIN ciudad ON ciudad.n47Id = bm_ppal.nbmCiudad
>  LEFT JOIN bm_calles ON bm_calles.nbmId = bm_ppal.nbmCalle
>  LEFT JOIN depto ON depto.n46Id = ciudad.n47Depart
>  ORDER BY bm_ppal.nbmId
>  LIMIT 512
>  OFFSET 0
> 157ms
> 
> I have this sqlite sentence for read the file, change the ORDER BY, very
> fast: 135ms
> 
> SELECT bm_ppal.nbmId, bm_ppal.nbmCodigo, bm_ppal.abmNombre,
> bm_ppal.nbmCiudad, ciudad.a47Nombre, ciudad.n47Depart, depto.a46Nombre,
> 
> bm_ppal.nbmTelefono, bm_ppal.nbmCalle, bm_calles.abmNombre,
> bm_ppal.nbmNroPuerta, bm_ppal.nbmLongitud, bm_ppal.nbmLatitud
>  FROM bm_ppal
>  LEFT JOIN ciudad ON ciudad.n47Id = bm_ppal.nbmCiudad
>  LEFT JOIN bm_calles ON bm_calles.nbmId = bm_ppal.nbmCalle
>  LEFT JOIN depto ON depto.n46Id = ciudad.n47Depart
>  ORDER BY bm_ppal.abmNombre, bm_ppal.nbmId
>  LIMIT 512
>  OFFSET 0
> 135ms
> 
> but when I change the ORDER BY with an attributt of other table is very
> slowly: 5699 ms
> 
> SELECT bm_ppal.nbmId, bm_ppal.nbmCodigo, bm_ppal.abmNombre,
> bm_ppal.nbmCiudad, ciudad.a47Nombre, ciudad.n47Depart, depto.a46Nombre,
> 
> bm_ppal.nbmTelefono, bm_ppal.nbmCalle, bm_calles.abmNombre,
> bm_ppal.nbmNroPuerta, bm_ppal.nbmLongitud, bm_ppal.nbmLatitud
>  FROM bm_ppal
>  LEFT JOIN ciudad ON ciudad.n47Id = bm_ppal.nbmCiudad
>  LEFT JOIN bm_calles ON bm_calles.nbmId = bm_ppal.nbmCalle
>  LEFT JOIN depto ON depto.n46Id = ciudad.n47Depart
>  ORDER BY bm_calles.abmNombre
>  LIMIT 512
>  OFFSET 0
> 5699ms ??
> 
> I create the index with:
> 
> CREATE INDEX IF NOT EXISTS idxbm_calles2 ON bm_calles(abmNombre);
> 
> where I create an index in the table bm_ppal for bm_calles(abmNombre) with
> the LEFT JOIN bm_calles ON bm_calles.nbmId = bm_ppal.nbmCalle
> 
> How I can optimize it ?
> ___
> 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] change ORDER BY slowly

2017-03-27 Thread David Raymond

The main issue there I believe is that the order by/limit for the first two is 
on the outermost table, so it can order by/limit that right away. In the last 
query the order by/limit is on a table in the middle, so it can't order or 
filter on it right away, and needs the temp tree there.

I saw a small speed increase by putting the bm_calles join as the first one, 
doing the sort/limit after that 1 join, then doing the rest of the joins. But 
that was only 10% or so. So I'm not sure how best to optimize that. If they 
were inner joins then it could do it in any order it wanted, and thus be just 
as fast. But with the outer joins then I'm not sure.

Also note that "unique primary key" is redundant and creates an extra unneeded 
index.


Your first query (ORDER BY bm_ppal.nbmId  LIMIT 512):
selectid|order|from|detail
0|0|0|SCAN TABLE bm_ppal
0|1|1|SEARCH TABLE ciudad USING INTEGER PRIMARY KEY (rowid=?)
0|2|2|SEARCH TABLE bm_calles USING INTEGER PRIMARY KEY (rowid=?)
0|3|3|SEARCH TABLE depto USING INTEGER PRIMARY KEY (rowid=?)


Your second query (ORDER BY bm_ppal.abmNombre, bm_ppal.nbmId LIMIT 512):
selectid|order|from|detail
0|0|0|SCAN TABLE bm_ppal USING INDEX idxbm_ppal2
0|1|1|SEARCH TABLE ciudad USING INTEGER PRIMARY KEY (rowid=?)
0|2|2|SEARCH TABLE bm_calles USING INTEGER PRIMARY KEY (rowid=?)
0|3|3|SEARCH TABLE depto USING INTEGER PRIMARY KEY (rowid=?)


Your third (slow) query (ORDER BY bm_calles.abmNombre LIMIT 512):
selectid|order|from|detail
0|0|0|SCAN TABLE bm_ppal
0|1|1|SEARCH TABLE ciudad USING INTEGER PRIMARY KEY (rowid=?)
0|2|2|SEARCH TABLE bm_calles USING INTEGER PRIMARY KEY (rowid=?)
0|3|3|SEARCH TABLE depto USING INTEGER PRIMARY KEY (rowid=?)
0|0|0|USE TEMP B-TREE FOR ORDER BY


(Not very helpful) Reordering (To make use of the limit as soon as possible):
selectid|order|from|detail
1|0|0|SCAN TABLE bm_ppal
1|1|1|SEARCH TABLE bm_calles USING INTEGER PRIMARY KEY (rowid=?)
1|0|0|USE TEMP B-TREE FOR ORDER BY
0|0|0|SCAN SUBQUERY 1 AS tmp
0|1|1|SEARCH TABLE ciudad USING INTEGER PRIMARY KEY (rowid=?)
0|2|2|SEARCH TABLE depto USING INTEGER PRIMARY KEY (rowid=?)


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of MONSTRUO Hugo González
Sent: Monday, March 27, 2017 11:35 AM
To: sqlite-users@mailinglists.sqlite.org
Subject: [sqlite] change ORDER BY slowly

Hi,

I have a phone book (bm_ppal), 726.000 rows, 10 columns

This phone book have this columns
Name Declared Type Type Size
nbmId INTEGER INTEGER
nbmCodigo VARCHAR (6) VARCHAR 6
abmNombre VARCHAR (320) VARCHAR 320
abmNombrePlano VARCHAR (320) VARCHAR 320
nbmCiudad INTEGER INTEGER
nbmTelefono VARCHAR (9) VARCHAR 9
nbmCalle INTEGER INTEGER
nbmNroPuerta VARCHAR (5) VARCHAR
nbmLongitud VARCHAR (5) VARCHAR
nbmLatitud VARCHAR (5) VARCHAR

I use c# winform datagridview virtualmode

The file.sqlite is in: https://1drv.ms/u/s!Ar9mkZ1CPa2WhkUSJLxLI0WKV_TP

I have this sqlite sentence for read the file, very fast: 157 ms

SELECT bm_ppal.nbmId, bm_ppal.nbmCodigo, bm_ppal.abmNombre,
bm_ppal.nbmCiudad, ciudad.a47Nombre, ciudad.n47Depart, depto.a46Nombre,

bm_ppal.nbmTelefono, bm_ppal.nbmCalle, bm_calles.abmNombre,
bm_ppal.nbmNroPuerta, bm_ppal.nbmLongitud, bm_ppal.nbmLatitud
 FROM bm_ppal
 LEFT JOIN ciudad ON ciudad.n47Id = bm_ppal.nbmCiudad
 LEFT JOIN bm_calles ON bm_calles.nbmId = bm_ppal.nbmCalle
 LEFT JOIN depto ON depto.n46Id = ciudad.n47Depart
 ORDER BY bm_ppal.nbmId
 LIMIT 512
 OFFSET 0
157ms

I have this sqlite sentence for read the file, change the ORDER BY, very
fast: 135ms

SELECT bm_ppal.nbmId, bm_ppal.nbmCodigo, bm_ppal.abmNombre,
bm_ppal.nbmCiudad, ciudad.a47Nombre, ciudad.n47Depart, depto.a46Nombre,

bm_ppal.nbmTelefono, bm_ppal.nbmCalle, bm_calles.abmNombre,
bm_ppal.nbmNroPuerta, bm_ppal.nbmLongitud, bm_ppal.nbmLatitud
 FROM bm_ppal
 LEFT JOIN ciudad ON ciudad.n47Id = bm_ppal.nbmCiudad
 LEFT JOIN bm_calles ON bm_calles.nbmId = bm_ppal.nbmCalle
 LEFT JOIN depto ON depto.n46Id = ciudad.n47Depart
 ORDER BY bm_ppal.abmNombre, bm_ppal.nbmId
 LIMIT 512
 OFFSET 0
135ms

but when I change the ORDER BY with an attributt of other table is very
slowly: 5699 ms

SELECT bm_ppal.nbmId, bm_ppal.nbmCodigo, bm_ppal.abmNombre,
bm_ppal.nbmCiudad, ciudad.a47Nombre, ciudad.n47Depart, depto.a46Nombre,

bm_ppal.nbmTelefono, bm_ppal.nbmCalle, bm_calles.abmNombre,
bm_ppal.nbmNroPuerta, bm_ppal.nbmLongitud, bm_ppal.nbmLatitud
 FROM bm_ppal
 LEFT JOIN ciudad ON ciudad.n47Id = bm_ppal.nbmCiudad
 LEFT JOIN bm_calles ON bm_calles.nbmId = bm_ppal.nbmCalle
 LEFT JOIN depto ON depto.n46Id = ciudad.n47Depart
 ORDER BY bm_calles.abmNombre
 LIMIT 512
 OFFSET 0
5699ms ??

I create the index with:

CREATE INDEX IF NOT EXISTS idxbm_calles2 ON bm_calles(abmNombre);

where I create an index in the table bm_ppal for bm_calles(abmNombre) with
the LEFT JOIN bm_calles ON bm_calles.nbmId = bm_ppal.nbmCalle

How I can optimize it ?
___
sqlite-users mailing list

Re: [sqlite] change ORDER BY slowly

2017-03-27 Thread Simon Slavin


On 27 Mar 2017, at 4:35pm, MONSTRUO Hugo González  
wrote:

> nbmCodigo VARCHAR (6) VARCHAR 6

I wanted to note that SQLite completely ignores VARCHAR and any size limits.  
As far as SQLite is concerned, all those fields are TEXT fields and can contain 
any number of characters.  If your code is assuming that SQLite will truncate 
your Codigo field to six character you might want to take another look.

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

Re: [sqlite] change ORDER BY slowly

Re: [sqlite] change ORDER BY slowly

Re: [sqlite] change ORDER BY slowly

Re: [sqlite] change ORDER BY slowly

4 matches

Site Navigation

Mail list logo

Footer information