[SQL] migliorare il risultato di una query con molte join

[TorpedoBlu]

ciao ragazzi, per un progetto che deve effettuare lo storico modifiche sul mio db ho la necessità di effettuare una Select enorme (in una stored procedure per migliorare le performance) su molteplici tabelle (molte delle quali referenziate tra di loro) attraverso una decina di left outher join.

il risultato sono tantissime righe... ovviamente la permutazione dei dati delle tabelle... il risultato devo metterlo in un xml (ma quello lo faccio chiaramente lato applicazione)

ad esempio se ho l'anagrafe e 3 residenze avrò 3 righe (1 nome x 3 residenze)

nome1, cognome1, residenza1
nome1, cognome1, residenza2
nome1, cognome1, residenza3

e l'xml associato sarebbe

<nome>nome1</nome>
<cognome>cognome1</cognome>
<residenze>
 <residenza>residenza1</residenza>
 <residenza>residenza2</residenza>
 <residenza>residenza3</residenza>
</residenze>

capite che però se ho gia due recapiti telefonici, le righe del db diventano da 3 a 6 (1 nome x 3 residenze x 2 recapiti)

nome1, cognome1, residenza1, telefono1
nome1, cognome1, residenza2, telefono1
nome1, cognome1, residenza3, telefono1
nome1, cognome1, residenza1, telefono2
nome1, cognome1, residenza2, telefono2
nome1, cognome1, residenza3, telefono2

e per produrre l'xml associato gia è + complicato

<nome>nome1</nome>
<cognome>cognome1</cognome>
<residenze>
 <residenza>residenza1</residenza>
 <residenza>residenza2</residenza>
 <residenza>residenza3</residenza>
</residenze>
<recapiti>
 <telefono>telefono1<telefono>
 <telefono>telefono1<telefono>
</recapiti>

Questi sono solo esempi di fantasia per far comprendere il problema....

dovendo fare join su una ventina di tabelle ognuna con un numero medio di 40 campi il numero di righe di output è davvero spropositato con moltissimi dati ripetuti o nulli e il problema diventa scorrere tutte le righe per costruire tutti i tag innestati (anche 4 o 5 generazioni di nodi).


La struttura dell'XML l'ho fatta e ho anche un insieme di classi per costruire i nodi (dando in input la sequenza di stringhe rappresentanti i campi della tabella associata) ma dovermi scorrere migliaia di tuple cercando di fare ordine (del tipo questo nome l'ho gia inserito, questo telefono appartiene a questo nome, questo nome e questo telefono l'ho gia inserito per questo nodo ecc...)


qualcuno ha esperienze in merito?


L'applicazione è una web application Java (non deve usare EJB) su IBM DB2 8.2 (tramite Jt400)

[banryu79]

Non puoi usare la DISTINCT nelle SELECT?

[TorpedoBlu]

Originariamente inviato da banryu79

Non puoi usare la DISTINCT nelle SELECT?

certo, ma la clausola distinct toglie le linee duplicate e questo gia lo faccio, mentre ogni linea da me rappresenta una composizione di valori diversi... magari diversi per una sola colonna su 500...

io vorrei cercare di capire se è possibile tirare fuori i valori gia organizzati senza riempire per forza tutta la matrice rappresentativa:

nome1 cognome 1 .. .. .. .. .. .. .. telefono 1
/ / telefono2

al posto di
nome1 cognome 1 .. .. .. .. .. .. .. telefono 1
nome1 cognome 1 .. .. .. .. .. .. .. telefono 2

[banryu79]

scusa ogni tanto sparo qualche vaccata su argomenti che non conosco a sufficienza

Purtroppo il materia SQL sono nubbissimo (e il mio posto poco sopra lo dimostra) ergo non ti so aiutare nel problema in questione, ciao

[TorpedoBlu]

qualcuno mi suggerisce di usare result set tipizzati.. ma non so come si implementano in db2 e non trovo documentazione

[gugoXX]

Originariamente inviato da TorpedoBlu

qualcuno mi suggerisce di usare result set tipizzati.. ma non so come si implementano in db2 e non trovo documentazione

Non c'entra niente DB2, e' al livello dell'output, dell'XML.
In pratica ti stanno proponendo di non fare nessuna JOIN.
E di avere una sezione del file XML per ciascuna tabella.
Mi sembra anche sensato, per un problema tipo "Estrarre lo storico dei dati del Database"

[TorpedoBlu]

mi è stato consigliato di usare record tipizzati:

nome1, cognome1, tipo:RESIDENZA, residenza1, tel:null
nome1, cognome1, tipo:RESIDENZA, residenza2, tel:null
nome1, cognome1, tipo:RESIDENZA, residenza3, tel:null
nome1, cognome1, tipo:TELEFONO, residenza:null, telefono1
nome1, cognome1, tipo:TELEFONO, residenza:null, telefono2

ma non capisco cosa intendono.. qualcuno ha esperienza in merito? ho cercato su google ma non trovo nulla...

[gugoXX]

Tipo questo:

<xml ....>
<Persone>
  <Persona>
    <ID>id1</ID>
    <nome>nome1</nome>
    <cognome>cognome1</cognome>
  </Persona>
  <Persona>
    <ID>id2</ID>
    <nome>nome2</nome>
    <cognome>cognome2</cognome>
  </Persona>
</Persone>
<Inidirizzi>
  <Indirizzo>
    <ID>id1</ID>
    <Via>via di sotto</Via>
  </Indirizzo>
  <Indirizzo>
    <ID>id1</ID>
    <Via>via di sopra</Via>
  </Indirizzo>
  <Indirizzo>
    <ID>id2</ID>
    <Via>via di lato</Via>
  </Indirizzo>
</Indirizzi>
</XML>

Ciascuna tabella ha il suo tipo di record, e viene memorizzata indipendentemente dalle altre.

[TorpedoBlu]

Originariamente inviato da gugoXX

Tipo questo:

<xml ....>
<Persone>
  <Persona>
    <ID>id1</ID>
    <nome>nome1</nome>
    <cognome>cognome1</cognome>
  </Persona>
  <Persona>
    <ID>id2</ID>
    <nome>nome2</nome>
    <cognome>cognome2</cognome>
  </Persona>
</Persone>
<Inidirizzi>
  <Indirizzo>
    <ID>id1</ID>
    <Via>via di sotto</Via>
  </Indirizzo>
  <Indirizzo>
    <ID>id1</ID>
    <Via>via di sopra</Via>
  </Indirizzo>
  <Indirizzo>
    <ID>id2</ID>
    <Via>via di lato</Via>
  </Indirizzo>
</Indirizzi>
</XML>

Ciascuna tabella ha il suo tipo di record, e viene memorizzata indipendentemente dalle altre.

grazie, questo però è quello che voglio fare.. come faccio a farlo con SQL? il mio problema di come gestire il risultato delle query rimane, il numero delle tuple prodotte.

Esistono queste semistrutture in sql? (qualcosa di simile alle strutture C e agli oggetti in java penso)

[gugoXX]

Originariamente inviato da TorpedoBlu

grazie, questo però è quello che voglio fare.. come faccio a farlo con SQL? il mio problema di come gestire il risultato delle query rimane, il numero delle tuple prodotte.

Esistono queste semistrutture in sql? (qualcosa di simile alle strutture C e agli oggetti in java penso)

Non ho capito.
QUESTO nella frase cos'e'?
Il tuo risultato o quello che ho postato io poco fa?

In entrambi i casi non serve scomodare alcuna "semistrittura" dell'SQL, che peraltro e' sempre specifica per ciascun motore.
Oracle ha la sua implementazione, etc.
Ma tanto non cambierebbe la complessita' del tuo problema.
Si risolve tutto facendo una (io dico tante) query, e poi con postprocess per realizzare l'XML

[TorpedoBlu]

Originariamente inviato da gugoXX

Non ho capito.
QUESTO nella frase cos'e'?
Il tuo risultato o quello che ho postato io poco fa?

In entrambi i casi non serve scomodare alcuna "semistrittura" dell'SQL, che peraltro e' sempre specifica per ciascun motore.
Oracle ha la sua implementazione, etc.
Ma tanto non cambierebbe la complessita' del tuo problema.
Si risolve tutto facendo una (io dico tante) query, e poi con postprocess per realizzare l'XML

conosci la semantica per realizzare l'xml?

[gugoXX]

Ci sono tanti modi di realizzare un XML, ma non lo farei sicuramente usando i linguaggi imperativi dei database come il TSQL o il PL/SQL se e' questo che stai chiedendo.
Dal database estrarrei i dati che mi servono, e con un linguaggio tipo C#, Python o altri realizzerei la presentazione XML.

Comunque la complessita' realizzativa sarebbe la stessa anche in TSQL o PL/SQL, se non che hai molti meno strumenti a disposizione e la la leggibilita' del codice che ne segue e' davvero ridotta.

Ma prima di tutto, prima di decidere come scrivere l'XML, devi veramente decidere COSA scrivere. Ovvero il formato del tuo file XML.
Quello che sto dicendo dall'inizio e' che il formato che vorresti ottenere nel primo post e' dispendioso per te e inutile per chi dovra' poi leggerlo.

[TorpedoBlu]

Originariamente inviato da gugoXX

Ci sono tanti modi di realizzare un XML, ma non lo farei sicuramente usando i linguaggi imperativi dei database come il TSQL o il PL/SQL se e' questo che stai chiedendo.
Dal database estrarrei i dati che mi servono, e con un linguaggio tipo C#, Python o altri realizzerei la presentazione XML.

Comunque la complessita' realizzativa sarebbe la stessa anche in TSQL o PL/SQL, se non che hai molti meno strumenti a disposizione e la la leggibilita' del codice che ne segue e' davvero ridotta.

Ma prima di tutto, prima di decidere come scrivere l'XML, devi veramente decidere COSA scrivere. Ovvero il formato del tuo file XML.
Quello che sto dicendo dall'inizio e' che il formato che vorresti ottenere nel primo post e' dispendioso per te e inutile per chi dovra' poi leggerlo.

non lo leggerà nessuno, andrà ad essere processato in pagine web con xslt

il formato è fissato dal cliente

[gugoXX]

Originariamente inviato da TorpedoBlu

non lo leggerà nessuno, andrà ad essere processato in pagine web con xslt

il formato è fissato dal cliente

Quel formato e' una cosa che relazionalmente e' decisamente errata.
Se una persona avesse 3 indirizzi, 4 numeri telefonici, 3 conti bancari e 3 animali preferiti

invece che 3+4+3+3=13 "informazioni" quell XML avrebbe 3x4x3x3=108 record, con tutte le permutazioni possibili. A cosa serve?

[TorpedoBlu]

Originariamente inviato da gugoXX

Quel formato e' una cosa che relazionalmente e' decisamente errata.
Se una persona avesse 3 indirizzi, 4 numeri telefonici, 3 conti bancari e 3 animali preferiti

invece che 3+4+3+3=13 "informazioni" quell XML avrebbe 3x4x3x3=108 record, con tutte le permutazioni possibili. A cosa serve?

infatti da quel numero di tuple impressionante io mi devo estrarre un xml come dici tu, è quello il problema.

[TorpedoBlu]

alla fine mi è stato commissionato di concatenare tutti i campi delle query in stringhe, e delimitare le singole query con opportuni delimitatori..

in questo modo faccio varie store procedure singole che vengono richiamate da un'unica store procedure che concatena tutti i risultati fornendo alla fine un'unica stringa.

Non discuto sulla bontà della soluzione poichè non l'ho scelta io.

Ho un problema, se un solo campo è null la stringa che rappresenta il risultato della concatenazione è null!!!

come faccio a risolvere? (senza riempire la query di IF poichè i campi sono tantissimi)

esempio:

CREATE PROCEDURE PROVA_ASSEGNI(IN parametro1 INTEGER, IN parametro2 VARCHAR(20), OUT TAG_ASSEGNI VARCHAR(20000))
LANGUAGE SQL

BEGIN 

	DECLARE ASSEGNI VARCHAR ( 20000 ) ;
	SET ASSEGNI = '';

	FOR 
		--- SELECT QUERY ASSOCIATA AL CICLO FOR ---
		ASSEGNI_ROW AS 	
			SELECT 	
				campo1|| '|' ||
				campo2|| '|' ||
				campo3 as ASSEGNO
			FROM 	tabella.ASSEGNI
			WHERE 	.... 
		
		--- OGNI TUPLA è CIRCONDATA DAL SUO TAG
		DO SET ASSEGNI=CONCAT(ASSEGNI, ('<Assegno>'||ASSEGNI_ROW.ASSEGNO||'</Assegno>'));	
	END FOR;
	
	-- RISULTATO DELLA SP ---
	SET TAG_ASSEGNI = ('<Assegni>'||ASSEGNI||'</Assegni>');

END

che mi da una stringa tipo :
<assegni><assegno>campo1|campo2|campo3</assegno><assegno>campo1|campo2|campo3</assegno></assegni>

ma se un solo campo è null... la stringa risultante è null....


evidentemente la concatenazione non posso farla direttamente nella select.....

come si fa a scorrere i campi della query iterativamente e controllare che non siano null?

[TorpedoBlu]

certo che è proprio strano che non si possano scorrere le colonne
risultanti di una select...pensavo che finissero comunque in un array
(o qualcosa di simile) la cosa mi sorprende.... e mi sverva visto che
devo costruire una procedura ad hoc per ogni tabella.....

[banryu79]

Ciao,
senti ma prima di concatenare le stringhe risultanti dalla Select non si può andare a iterare il Result Set della query per gestire il caso in cui un campo stringa sia nullo?

[TorpedoBlu]

Originariamente inviato da banryu79

Ciao,
senti ma prima di concatenare le stringhe risultanti dalla Select non si può andare a iterare il Result Set della query per gestire il caso in cui un campo stringa sia nullo?

è esattamente quello che vorrei fare e che sto chiedendo, se sai come si fa...

[banryu79]

So farlo ma nel modo classico: ovvero lato applicativo, tramite query passate al DBMS del caso da codice sorgente, quindi con metodi di oggetti che tornano come risultato un oggetto ResultSet iterabile.

Da SQL via Stored Procedure (quindi, se non erro, da dentro il contesto del DBMS) no, non l'ho mai fatto.