Distribuire il carico di lavoro di SQL server per una singola query

mercoledì 24 gennaio 2007 - 19.57

Elenco Threads

Stanze Forum

Aggiungi ai Preferiti

Cerca nel forum

curwen Profilo | Newbie

5 messaggi | Data Invio: mer 24 gen 2007 - 19:57

Ho un db SQL Server 2005 contenente una singola tabella di circa 50.000 record, formata (a parte una chiave primaria identity) da un unico campo xml che contiene interi documenti tradotti in xml.
Il database è essenzialmente di tipo OLAP, ma non posso ottimizzarlo con le solite procedure perché:

- i documenti XML sono troppo diversificati tra loro per tradurre la struttura in tabelle;
- gli utenti eseguono xquery su quasi tutti i nodi dei documenti conservati nel campo XML (in genere ci sono circa un centinaio di nodi per documento);
- gli utenti scelgono di volta in volta i nodi da visualizzare per i risultati delle ricerche e i nodi sui quali eseguire la ricerca;

In pratica ho le mani legate perché non ci sono query per le quali creare viste apposite.
Ho provato a creare indici XML sull'intero documento, sui nodi che reputavo più importanti, ecc., ma non ho ottenuto miglioramenti significativi delle prestazioni (una query di ricerca impiega ben 30 secondi (!!) per essere eseguita).

Devo trovare il modo di distribuire una *SINGOLA* QUERY del tipo:

SELECT campo.value('(//NODO1)', 'nvarchar(max)') AS NODO1, campo.value('(//NODO2)', 'nvarchar(max)') AS NODO2
FROM tabella
WHERE campo.value('//NODO3)', 'nvarchar(max)') LIKE '%PROVA%'
ORDER BY campo.value('(//NODO4)', 'nvarchar(max)')

su più istanze di SQL Server. E' possibile? Immagino di sì, ma non so come. Il nome e il numero di campi in ogni clausola dello statement XML cambia sempre...

Grazie per l'aiuto

nota: la query nell'esempio è semplificata, il programma utilizza la paginazione dei dati.

lbenaglia Profilo | Guru

5.625 messaggi | Data Invio: mer 24 gen 2007 - 21:14

>- i documenti XML sono troppo diversificati tra loro per tradurre
>la struttura in tabelle;
>- gli utenti eseguono xquery su quasi tutti i nodi dei documenti
>conservati nel campo XML (in genere ci sono circa un centinaio
>di nodi per documento);
>- gli utenti scelgono di volta in volta i nodi da visualizzare
>per i risultati delle ricerche e i nodi sui quali eseguire la
>ricerca;
>
>In pratica ho le mani legate perché non ci sono query per le
>quali creare viste apposite.
>Ho provato a creare indici XML sull'intero documento, sui nodi
>che reputavo più importanti, ecc., ma non ho ottenuto miglioramenti
>significativi delle prestazioni (una query di ricerca impiega
>ben 30 secondi (!!) per essere eseguita).

Ciao curwen,

SQL Server 2005 pur gestendo fragments e documenti XML rimane un RDBMS, quindi ottimizzato per elaborare dati in forma relazionale.
Con questo non voglio dire che non sia in grado di gestire dati XML (per loro natura gerarchici), ma l'uso che ne stai facendo è molto forzato.
Non parli della dimensione dei documenti, ma posso presupporre che non siano proprio "piccoli".
Se con le indicizzazioni non hai ottenuto benefici, secondo me o utilizzi un database XML nativo (non chiedermi quale dato che sono ignorante in materia

) oppure cambi architettura, evitando di memorizzare questi documenti nel database.

>Devo trovare il modo di distribuire una *SINGOLA* QUERY del tipo:
>
>SELECT campo.value('(//NODO1)', 'nvarchar(max)') AS NODO1, campo.value('(//NODO2)',
>'nvarchar(max)') AS NODO2
>FROM tabella
>WHERE campo.value('//NODO3)', 'nvarchar(max)') LIKE '%PROVA%'
>ORDER BY campo.value('(//NODO4)', 'nvarchar(max)')
>
>su più istanze di SQL Server. E' possibile? Immagino di sì, ma
>non so come. Il nome e il numero di campi in ogni clausola dello
>statement XML cambia sempre...
Con la versione 2000 SQL Server ha introdotto le distributed partitioned views e la versione 2005 le partitioned tables.
Onestamente non credo che queste soluzioni possano essere utilizzate con il data type XML, ma ti invito ad approfondirle sui Books Online.

Una curiosità: per quale motivo casti tutti i nodi a 'nvarchar(max)'?
- Hai la necessità di memorizzare informazioni in più alfabeti (motivo che determina l'utilizzo di un data type unicode)?
- Le informazioni riferite ad un singolo nodo solo SOLO stringhe?
- Le stesse informazioni nel caso siano stringhe, superano gli 8060 bytes?

Se il casting a 'nvarchar(max)' è ingiustificato, prova ad utilizzare i data type più indicati a gestire quella singola informazione, non è escluso che potrai ottenere benefici a livello prestazionale.

Inoltre se i documenti inseriti si basano sul medesimo schema, è oppurtuno specificarlo esplicitamente nella definizione della colonna XML in modo da permettere a SQL Server di scomporre lo stream binario in sottotabelle relazionali aventi data type specifici e stai certo che questo comporterà una notevole diminuzione dei tempi di esecuzione della query.

Infine per eseguire filtri sui dati, ti suggerisco di ricorrere al metodo exist() che è più efficiente rispetto al metodo value(), evitando A TUTTI I COSTI di utilizzare pattern di ricerca che iniziano con la wildcard "%" che porta SQL Server ad eseguire un table scan o un index scan nel caso di indice clustered.

>Grazie per l'aiuto
Prego.

Ciao!
--
Lorenzo Benaglia
Microsoft MVP - SQL Server
http://blogs.dotnethell.it/lorenzo/
http://italy.mvps.org

curwen Profilo | Newbie

5 messaggi | Data Invio: mer 24 gen 2007 - 21:38

Ciao,

grazie per la risposta. In effetti so di stare forzando SQL Server; vorrei però cercare di vitare di cambiare database, visto che questo mi costringerebbe a riscrivere parte dell'applicazione che lo sfrutta.
Preferisco, se possibile, insistere sull'hardware, consapevole del fatto di non usarlo al meglio.

La conversione a nvarchar(max) è data dal fatto che non conosco a priori il tipo di dato dei singoli nodi, né la lunghezza dei testi

.

Ma è possibile utilizzare exist al posto di value per eseguire una ricerca case insensitive simile a quella di LIKE?

lbenaglia Profilo | Guru

5.625 messaggi | Data Invio: mer 24 gen 2007 - 21:44

>Preferisco, se possibile, insistere sull'hardware, consapevole
>del fatto di non usarlo al meglio.
>
>La conversione a nvarchar(max) è data dal fatto che non conosco
>a priori il tipo di dato dei singoli nodi, né la lunghezza dei
>testi

.

Mi spiace, se non puoi nemmeno applicare uno schema specifico data la natura eterogenea dei documenti temo che neanche un Cray potrà permetterti di ottenere prestazioni soddisfacenti

Ciao!
--
Lorenzo Benaglia
Microsoft MVP - SQL Server
http://blogs.dotnethell.it/lorenzo/
http://italy.mvps.org

Torna su

Stanze Forum

Elenco Threads

Partecipa anche tu! Registrati!

Hai bisogno di aiuto ?
Perchè non ti registri subito?

Dopo esserti registrato potrai chiedere
aiuto sul nostro Forum oppure aiutare gli altri

Consulta le Stanze disponibili.

Registrati ora !