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.
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
