I'm no expert, but I believe I recall reading on one of the SQLite docs that the performance of Joins can be a bit slower than on other DB systems. Seeing as you have a couple of tables being joined, this could be the case. I also think I recall reading some posts in this list about people changing their joins into subselects and gaining some performance.
Then again, I'm no expert. // -------------------------------------- Enrique Ramirez Irizarry Lead Developer Indie Code Labs http://www.indiecodelabs.com On Mon, Oct 27, 2008 at 7:03 AM, L B <[EMAIL PROTECTED]> wrote: > Hi all, > I have a 500mb database in access, migrated to sqlite. > > > The structure is: > > --this table has 100 records > CREATE TABLE CATALOGUES > ( > IDCATALOGUE INTEGER PRIMARY KEY AUTOINCREMENT, > CATALOGUENAME TEXT, > IDPERSONALDATA INTEGER, > TYPE INTEGER, > ACTIVE INTEGER > ); > CREATE INDEX IDX_CATALOGUES_IDCATALOGUE ON CATALOGUES > (IDCATALOGUE); > CREATE INDEX IDX_CATALOGUES_ACTIVE ON CATALOGUES > (ACTIVE); > CREATE INDEX IDX_CATALOGUES_TYPE ON CATALOGUES (TYPE); > CREATE INDEX IDX_CATALOGUES_IDPERSONALDATA ON > CATALOGUES (IDPERSONALDATA); > CREATE INDEX IDX_CATALOGUES_IDCATALOGUE ON CATALOGUES > (IDCATALOGUE); > > --this table has 3 000 records > CREATE TABLE CATALOGUES_PERSONALDATAS > ( > IDCC INTEGER PRIMARY KEY AUTOINCREMENT, > IDCATALOGUE INTEGER, > IDPERSONALDATA INTEGER, > AMOUNT INTEGER, > REDUCTION TEXT, > DTDELIVERED DATETIME, > ); > CREATE INDEX IDX_CATALOGUES_PERSONALDATAS_DTDELIVERED > ON CATALOGUES_PERSONALDATAS > (DTDELIVERED); > CREATE INDEX IDX_CATALOGUES_PERSONALDATAS_IDCC ON > CATALOGUES_PERSONALDATAS (IDCC); > CREATE INDEX > IDX_CATALOGUES_PERSONALDATAS_IDPERSONALDATA ON > CATALOGUES_PERSONALDATAS (IDPERSONALDATA); > CREATE INDEX IDX_CATALOGUES_PERSONALDATAS_IDCATALOGUE > ON CATALOGUES_PERSONALDATAS (IDCATALOGUE); > > --this table has 2 500 000 records!! > CREATE TABLE CRONOCATALOGUES > ( > IDCRCA INTEGER PRIMARY KEY AUTOINCREMENT, > IDCATALOGUE INTEGER, > DT DATETIME, > NUMBER INTEGER, > DTFROM DATETIME > ); > CREATE INDEX IDX_CRONOCATALOGUES_DTFROM > ON CRONOCATALOGUES > (DTFROM); > CREATE INDEX IDX_CRONOCATALOGUES_IDCATALOGUE ON > CRONOCATALOGUES (IDCATALOGUE); > > --this table has 800 000 records > CREATE TABLE PERSONALDATAS ( > IDPERSONALDATA INTEGER PRIMARY KEY > AUTOINCREMENT, > IDTYPE INTEGER, > NAME TEXT COLLATE NOCASE > ); > CREATE INDEX IDX_PERSONALDATAS_IDTYPE > ON PERSONALDATAS > (IDTYPE); > > > > I cannot obtain similar performances on sqlite, having > a 40-50% difference. > (9 seconds vs. 13 seconds on windows xp, average > results, after first executions (OS cache) ) > > > The query in access is: > > --IN ACCESS > SELECT DISTINCT TBL.*, PERSONALDATAS.NAME > FROM > ( > SELECT CATALOGUES.IDCATALOGUE, CATALOGUES.TYPE, > VENDORS.NAME AS VENDORNAME, > CATALOGUES.CATALOGUENAME, MAX(CRONOCATALOGUES.DTFROM) > AS MAXDT > FROM PERSONALDATAS AS VENDORS INNER JOIN > CATALOGUES ON VENDORS.IDPERSONALDATA = > CATALOGUES.IDPERSONALDATA INNER JOIN CRONOCATALOGUES > ON CATALOGUES.IDCATALOGUE = > CRONOCATALOGUES.IDCATALOGUE > WHERE CATALOGUES.ACTIVE=0 > GROUP BY CATALOGUES.IDCATALOGUE, VENDORS.NAME, > CATALOGUES.CATALOGUENAME, CATALOGUES.TYPE > ) AS TBL > INNER JOIN CATALOGUES_PERSONALDATAS ON > (TBL."CATALOGUES.IDCATALOGUE"=CATALOGUES_PERSONALDATAS.IDCATALOGUE) > INNER JOIN PERSONALDATAS ON > (PERSONALDATAS.IDPERSONALDATA=CATALOGUES_PERSONALDATAS.IDPERSONALDATA) > WHERE > CATALOGUES_PERSONALDATAS.DTDELIVERED<TBL."MAXDT" > ORDER BY VENDORNAME > > > In sqlite, same query is really to slow (several > minutes, even if i put an index in the text field > PERSONALDATAS.NAME). > So after several tries and days, best performances I > get is with (but it is still 40% slower): > > --IN SQLITE > SELECT DISTINCT > TBL3.*, PERSONALDATAS.NAME AS VENDORNAME > FROM > ( > SELECT TBL2.IDCATALOGUE AS IDCATALOGUE, TBL2.TYPE AS > TYPE, TBL2.IDPERSONALDATA AS IDPERSONALDATA, > TBL2.MAXDT AS MAXDT, PERSONALDATAS.NAME AS VENDORNAME > ,CATALOGUES.CATALOGUENAME AS CATALOGUENAME > FROM > ( > SELECT TBL.IDCATALOGUE AS IDCATALOGUE, TYPE, > IDPERSONALDATA, TBL.MAXDT AS MAXDT FROM CATALOGUES > INNER JOIN > ( > SELECT MAX(DTFROM) AS MAXDT, IDCATALOGUE FROM > CRONOCATALOGUES > GROUP BY IDCATALOGUE > ) AS TBL ON CATALOGUES.IDCATALOGUE=TBL.IDCATALOGUE > AND CATALOGUES.ACTIVE=0 > ) AS TBL2 > INNER JOIN PERSONALDATAS ON TBL2.IDPERSONALDATA = > PERSONALDATAS.IDPERSONALDATA > INNER JOIN CATALOGUES ON TBL2.IDCATALOGUE = > CATALOGUES.IDCATALOGUE > ) AS TBL3 > INNER JOIN CATALOGUES_PERSONALDATAS ON > TBL3.IDCATALOGUE=CATALOGUES_PERSONALDATAS.IDCATALOGUE > INNER JOIN PERSONALDATAS ON > PERSONALDATAS.IDPERSONALDATA=CATALOGUES_PERSONALDATAS.IDPERSONALDATA > > WHERE CATALOGUES_PERSONALDATAS.DTDELIVERED<TBL3.MAXDT > ORDER BY TBL3.VENDORNAME > > Cannot find a way to get same performances. > > What's wrong with my query? > > > Thanks very much, any suggestion is really > appreciated! > LB > > > > > > > _______________________________________________ > 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
