change ORDER BY slowly

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

change ORDER BY slowly

MONSTRUO Hugo González
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: change ORDER BY slowly

Simon Slavin-3

On 27 Mar 2017, at 4:35pm, MONSTRUO Hugo González <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: change ORDER BY slowly

David Raymond
In reply to this post by MONSTRUO Hugo González
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:[hidden email]] On Behalf Of MONSTRUO Hugo González
Sent: Monday, March 27, 2017 11:35 AM
To: [hidden email]
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: change ORDER BY slowly

Keith Medcalf
In reply to this post by MONSTRUO Hugo González

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

> -----Original Message-----
> From: sqlite-users [mailto:[hidden email]]
> On Behalf Of MONSTRUO Hugo González
> Sent: Monday, 27 March, 2017 09:35
> To: [hidden email]
> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: change ORDER BY slowly

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:[hidden email]]
> > On Behalf Of MONSTRUO Hugo González
> > Sent: Monday, 27 March, 2017 09:35
> > To: [hidden email]
> > 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
> > [hidden email]
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Loading...