On Mon, Aug 16, 2010 at 4:46 AM, L L <[email protected]> wrote:
> Hi all,
> I have noticed a performance regression of 10 times in my db changing from
> version 3.6.32.1 to the 3.7.0.1.. It takes now 30 seconds against 3 seconds
> and query plans are different.
>
Please try again with the snapshot at
http://www.sqlite.org/draft/download.html
And let us know if the problem persists. Perhaps this has been fixed by
http://www.sqlite.org/src/info/e4b8a2ba6e
>
> Considering that table:
> customers have 800000 records
> catalogues 60000 records
> cronocatalogues 2600000
> catalogues_customers 2000
>
> This is my database:
>
> CREATE TABLE anagraphics (
> idanagraphic integer PRIMARY KEY AUTOINCREMENT,
> name text COLLATE NOCASE,
> anagraphictype text
> );
> CREATE TABLE CRONOcatalogues
> (
> idcrca INTEGER PRIMARY KEY AUTOINCREMENT,
> idcatalogue INTEGER,
> date DATETIME,
> validfrom DATETIME
> );
> CREATE TABLE catalogues
> (
> idcatalogue INTEGER PRIMARY KEY AUTOINCREMENT,
> cataloguename TEXT,
> idanagraphic INTEGER,
> type TEXT,
> active TEXT
> );
> CREATE TABLE catalogues_anagraphics
> (
> idcc INTEGER PRIMARY KEY AUTOINCREMENT,
> idcatalogue INTEGER,
> idanagraphic INTEGER,
> datereceived DATETIME
> );
> CREATE INDEX "idxanagraphictype"
> ON "anagraphics" ("anagraphictype");
> CREATE INDEX "idxdate"
> ON "CRONOcatalogues" ("date");
> CREATE INDEX "idxidcatalogue"
> ON "catalogues_anagraphics" ("idcatalogue" ASC);
> CREATE INDEX "idxidcatalogue2"
> ON "CRONOcatalogues" ("idcatalogue" ASC, "validfrom" ASC);
> CREATE INDEX "idxidanagraphic"
> ON "catalogues_anagraphics" ("idanagraphic" ASC);
>
>
> This is my query:
>
> select distinct tbl2.idcatalogue as idcatalogue, tbl2.type as type,
> tbl2.idanagraphic as idanagraphic, tbl2.maxdate as maxdate,
> anagraphics.name as nameforniture
> ,catalogues.cataloguename, anagraphics2.name as anagraphic
> from
> (
> select tbl.idcatalogue as idcatalogue, type, idanagraphic,
> tbl.maxdate as maxdate from catalogues
> inner join
> (
> select max(validfrom) as maxdate, idcatalogue from CRONOcatalogues
> group by idcatalogue
> ) as tbl on catalogues.idcatalogue=tbl.idcatalogue
> and catalogues.active='s'
> ) as tbl2
> inner join anagraphics on tbl2.idanagraphic = anagraphics.idanagraphic
> inner join catalogues on tbl2.idcatalogue = catalogues.idcatalogue
> INNER JOIN catalogues_anagraphics ON
> TBL2.idcatalogue=catalogues_anagraphics.idcatalogue
> INNER JOIN anagraphics as anagraphics2 ON
> anagraphics2.idanagraphic=catalogues_anagraphics.idanagraphic
> WHERE catalogues_anagraphics.datereceived<TBL2.maxdate
> order by nameforniture
>
> I cannot explain this regression, may be something related to subqueries?
> Thanks
>
>
>
> _______________________________________________
> sqlite-users mailing list
> [email protected]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
--
D. Richard Hipp
[email protected]
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
