Joe Wilson uttered:
CREATE TABLE 'Months'
(
IDMonth INTEGER PRIMARY KEY NOT NULL,
MonthRef INTEGER
);
(where MonthRef is the date of the first day of the month - created in the code)
Using what epoc?
CustomerData
------------------------------------------
CREATE TABLE 'CustomerData'
(
IDCustomerData INTEGER PRIMARY KEY NOT NULL,
IDMonth INTEGER,
NdgSingolo TEXT NOT NULL DEFAULT '0' ,
NdgCliente TEXT NOT NULL DEFAULT '0' ,
FatturatoNdg REAL DEFAULT 0 ,
FatturatoGruppo REAL DEFAULT 0 ,
MargineIntermediazioneLordo REAL DEFAULT 0 ,
MargineInteresse REAL DEFAULT 0 ,
MargineServizi REAL DEFAULT 0 ,
RaccoltaDirettaSM REAL DEFAULT 0 ,
RaccoltaIndirettaSM REAL DEFAULT 0 ,
ImpieghiSM REAL DEFAULT 0 ,
RaccoltaDirettaSP REAL DEFAULT 0
);
(where IDMonth is the foreign key to the Months table).
CustomerData contains the data of a single Customer (NdgSingolo), for
the selected month ID. What I need to do is to get "some" data in a
record from the previous year, and from the end of the previous year.
For instance, if the current month is March 2007, then I need the data
of March 2006, and of December 2006. To accomplish this, I created
these two views:
_VCustDataMonths
------------------------------------------
CREATE VIEW _VCustDataMonths AS
SELECT * FROM CustomerData A LEFT OUTER JOIN Months B ON A.IDMonth = B.IDMonth;
_VCustomerData_1
------------------------------------------
CREATE VIEW _VCustomerData_1 AS
SELECT AC.*,
M1.MargineIntermediazioneLordo AS MargineIntermediazioneLordo_m1,
AP.MargineIntermediazioneLordo AS MargineIntermediazioneLordo_ap,
M1.MargineInteresse AS MargineInteresse_m1,
AP.MargineInteresse AS MargineInteresse_ap,
FROM _VCustDataMonths AC
LEFT OUTER JOIN _VCustDataMonths M1 ON AC.NdgSingolo = M1.NdgSingolo AND
AC.NdgCliente =
M1.NdgCliente AND M1.MonthRef = date( AC.MonthRef, '-1 year' )
LEFT OUTER JOIN _VCustDataMonths AP ON AC.NdgSingolo = AP.NdgSingolo AND
AC.NdgCliente =
AP.NdgCliente AND AP.MonthRef = date( AC.MonthRef, 'start of year', '-1 month'
);
Now, the query _VCustomerData_1 (that is the one that I need) takes
*145,23 seconds* to run!! (with about 4000 records in the CustomerData
table). This is really too much...
I have indexes in the Months and CustomerData tables for the fields
NdgSingolo and NdgCliente...
How could I increase the performance of this query to get reasonable
results??
Much faster - add 3 new fields in CustomerData which you can populate
via SQLite's trigger mechanism, or an explicit UPDATE prior to your
SELECT:
MonthRef -- populate from Months table
MonthRef2 -- date(Months.MonthRef, '-1 year')
MonthRef3 -- date(Months.MonthRef, 'start of year', '-1 month')
This way you can avoid several joins with the Months table
and avoid the use of the slow view.
This is leaving you open to data errors. Better to use a single IDMonth
and calculate the join values at run time. Even better, avoid the MonthRef
table completely, and use the first day of the month directly.
My take:
CREATE TABLE 'CustomerData'
(
IDCustomerData INTEGER PRIMARY KEY NOT NULL,
IDMonth INTEGER,
NdgSingolo TEXT NOT NULL DEFAULT '0' ,
NdgCliente TEXT NOT NULL DEFAULT '0' ,
FatturatoNdg REAL DEFAULT 0 ,
FatturatoGruppo REAL DEFAULT 0 ,
MargineIntermediazioneLordo REAL DEFAULT 0 ,
MargineInteresse REAL DEFAULT 0 ,
MargineServizi REAL DEFAULT 0 ,
RaccoltaDirettaSM REAL DEFAULT 0 ,
RaccoltaIndirettaSM REAL DEFAULT 0 ,
ImpieghiSM REAL DEFAULT 0 ,
RaccoltaDirettaSP REAL DEFAULT 0
);
CREATE INDEX CustomerDataByMonth ON CustomerData(IDMonth,NdgCliente,NdgSingolo);
DROP VIEW IF EXISTS _VCustomerData_1;
CREATE VIEW _VCustomerData_1 AS
SELECT AC.*,
M1.MargineIntermediazioneLordo AS MargineIntermediazioneLordo_m1,
AP.MargineIntermediazioneLordo AS MargineIntermediazioneLordo_ap,
M1.MargineInteresse AS MargineInteresse_m1,
AP.MargineInteresse AS MargineInteresse_ap
FROM CustomerData AC
LEFT OUTER JOIN CustomerData M1
ON AC.NdgSingolo = M1.NdgSingolo
AND AC.NdgCliente = M1.NdgCliente
AND M1.IDMonth = date(AC.IDMonth,'-1 year')
LEFT OUTER JOIN CustomerData AP
ON AC.NdgSingolo = AP.NdgSingolo
AND AC.NdgCliente = AP.NdgCliente
AND AP.IDMonth = date(AC.IDMonth,'start of year', '-1 month');
Now you have the same speed as Joe's solution (similar query plan):
sqlite> explain query plan select * from _VCustomerData_1 ;
0|0|TABLE CustomerData AS AC
1|1|TABLE CustomerData AS M1 WITH INDEX CustomerDataByMonth
2|2|TABLE CustomerData AS AP WITH INDEX CustomerDataByMonth
without the duplicate data.
This assumes you use a IDMonth value that is compatible with SQLite's date
functions. But that isn't hard.
Christian
--
/"\
\ / ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
X - AGAINST MS ATTACHMENTS
/ \
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
