[SQL] Esecuzione di query con viste troppo inefficiente... mi potete aiutare?

[doctorAle85]

Prima ho aperto un post sulla copia di un DB da Postgre a Mysql perchè pensavo che MySQL fosse più veloce nell'eseguire query con viste...invece nn è assolutamente vero!
Il mio problema è questo...
Devo rispondere a delle query in 2 modi...
Nel primo modo utilizzo solo simboli di tabelle presenti nel DB, mentre nel 2° caso utilizzo solo viste.
Nel primo caso sebbene la query sql sia molto più lunga ci mette 200ms con Postgre, mentre per eseguire la query con le viste ci mette 3 sec.
Come cavolo è possibile? A me sembra una cosa assurda...
Non è che bisogna settare qualcosa in particolare?
Cioè, nn capisco come possa eserci questa differenza...
...nn vi sembra strano?

Vi copio le 2 query...

----------Query con simboli di tabelle del DB-----------

SELECT DISTINCT alias_4.term1, alias_4.term2, alias_0.term2, alias_2.term2 FROM EA alias_0 , IH alias_1 , T alias_2 , VP alias_3 , NM alias_4 WHERE alias_0.term1=alias_1.term1 AND alias_1.term2='c' AND alias_1.term1=alias_2.term1 AND alias_2.term1=alias_3.term AND alias_3.term=alias_4.term1
UNION
SELECT DISTINCT alias_4.term1, alias_4.term2, alias_0.term2, alias_2.term2 FROM EA alias_0 , IH alias_1 , TEL alias_2 , ISA alias_3 , NM alias_4 WHERE alias_0.term1=alias_1.term1 AND alias_1.term2='c' AND alias_1.term1=alias_2.term1 AND alias_2.term1=alias_3.term2 AND alias_3.term2=alias_4.term1
UNION
SELECT DISTINCT alias_4.term1, alias_4.term2, alias_0.term2, alias_2.term2 FROM EA alias_0 , WF alias_1 , TEL alias_2 , CH alias_3 , NM alias_4 WHERE alias_0.term1=alias_1.term1 AND alias_1.term2='c' AND alias_1.term1=alias_2.term1 AND alias_2.term1=alias_3.term AND alias_3.term=alias_4.term1
UNION
SELECT DISTINCT alias_4.term1, alias_4.term2, alias_0.term2, alias_2.term2 FROM EA alias_0 , WF alias_1 , TEL alias_2 , AP alias_3 , NM alias_4 WHERE alias_0.term1=alias_1.term1 AND alias_1.term2='c' AND alias_1.term1=alias_2.term1 AND alias_2.term1=alias_3.term AND alias_3.term=alias_4.term1
UNION
SELECT DISTINCT alias_4.term1, alias_4.term2, alias_0.term2, alias_2.term2 FROM EA alias_0 , IH alias_1 , TEL alias_2 , AP alias_3 , NM alias_4 WHERE alias_0.term1=alias_1.term1 AND alias_1.term2='c' AND alias_1.term1=alias_2.term1 AND alias_2.term1=alias_3.term AND alias_3.term=alias_4.term1
UNION
SELECT DISTINCT alias_4.term1, alias_4.term2, alias_1.term2, alias_3.term2 FROM P alias_0 , EA alias_1 , IH alias_2 , TEL alias_3 , NM alias_4 WHERE alias_0.term=alias_1.term1 AND alias_1.term1=alias_2.term1 AND alias_2.term2='c' AND alias_2.term1=alias_3.term1 AND alias_3.term1=alias_4.term1
UNION
SELECT DISTINCT alias_4.term1, alias_4.term2, alias_0.term2, alias_3.term2 FROM EA alias_0 , TN alias_1 , IH alias_2 , TEL alias_3 , NM alias_4 WHERE alias_0.term1=alias_1.term1 AND alias_1.term1=alias_2.term1 AND alias_2.term2='c' AND alias_2.term1=alias_3.term1 AND alias_3.term1=alias_4.term1
UNION
SELECT DISTINCT alias_4.term1, alias_4.term2, alias_0.term2, alias_2.term2 FROM EA alias_0 , IH alias_1 , TEL alias_2 , CH alias_3 , NM alias_4 WHERE alias_0.term1=alias_1.term1 AND alias_1.term2='c' AND alias_1.term1=alias_2.term1 AND alias_2.term1=alias_3.term AND alias_3.term=alias_4.term1
UNION
SELECT DISTINCT alias_4.term1, alias_4.term2, alias_0.term2, alias_3.term2 FROM EA alias_0 , FP alias_1 , WF alias_2 , TEL alias_3 , NM alias_4 WHERE alias_0.term1=alias_1.term AND alias_1.term=alias_2.term1 AND alias_2.term2='c' AND alias_2.term1=alias_3.term1 AND alias_3.term1=alias_4.term1
UNION
SELECT DISTINCT alias_4.term1, alias_4.term2, alias_0.term2, alias_2.term2 FROM EA alias_0 , WF alias_1 , TEL alias_2 , VP alias_3 , NM alias_4 WHERE alias_0.term1=alias_1.term1 AND alias_1.term2='c' AND alias_1.term1=alias_2.term1 AND alias_2.term1=alias_3.term AND alias_3.term=alias_4.term1
UNION
SELECT DISTINCT alias_4.term1, alias_4.term2, alias_1.term2, alias_3.term2 FROM AP alias_0 , EA alias_1 , IH alias_2 , TEL alias_3 , NM alias_4 WHERE alias_0.term=alias_1.term1 AND alias_1.term1=alias_2.term1 AND alias_2.term2='c' AND alias_2.term1=alias_3.term1 AND alias_3.term1=alias_4.term1
UNION
SELECT DISTINCT alias_4.term1, alias_4.term2, alias_0.term2, alias_2.term2 FROM EA alias_0 , WF alias_1 , TEL alias_2 , Dean alias_3 , NM alias_4 WHERE alias_0.term1=alias_1.term1 AND alias_1.term2='c' AND alias_1.term1=alias_2.term1 AND alias_2.term1=alias_3.term AND alias_3.term=alias_4.term1
UNION
SELECT DISTINCT alias_4.term1, alias_4.term2, alias_0.term2, alias_2.term2 FROM EA alias_0 , IH alias_1 , TEL alias_2 , Dean alias_3 , NM alias_4 WHERE alias_0.term1=alias_1.term1 AND alias_1.term2='c' AND alias_1.term1=alias_2.term1 AND alias_2.term1=alias_3.term AND alias_3.term=alias_4.term1
UNION
SELECT DISTINCT alias_4.term1, alias_4.term2, alias_1.term2, alias_3.term2 FROM P alias_0 , EA alias_1 , WF alias_2 , TEL alias_3 , NM alias_4 WHERE alias_0.term=alias_1.term1 AND alias_1.term1=alias_2.term1 AND alias_2.term2='c' AND alias_2.term1=alias_3.term1 AND alias_3.term1=alias_4.term1
UNION
SELECT DISTINCT alias_4.term1, alias_4.term2, alias_0.term2, alias_3.term2 FROM EA alias_0 , TN alias_1 , WF alias_2 , TEL alias_3 , NM alias_4 WHERE alias_0.term1=alias_1.term1 AND alias_1.term1=alias_2.term1 AND alias_2.term2='c' AND alias_2.term1=alias_3.term1 AND alias_3.term1=alias_4.term1
UNION
SELECT DISTINCT alias_4.term1, alias_4.term2, alias_1.term2, alias_3.term2 FROM AP alias_0 , EA alias_1 , WF alias_2 , TEL alias_3 , NM alias_4 WHERE alias_0.term=alias_1.term1 AND alias_1.term1=alias_2.term1 AND alias_2.term2='c' AND alias_2.term1=alias_3.term1 AND alias_3.term1=alias_4.term1
UNION
SELECT DISTINCT alias_4.term1, alias_4.term2, alias_0.term2, alias_2.term2 FROM EA alias_0 , WF alias_1 , TEL alias_2 , ISA alias_3 , NM alias_4 WHERE alias_0.term1=alias_1.term1 AND alias_1.term2='c' AND alias_1.term1=alias_2.term1 AND alias_2.term1=alias_3.term2 AND alias_3.term2=alias_4.term1
UNION
SELECT DISTINCT alias_4.term1, alias_4.term2, alias_0.term2, alias_3.term2 FROM EA alias_0 , FP alias_1 , IH alias_2 , TEL alias_3 , NM alias_4 WHERE alias_0.term1=alias_1.term AND alias_1.term=alias_2.term1 AND alias_2.term2='c' AND alias_2.term1=alias_3.term1 AND alias_3.term1=alias_4.term1


----------Query con simboli di vista-----------

SELECT DISTINCT VP.term AS term1,VNM.term2 AS term2,VEA.term2 AS term3,VTEL.term2 AS term4
FROM VP,VEA,VWF,VTEL,VNM
WHERE VP.term=VEA.term1 AND VWF.term1=VP.term AND VTEL.term1=VP.term AND VNM.term1=VP.term AND VWF.term2='c'

[doctorAle85]

...può servire a qualcosa l'indicizzazione delle viste?
sto leggendo qualcosa online, ma nn ci sto capendo molto!!!!

[gugoXX]

Originariamente inviato da doctorAle85

Prima ho aperto un post sulla copia di un DB da Postgre a Mysql perchè pensavo che MySQL fosse più veloce nell'eseguire query con viste...invece nn è assolutamente vero!

Gia', e' peggiore di tanti altri, quasi di tutti gli altri (a parte Access), costa di piu' e non e' standard.

Nel primo caso sebbene la query sql sia molto più lunga ci mette 200ms con Postgre, mentre per eseguire la query con le viste ci mette 3 sec.
Come cavolo è possibile? A me sembra una cosa assurda...

Una query diretta o una query su viste anaolghe ci impiega lo stesso tempo.
Un vista non e' altro che uno shortcut il cui statement viene sostituito in fase di esecuzione. Se le cose sono state fatte bene ci si ritrova ad avere una query quindi analoga all'originale senza viste, che ci mettera' lo stesso tempo.
Nel tuo caso, addiriuttura peggiorata, implica che le due esecuzioni non sono identiche, ed e' probabile che tu abbia fatto errori nella costruzione delle viste.

La strada per velocizzare non e' quindi quella delle viste normali, che una volta potevano essere usate per risparmiare il tempo di parsing (Allora prezioso), oggi e' invece ininfluente da quel punto di vista.

Dato che mi sembra di vedere ci siano dei pattern ricorrenti nella tua query, potresti p.es. spiegare come sono fatte le tabelle di partenza e cosa vorresti ottenere. Magari si riesce a trovare un modo piu' efficiente.
E poi, ma solo poi, quando si e' sicuri che non c'e' modo di migliorare, si puo' pensare ad ottimizzare.
Anche per i DB, l'ottimizzazione prematura e' il peggiore di tutti i mali.
(Corollario alla regola dell'ottimizzazione della filosofia Unix)

[doctorAle85]

Originariamente inviato da gugoXX

Nel tuo caso, addiriuttura peggiorata, implica che le due esecuzioni non sono identiche, ed e' probabile che tu abbia fatto errori nella costruzione delle viste.

Grazie della risposta...

Allora...ti posso assicurare che le viste sono create in modo assolutamente corretto!
...tant'è che le 2 query rispondono nello stesso identico modo!

Ti dico che il DB l'ho caricato da un file di backup che mi è stato fornito...e a partire da quelle tabelle ho creato queste viste.

Mi sono appena accorto che nelle tabelle del DB sono definiti degli indici...
ad es
CREATE INDEX EA_index1
ON EA
USING btree
(term1);

...e io penso che sia questo il motivo della grande differenza nel tempo d'esecuzione, dato che ogni tabella contiene da qualche migliaia a centinaia di migliaia di tuple!
...le viste invece non sono indicizzate!!!...e non riesco a trovare il modo per indicizzarle con Postgre!

Come posso fare?

[gugoXX]

Come ti ho detto prima, una vista non e' nient'altro che una stringa
"SELECT ..... FROM ... WHERE ..... etc." che viene presa e sostiuita in fase di compilazione.
Quindi poiche' viene alla fine eseguito di nuovo lo statement originale senza viste (se le cose sono state fatte correttamente), che fara' uso degli indici, se presenti.

Le viste non vengono usate per velocizzare l'esecuzione, ma per ottimizzare la scrittura del codice, per questioni di sicurezza, per minimizzare gli errori, etc.

[doctorAle85]

Originariamente inviato da gugoXX

Come ti ho detto prima, una vista non e' nient'altro che una stringa
"SELECT ..... FROM ... WHERE ..... etc." che viene presa e sostiuita in fase di compilazione.
Quindi poiche' viene alla fine eseguito di nuovo lo statement originale senza viste (se le cose sono state fatte correttamente), che fara' uso degli indici, se presenti.

Le viste non vengono usate per velocizzare l'esecuzione, ma per ottimizzare la scrittura del codice, per questioni di sicurezza, per minimizzare gli errori, etc.

Il fatto è che se per esempio faccio

select * from EA e select * from VEA , una con nomi di tabelle e una di viste, non da assolutamente problema...impiegano all'incirca lo stesso tempo!
se vado a faro query con join è un casino...

Una cosa... se da come dici, le query con le viste alla fine fanno uso degli indici esistenti, perchè online si trovano varie risorse per l'indicizzazione delle viste?

[gugoXX]

Originariamente inviato da doctorAle85

Il fatto è che se per esempio faccio

select * from EA e select * from VEA , una con nomi di tabelle e una di viste, non da assolutamente problema...impiegano all'incirca lo stesso tempo!
se vado a faro query con join è un casino...

Una cosa... se da come dici, le query con le viste alla fine fanno uso degli indici esistenti, perchè online si trovano varie risorse per l'indicizzazione delle viste?

Perche' ci sono determinati tipi di viste specifiche di alcuni database che in realta' sono materializzazioni fisiche dei dati delle viste.
Ma questi tipi di strumenti devono superare parecchi problemi, il principale e' il rinfresco dei dati della vista al cambiare dei dati sottostanti delle tabelle.

A vedere come sono scritte le tue query scommetto che potresti migliorare tantissimo l'esecuzione, senza conivolgere nient'altro che il puro SQL.

[doctorAle85]

Originariamente inviato da gugoXX

Perche' ci sono determinati tipi di viste specifiche di alcuni database che in realta' sono materializzazioni fisiche dei dati delle viste.
Ma questi tipi di strumenti devono superare parecchi problemi, il principale e' il rinfresco dei dati della vista al cambiare dei dati sottostanti delle tabelle.

A vedere come sono scritte le tue query scommetto che potresti migliorare tantissimo l'esecuzione, senza conivolgere nient'altro che il puro SQL.

La prima query più lunga non la posso modificare!
Io ho creato solo la 2a...quella con le viste!...e il tempo di esecuzione dovrei riuscire a farlo avvicinare a quello della 1a...

Mi potresti dare qualche consiglio per favore?...a parte il problema della creazione delle viste, che ripeto, è correttirrimo!

ps Il DB non subisce modifiche...quindi nn c'è il problema dell'aggiornamento dei dati delle viste...

[gugoXX]

Originariamente inviato da doctorAle85

La prima query più lunga non la posso modificare!
Io ho creato solo la 2a...quella con le viste!...e il tempo di esecuzione dovrei riuscire a farlo avvicinare a quello della 1a...

Mi potresti dare qualche consiglio per favore?...a parte il problema della creazione delle viste, che ripeto, è correttirrimo!

ps Il DB non subisce modifiche...quindi nn c'è il problema dell'aggiornamento dei dati delle viste...

MA allora fai una tabella.
Qualcosa tipo

CREATE TABLE xxxx AS SELECT
...

E ci piazzi la tua select di prima...

[doctorAle85]

Originariamente inviato da gugoXX

MA allora fai una tabella.
Qualcosa tipo

CREATE TABLE xxxx AS SELECT
...

E ci piazzi la tua select di prima...

..dici le tabelle a posto delle viste o una tabella per la 2a query che ho scritto?
...cmq sia non posso aggiungere tabelle al DB, a parte il fatto delle viste!

[gugoXX]

Originariamente inviato da doctorAle85

..dici le tabelle a posto delle viste o una tabella per la 2a query che ho scritto?
...cmq sia non posso aggiungere tabelle al DB, a parte il fatto delle viste!

Dico una sola tabella con dentro tutti e soli i dati che ti servono.
Comunque se non puoi fare tabelle, ovvero non puoi allocare spazio, non puoi neppure usare quei particolari tipi di viste con gli indici, che di fatto usano spazio (sono piu' tabelle che viste)

Ma tutte queste limitazioni da dove arrivano?
Non puoi cambiare quella query, non puoi fare tabelle...

[doctorAle85]

Originariamente inviato da gugoXX

Dico una sola tabella con dentro tutti e soli i dati che ti servono.
Comunque se non puoi fare tabelle, ovvero non puoi allocare spazio, non puoi neppure usare quei particolari tipi di viste con gli indici, che di fatto usano spazio (sono piu' tabelle che viste)

Ma tutte queste limitazioni da dove arrivano?
Non puoi cambiare quella query, non puoi fare tabelle...

Allora...ho cercato online e come hai detto te gli indici per le viste non servono a niente ...poi c'è da aggiungere anche il fatto che per es Postgre non li supporta!

Vuoi sapere da dove vengono le limitazioni...beh, allora sto modificando un algoritmo per rispondere alle query...e in teoria dovrei ottimizzarlo!
Come vedi la prima query (quella lunga) è quella dell'algoritmo iniziale, mentre la 2a sarebbe la mia!
La cosa assurda come avrai capito però è questo tempo che ci impiega... che a me continua a sembrare molto strano!

[cdimauro]

Originariamente inviato da gugoXX

Come ti ho detto prima, una vista non e' nient'altro che una stringa
"SELECT ..... FROM ... WHERE ..... etc." che viene presa e sostiuita in fase di compilazione.
Quindi poiche' viene alla fine eseguito di nuovo lo statement originale senza viste (se le cose sono state fatte correttamente), che fara' uso degli indici, se presenti.

Le viste non vengono usate per velocizzare l'esecuzione, ma per ottimizzare la scrittura del codice, per questioni di sicurezza, per minimizzare gli errori, etc.

Anche per emulare delle tabelle che originariamente erano stato realizzate senza normalizzare alcuni campi.

[gugoXX]

Originariamente inviato da doctorAle85

Allora...ho cercato online e come hai detto te gli indici per le viste non servono a niente ...poi c'è da aggiungere anche il fatto che per es Postgre non li supporta!

Vuoi sapere da dove vengono le limitazioni...beh, allora sto modificando un algoritmo per rispondere alle query...e in teoria dovrei ottimizzarlo!
Come vedi la prima query (quella lunga) è quella dell'algoritmo iniziale, mentre la 2a sarebbe la mia!
La cosa assurda come avrai capito però è questo tempo che ci impiega... che a me continua a sembrare molto strano!

Ma come fai a dire che la tua query e quella originale sono uguali?
Comunque quello che ti sto proponendo e' quello di scrivere un'altra query, una nuova query, diversa probabilmente dalla tua.
Ma per poterlo fare occorre sapere cosa vorrebbe fare la query dell'algoritmo iniziale.

Originariamente inviato da cdimauro

Anche per emulare delle tabelle che originariamente erano stato realizzate senza normalizzare alcuni campi.

Vero

[doctorAle85]

Originariamente inviato da gugoXX

Ma come fai a dire che la tua query e quella originale sono uguali?
Comunque quello che ti sto proponendo e' quello di scrivere un'altra query, una nuova query, diversa probabilmente dalla tua.
Ma per poterlo fare occorre sapere cosa vorrebbe fare la query dell'algoritmo iniziale.

non posso entrare nei dettagli perchè è un lavoro per l'università...
Non voglio fare lo spavaldo ma sono sicuro al 100% che le viste sono corrette e in più le query con i simboli di tabelle e quelle con i simboli di vista danno gli stessi risultati!