Dear both (Christian and Joe),
(I'm the original author of the first mail, I'm just using my "usual" mail,
now... :D ).
Thanks for the replies: both works fine: I have no problem in adding new
data to the DB, but the performance IS an issue.
I tested your solutions, and I got the data in 200ms, that is really good
(compared to the one before).
Thanks again
Marco
2007/7/13, Joe Wilson <[EMAIL PROTECTED]>:
--- Christian Smith <[EMAIL PROTECTED]> wrote:
> > 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.
Fair enough - just use a temp table to close that loophole.
This is pretty much optimal without changing the original poster's
schema or any application logic concerning IDMonth and MonthRef:
CREATE TABLE Months (
IDMonth INTEGER PRIMARY KEY NOT NULL,
MonthRef INTEGER
);
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
);
drop table CustomerData2 if exists;
create temp table CustomerData2 as
SELECT MonthRef,
date(MonthRef, '-1 year') as MonthRef2,
date(MonthRef, 'start of year', '-1 month') as MonthRef3,
IDCustomerData,
Months.IDMonth IDMonth,
NdgSingolo,
NdgCliente,
FatturatoNdg,
FatturatoGruppo,
MargineIntermediazioneLordo,
MargineInteresse,
MargineServizi,
RaccoltaDirettaSM,
RaccoltaIndirettaSM,
ImpieghiSM,
RaccoltaDirettaSP
FROM CustomerData, Months
WHERE CustomerData.IDMonth = Months.IDMonth;
create index CustomerData2i on CustomerData2(NdgSingolo, NdgCliente,
MonthRef);
explain query plan
SELECT AC.*,
M1.MargineIntermediazioneLordo AS MargineIntermediazioneLordo_m1,
AP.MargineIntermediazioneLordo AS MargineIntermediazioneLordo_ap,
M1.MargineInteresse AS MargineInteresse_m1,
AP.MargineInteresse AS MargineInteresse_ap
FROM CustomerData2 AC
LEFT OUTER JOIN CustomerData2 M1
ON AC.NdgSingolo = M1.NdgSingolo
AND AC.NdgCliente = M1.NdgCliente
AND M1.MonthRef = AC.MonthRef2
LEFT OUTER JOIN CustomerData2 AP
ON AC.NdgSingolo = AP.NdgSingolo
AND AC.NdgCliente = AP.NdgCliente
AND AP.MonthRef = AC.MonthRef3;
-- 0|0|TABLE CustomerData2 AS AC
-- 1|1|TABLE CustomerData2 AS M1 WITH INDEX CustomerData2i
-- 2|2|TABLE CustomerData2 AS AP WITH INDEX CustomerData2i
-- optional - temp table will be destroyed by connection anyway
drop table CustomerData2;
____________________________________________________________________________________
Boardwalk for $500? In 2007? Ha! Play Monopoly Here and Now (it's updated
for today's economy) at Yahoo! Games.
http://get.games.yahoo.com/proddesc?gamekey=monopolyherenow
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
