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
