Newsgroup: it.comp.software.database
Titolo: [database] schema efficiente per avere uno storico dei prezzi

ho la necessità di importare quotidianamente i listini dei fornitori e 
di tenere traccia di eventuali variazioni
 
ad esempio conoscere in un dato momento gli articoli il cui prezzo è 
variato rispetto a sei mesi fa (o rispetto ad una data specifica)
oppure quali sono i prezzi degli ultimi dieci giorni oppure quali erano 
i prezzi dalla <data> alla <data>
 
attualmente (su mysql) viene usata una singola tabella con questa 
struttura:
 
CREATE TABLE `products` (
  `file_date` date NOT NULL,
  `supplier_id` varchar(128) NOT NULL,
  `description` varchar(255) DEFAULT NULL,
  `cost` double(10,2) DEFAULT NULL,
  PRIMARY KEY (`file_date`,`supplier_id`),
) ENGINE=InnoDB;
 
i tempi di lettura delle informazioni sono inaccettabili
 
una query come
 
SELECT * FROM products AS po WHERE po.supplier_id='CODICE' ORDER BY 
file_date DESC LIMIT 0,10;
 
impiega 12 secondi
 
fortunatamente questo sistema esisteva già :) e vogliono ampliarlo per 
supportare l'importazione da fornitori multipli ma anche per 
velocizzare il tutto
 
ho una macchina linux a disposizione quindi posso passare da mysql a 
qualunque altro database engine senza particolari problemi
 
siccome è la prima volta che mi trovo a dover consultare un archivio di 
questo tipo (in cui le date giocano un ruolo fondamentale) sono alla 
ricerca di letture consigliate, schemi da cui prendere spunto e 
compagnia cantante
 
grazie anche solo della lettura
 
 
 
-- 
Ti dirò una cosa Vanessa un proiettile ha lo stesso suono in ogni 
lingua, quindi ficcati nel culo una calibro nove!!!
 
 

 grazie anche solo della lettura
 
pre
 
-- 
Soprano

 pre
 
ma quanto sei sciocchino? quanto?
 
-- 
la differenza è che lei me la chiaverei e da cieca non è detto che non 
ci stia
 
 

 prezzi dalla <data> alla <data>
 
l'inefficienza secondo me è dovuta al fatto che l'indice della pk è 
invertito rispetto alla selettività che ti serve, o cambi l'ordine di 
file_date, supplier_id oppure crei un altro indice.
 
per il resto la query corretta è qualcosa del tipo (da provare, 
ovviamente):
 
WHERE po.supplier_id='CODICE' 
and po.file_date = (select max(po1.file_date)
from products po1
where po1.supplier_id = po.supplier_id
and po1.file_date <= <data> )
 
dove <data> è la data di cui vuoi sapere il prezzo attivo.
 

 dove <data> è la data di cui vuoi sapere il prezzo attivo.
 
a parte i discorsi su pk e indici, la mia esperienza mi dice che mysql si 
siede paurosamente in caso di subquery del genere..
 
meglio una SP dove eseguo le due query separate e passo la data alla 
seconda query
 
all IMVHO
 
S.
 

 si siede paurosamente in caso di subquery del genere..
 
bah io ho avuto un problema simile usando h2 (un db scrio scrio pure java 
che gira anche in embedded mode) su diversi milioni di righe e se la cava 
almeno decentemente... penso che se ci sono gli indici giusti non c'è 
motivo di sedersi, è vero che esegue molte volte la subqery che però 
dovrebbe essere istantanea visto che rimane sempre sull'indice.

 dove <data> è la data di cui vuoi sapere il prezzo attivo.
 
il prezzo di un singolo giorno non viene richiesto praticamente mai
 
come query di riferimento ho
 
- i prezzi in un determinato intervallo temporale (gli ultimi dieci 
giorni, dal 20-09-2012 al 10-10-2012)
- i prezzi che sono variati confrontando due date specifiche
 
in ogni caso sono ancora in fase di analisi ed ho tempi abbastanza 
ragionevoli per cui preferisco analizzare con calma la situazione e 
approfittando per approfondire le mie conoscenze in materia.
 
 
 
 
 
-- 
Che dire di a||&v| che non sia gia' stato detto della dissenteria?
 
 

 ) ENGINE=InnoDB;
 
Sono l'unico che sposterebbe supplier_id e description in un'altra 
tabella collegata?
E' una cretinata?
 
  bye
	av.


No, lo farei anch'io
Duplicare la descrizione non è una bella cosa a meno che non possa 
cambiare per una nuova entry (cosa che non credo)
 
 


Io in questo schema ci vedo un sacco di problemi, non solo quello, provo 
velocemente a elencarli:
 
- Le chiavi private devono essere opache e compatte
- Manca una chiave esterna e univoca per il prodotto
- Gli ID per collegare i record devono essere compatti e opachi
- Bisogna separare i dati stabili da quelli storici
- È essenziale mettere gli indici giusti, il che include anche l'uso degli 
indici composti!
- Per la cronaca, un buon DBMS usa l'indice per un ORDER BY + LIMIT
- È essenziale usare EXPLAIN per verificare che il query plan sia quello 
atteso
- Non si usano i floating point per memorizzare prezzi
- Si usano veri DBMS :)
 
Poi si entra nel campo delle opinioni:
 
- Io terrei il prezzo corrente nel record del prodotto così l'accesso è 
immediato.
- Creerei un'altra tabella con la storia dei prezzi, collegata a questa via 
product_id che contiene la data fino cui era valido e il prezzo.
- Ogni volta che un prodotto riceve un aggiornamento di prezzo si inserisce 
il nuovo prezzo nel record "product" e si inserisce nello storico il vecchio 
prezzo con la data odierna.
 
Ottenere uno storico è banale e rapido visto che è un index scan di piccole 
dimensioni.
 
Così facendo ridurresti drasticamente le dimensioni del database in termini 
di numero di record e spazio.
 
14 milioni di record non sono nulla di folle e un index scan di 10 record 
deve impiegare nell'ordine di grandezza del millisecondo, non dei 10 secondi 
:)

 14 milioni di record non sono nulla di folle
 
Concordo, anche se comincerei comunque a valutare una forma di 
partizionamento dei dati (molto dipende dal DBMS che si utilizza)
 
Enrico


 
Sono l'unico a pensare che un varchar(128) come chiave primaria sia un obrobrio?
Da come lavori la query quasi sicuramente viene fato un full table scan, quello
associato al varchar e alla mancanza di indici appropriati e' la prima causa
della lentezza, crea un vero id prodotto (intero o char fa poca differenza ma
non varchar) io ho tabelle prodotto con il codice char di 15 o 20 caratteri,
tabelle con svariati milioni di record, ma tempi assolutamente congrui, poi
oltre un certo limite l'ottimizzazione non puo' che essere hw quindi o si rimane
su MySql cambiando server (o rivedendone la cofigurazione per memoria allocabile
e potenza cpu assegnata) o si cambia MySql proprio. 

 dovrebbe essere istantanea visto che rimane sempre sull'indice.
 
Vero, ma e` vero anche che con MySQL tutto e` possibile :)
Per dire, nella query da te postata, MySQL fa prima un index scan per la 
parte in subquery e poi un full scan per il resto. Diversamente, PostgreSQL 
fa sempre un index scan, sia per la parte in subquery, sia per la parte 
esterna
 
Enrico

 Idem come sopra
 
È un corollario della precedente. Le relazioni e i vincoli di integrità 
referenziale è bene siano fatti sulle chiavi primarie con le caratteristiche 
di cui sopra.
 
Ciao,