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
