Yes, with the new source code of the snapshot, performance is very similar (about 3 seconds), even if query plan is different.
--- On Mon, 8/16/10, Richard Hipp <d...@sqlite.org> wrote: > From: Richard Hipp <d...@sqlite.org> > Subject: Re: [sqlite] Same db, same query, very slow query performance in > sqlite 3.7.0.1 > To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org> > Date: Monday, August 16, 2010, 12:08 PM > On Mon, Aug 16, 2010 at 4:46 AM, L L > <brasiorid...@yahoo.com> > 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 > > sqlite-users@sqlite.org > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > > > -- > D. Richard Hipp > d...@sqlite.org > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
