Re: [sqlite] count registers ina a table

[Simon Slavin]

On 30 Dec 2016, at 11:15am, MONSTRUO Hugo González  
wrote:

> [URL for a copy of his database]
> 
> SELECT COUNT (*) FROM bm_ppal
> 59.046 seconds of delay
> 
> SELECT COUNT (*) FROM bm_ppal ORDER BY nbmId
> 1.128 minutes of delay
> 
> SELECT COUNT (nbmId) FROM bm_ppal ORDER BY nbmId
> 1.089 minutes of delay
> 
> in mi program y use sqlite version: 3.8.6

Here are results from the SQLite shell tool on my system:

SQLite version 3.14.0 2016-07-26 15:17:14
Enter ".help" for usage hints.
sqlite> .timer ON
sqlite> PRAGMA integrity_check;
ok
Run Time: real 11.962 user 9.205414 sys 2.752183
sqlite> SELECT COUNT (*) FROM bm_ppal;
724816
Run Time: real 0.016 user 0.005380 sys 0.009552
sqlite> SELECT COUNT (*) FROM bm_ppal ORDER BY nbmId;
724816
Run Time: real 0.016 user 0.005368 sys 0.009806
sqlite> SELECT COUNT (nbmId) FROM bm_ppal ORDER BY nbmId;
724816
Run Time: real 0.058 user 0.046052 sys 0.010368
sqlite> 

Note that the 'real' timings on my system are all less than a second.  This is 
far more like what I would expect from SQLite.  Timings of even 1 minute for 
less than a million rows would be unusual.

It would appears that either

(a) The problem has been fixed sometime between 3.8.6 and 3.14.0 … OR
(b) There’s a problem with valentina studio 6

Of the two, (a) is the most likely.  A good next step for you to investigate 
would be to download and the SQLite shell tool for your platform.  You can find 
it as one of the "Precompiled Binaries" on this page



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

Re: [sqlite] count registers ina a table

[MONSTRUO Hugo González]

Hi,

thanks: Donald Griggs, John Gillespie, Simon Slavin

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

I need the TOTAL (1) registers in the table, and If have an active filter,
this TOTAL (2) of
registers filtered.
These Totals are displayed at all times.

The filters may be one or more.

example:
abmNombre LIKE %hugo% AND abmNombre LIKE %daniel% AND nbmCiudad = 1

SELECT COUNT (*) FROM bm_ppal
59.046 seconds of delay

SELECT COUNT (*) FROM bm_ppal ORDER BY nbmId
1.128 minutes of delay

SELECT COUNT (nbmId) FROM bm_ppal ORDER BY nbmId
1.089 minutes of delay

in mi program y use sqlite version: 3.8.6

I use valentina studio 6, version 6.6.9
SELECT sqlite_version() --> 3.14.2
PRAGMA integrity_check -->ok 48.421 seconds of delay

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

THE BEST <<<
with this order we can fill the dataset:
SELECT bm_ppal.nbmId FROM bm_ppal ORDER BY bm_ppal.nbmId
8.657seconds o delay
then we can count the rows with this other command in c#:
dataset.Tables[0].Rows.Count



with this order we can fill the datagridview - virtualmode, only de 23
first rows
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 WHERE bm_ppal.nbmId >= (SELECT MAX(nbmId) FROM (SELECT
bm_ppal2.nbmId FROM bm_ppal bm_ppal2 LEFT JOIN ciudad ciudad2 ON
ciudad2.n47Id = bm_ppal2.nbmCiudad LEFT JOIN bm_calles bm_calles2 ON
bm_calles2.nbmId = bm_ppal2.nbmCalle LEFT JOIN depto depto2 ON depto2.n46Id
= ciudad2.n47Depart ORDER BY bm_ppal2.nbmId LIMIT 1)) ORDER BY
bm_ppal.nbmId LIMIT 23
this is very very fast, only 23 rows

when filter: bm_ppal.abmNombrePlano like '%HUGO%' with this order:
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 WHERE bm_ppal.nbmId >= (SELECT MAX(nbmId) FROM (SELECT
bm_ppal2.nbmId FROM bm_ppal bm_ppal2 LEFT JOIN ciudad ciudad2 ON
ciudad2.n47Id = bm_ppal2.nbmCiudad LEFT JOIN bm_calles bm_calles2 ON
bm_calles2.nbmId = bm_ppal2.nbmCalle LEFT JOIN depto depto2 ON depto2.n46Id
= ciudad2.n47Depart WHERE bm_ppal.abmNombrePlano like '%HUGO%' ORDER BY
bm_ppal2.nbmId LIMIT 1)) AND bm_ppal.abmNombrePlano like '%HUGO%' ORDER BY
bm_ppal.nbmId LIMIT 23

is very very slowly in this table

How I can optimize it

a) count the rows with filters or not

b) read 23 rows with filters
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] count registers in a table

[Simon Slavin]

On 26 Dec 2016, at 3:14pm, MONSTRUO Hugo González  
wrote:

> I have a table with 726.000 registers.
> 
> SELECT COUNT(*) FROM MyTable << is very slowly
> 
> SELECT COUNT (RowId) FROM MyTable ORDER BY PrimaryIndex << is very FAST

While this is not a bug in SQLite, since it reaches the right answer, there is 
definitely something wrong with this.  Can you please check to see that you get 
similar timings in the SQLite shell tool.  If you do, please post the result of

SELECT sqlite_version()

Please also post the result of

PRAGMA integrity_check

To answer the questions in your post, using COUNT(*) is meant to always be the 
fastest way to count rows.  If it’s not, something is wrong.  Which appears to 
be the case with your own setup.

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

Re: [sqlite] count registers in a table

[Donald Griggs]

Hello, Hugo,

Regarding: "I have a table with 726.000 registers."

1) I assume that you mean what others call "rows" correct?   (and not
columns, I hope)


2) Regarding:   "SELECT COUNT(*) FROM MyTable << is very slowly"

As I understand it, that should be as fast as SELECT COUNT (RowId) as of
late 2013.
What version of sqlite are you using?

You can make SELECT COUNT(*) FROM MyTable be almost instantaneous, at the
cost of a little effort and a slight slowdown with inserts and deletes, by
maintaining the current row count in the database, and creating TRIGGERs to
keep it current.

http://stackoverflow.com/questions/2869135/sqlite-trigger-to-update-summary-counts


3) Regarding:  "SELECT COUNT(RowId) FROM MyTable WHERE a LIKE '%abc%' ORDER
BY PrimaryIndex
<< is FAST with more registers, but is SLOWLY with 726000 registers"

You don't mention whether you have an index defined on column "a", but even
if you did, using LIKE with a wildcard (%) at the beginning means that
SQLite cannot use an index and must scan the entire table.

Perhaps:
 a) If you only have to search for a few known strings like %abc%, then
you could check for matches at the time rows are inserted, and update a new
column that signifies, e.g., "contains a match for %abc%" and create
indexes on those new columns.
 b) If the above is not feasible, you might want to look into SQLite's
FTS feature (full text search).


4) Regarding: "Which is the fastest way to select 23 registers that meet a
condition ?"

Simon Slavin answered that in his reply to you two days ago:
   SELECT COUNT(*) FROM MyTable WHERE a = b
and, of course, you would expect a great speed increase by having either
column "a" or column "b" indexed, but remember the caveat for LIKE
expressions.

5) In general:
-- Consider whether defining more sqlite cache would help.
-- Be sure you have useful indexes defined (and remove non-useful ones.
Use EXPLAIN QUERY PLAN)
-- Be sure to use TRANSACTIONs around multiple statements to be executed as
a unit -- this won't speed up individual SELECTS that you asked about, but
can make such a tremendous speedup for other things -- such as multiple
inserts -- that I'm mentioning it.


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

Re: [sqlite] count registers in a table

[MONSTRUO Hugo González]

> Which is the fastest way to count the records of a table. ? And records
> that meet a condition?

I have a table with 726.000 registers.

SELECT COUNT(*) FROM MyTable << is very slowly

SELECT COUNT (RowId) FROM MyTable ORDER BY PrimaryIndex << is very FAST

SELECT COUNT(RowId) FROM MyTable WHERE a LIKE '%abc%' ORDER BY PrimaryIndex
<< is FAST with more registers, but is SLOWLY with 726000 registers

Which is the fastest way to count the records of a table with a condition
and 700.000 registers ?


Which is the fastest way to select 23 registers that meet a condition ?

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

Re: [sqlite] count registers in a table

[Simon Slavin]

On 24 Dec 2016, at 10:52am, MONSTRUO Hugo González  
wrote:

> Which is the fastest way to count the records of a table. ? And records
> that meet a condition?

SELECT COUNT(*) FROM MyTable


SELECT COUNT(*) FROM MyTable WHERE a = b


There is special optimization for COUNT with a * which can sometimes speed 
these enquiries up quite a bit.

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

[sqlite] count registers in a table

[MONSTRUO Hugo González]

Hi,


Which is the fastest way to count the records of a table. ? And records
that meet a condition?

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