Ottimizzare query / memoria / CPU

[MatteoG]

Ciao mi aiutereste a migliorare una query:
HW:
Proc: 1 Xeon Prestonia 2,4 Ghz L2 512K
Ram: 2GB
HD: 4X146GB RAID 5
SW:
Windows 2000 SP4
MSSQL 2000 SP3
DataBase:
Dimensione 70 MB used + 64 Free

Tabella: Tab
Numero campi: 82 (68 varchar(in media da 100 caratteri) + 24 int/datetime/)
Numero indici: 1 sul campo identificatore di tipo intero
Numero righe: 68000 circa

Problema:
Eseguire una ricerca completa su tutti i campi della tabella Tab di stringa
di ricerca
Ovviamente per i campi varchar uso il like per i campi numerici uso l'uguale.
Non è una stored procedure ma una semplice query da programma.
Query:
Select * from Tab
where Col1 Like '%stringa%' OR Col2 like '%stringa%' OR Col3 = stringa ...
Col82 like '%stringa%'

Quando si esegue la query il serve utilizza la CPU al 100% per 10-15 secondi..

Cosa si può fare per migliorare la query?

PS: Io credo non serva più RAM ma un doppio processore! o un sistema nuovo!
cosa ne dite??

Grazie dell'attenzione
Saluti Matteo

[Andrea Benedetti]

Salve MatteoG,

Post by MatteoG
Proc: 1 Xeon Prestonia 2,4 Ghz L2 512K
Ram: 2GB
HD: 4X146GB RAID 5
Windows 2000 SP4
MSSQL 2000 SP3
Dimensione 70 MB used + 64 Free
Tabella: Tab
Numero campi: 82 (68 varchar(in media da 100 caratteri) + 24
int/datetime/)
Numero indici: 1 sul campo identificatore di tipo intero
Numero righe: 68000 circa
Eseguire una ricerca completa su tutti i campi della tabella Tab di stringa
di ricerca
Ovviamente per i campi varchar uso il like per i campi numerici uso l'uguale.
Non è una stored procedure ma una semplice query da programma.
Select * from Tab
where Col1 Like '%stringa%' OR Col2 like '%stringa%' OR Col3 = stringa ...
Col82 like '%stringa%'
Quando si esegue la query il serve utilizza la CPU al 100% per 10-15 secondi..
Cosa si può fare per migliorare la query?

Per come è strutturata la tabella direi ben poco... ;-)
Hai 68 campi su cui esegui una like (!) con il carattere jolly e 24 su cui
esegui una verifica di uguaglianza...

SQL Server sarà fatto anche bene... ma i miracoli non li sa ancora fare...
;-)

Post by MatteoG
PS: Io credo non serva più RAM ma un doppio processore! o un sistema nuovo!
cosa ne dite??

La query deve essere necessariamente fatta così?
O meglio: non conoscendo lo scenario su cui devi lavorare è difficile
aiutarti meglio.

Hai dato un'occhiata al piano di esecuzione della query per vedere come
l'optimizeri la risolve?

Post by MatteoG
Grazie dell'attenzione
Saluti Matteo

Ciao!

[Davide Mauri]

Post by MatteoG
Tabella: Tab
Numero campi: 82 (68 varchar(in media da 100 caratteri) + 24
int/datetime/)
Numero indici: 1 sul campo identificatore di tipo intero

dalla query che riporti direi che questo indici è inutile

Post by MatteoG
Numero righe: 68000 circa

sono poche, non dovresti avere problemi

Post by MatteoG
Eseguire una ricerca completa su tutti i campi della tabella Tab di stringa
di ricerca

Ti consiglio per questo genere di ricerche di utilizzare il Full-Text

Post by MatteoG
Ovviamente per i campi varchar uso il like per i campi numerici uso l'uguale.
Non è una stored procedure ma una semplice query da programma.
Select * from Tab
where Col1 Like '%stringa%' OR Col2 like '%stringa%' OR Col3 = stringa ...
Col82 like '%stringa%'
Quando si esegue la query il serve utilizza la CPU al 100% per 10-15 secondi..

Ci credo :-)

Post by MatteoG
Cosa si può fare per migliorare la query?

Riscrivere la query :-)
Scherzi a parte una query scritta cosi genererà sempre un table scan, e
quindi c'è poco da ottimizzare. L'idea da seguire - dalle informazioni che
ci dai, è questa:

1) Utilizzare il full-text search per tutte le colonne testuali sulla quale
attualmente fai ricerche del tipo: like "%qualcosa%". Fatto ciò usa gli
operatori FREETEXT o CONTAINS al posto della clausola LIKE
2) cerca di capire se c'è un campo che è SEMPRE presente nella tua query. Se
si è possibile pensare di mettere un indice sullo stesso
3) da quello che vedo mi sa che il db dovrebbe essere un pò ridisegnato...

Post by MatteoG
PS: Io credo non serva più RAM ma un doppio processore! o un sistema nuovo!
cosa ne dite??

Che per un database cosi piccolo un server del genere è gia
sovradimensionato :-)

Post by MatteoG
Grazie dell'attenzione
Saluti Matteo

--
Davide Mauri
Microsoft MVP - SQL Server
MCP, MCAD, MCDBA, MCT
http://www.davidemauri.it
http://blogs.ugidotnet.org/netTools
http://weblogs.sqlteam.com/dmauri/

UGISS: User Group Italiano Sql Server
http://www.ugiss.org

[Lorenzo Benaglia]

Post by MatteoG
Non è una stored procedure ma una semplice query da
programma.
Select * from Tab
where Col1 Like '%stringa%' OR Col2 like '%stringa%' OR Col3 =
stringa ... Col82 like '%stringa%'

Ciao Matteo,

il fatto che non sia una stored procedure costituisce già un problema dato
che ogni esecuzione genererà un nuovo execution plan, ma l'errore più grosso
è costituito dal criterio di ricerca: una LIKE basata sul pattern '%...%'
costituisce un PESSIMO search argument dato che l'unico modo di risolvere la
query consiste nell'effettuare una scansione dell'intera tabella (o
dell'indice clustered se presente). Le wildcard "%" e "_" non andrebbero MAI
utilizzate all'inizio del pattern appunto per questo motivo. Un search
argument del tipo LIKE '...%' invece costituisce un buon criterio di ricerca
che può sftuttare gli indici definiti sulla colonna in esame.

Un altro problema sono tutte quelle OR che appesantiscono di molto
l'esecuzione della query.
Nel tuo caso probabilmente otterresti prestazioni molto superiori
utilizzando un indice fulltext.
Sui Books Online troverai tutte le informazioni che ti servono per
implementare questo tipo di indici. Inizia a leggere il seguente paragrafo e
tutti quelli elencati alla fine:

"Full-Text Query Architecture"
http://msdn.microsoft.com/library/en-us/architec/8_ar_sa2_0ehx.asp

Post by MatteoG
Grazie dell'attenzione

Prego.

Ciao!

[MatteoG]

Ciao e grazie delle risposte.

Il database è un semplice gestore di una libreria di disegni che memorizza
le caratteristiche del disegno. E' un programma già fatto che abbiamo in
ditta e non credo di riuscire a modificarlo.
La query è stata intercettata con il query analizer.

Ho provato ad implementare il full text search su una tabella creata apposta
ma mi ha dato molte perplessità! Ad esempio le due ricerche seguenti non
portano allo stesso risultato..
Query 1:
Select * from Tab where col1 like '%12%' OR Col2 like '%12%'

Query 2:
Select * from Tab freetext(*, '12')

Mentre la query 1 fa la ricerca completa, la query 2 ritorna solo le righe
con una colonna contenente la parola '12' e non ricerca le sottostringhe!
(analogamente con content(*,'12')!
Mi sembra di aver capito che la ricerca full text si adatta alla ricerca di
parole (e fa anche il controllo sulla sintassi e sul vocabolario) mentre a me
serve una ricerca di sottostringhe!

Per curiosità:
- Per fare una ricerca tra 82 x 68000 righe (che aumentano di 1000 righe al
mese circa)
sono circa 5.603.000 confronti divisio per 10 secondi corrispondono a
560.000 confronti al secondo.

Se dovessi prendere dell'HW adeguato (10.000.000 di confronti al secondo)
che sia abbastanza potente per 3 anni di cosa dovrei disporre??

Grazie ancora dell'attenzione!
Ciao Matteo

[Luca Bianchi]

Post by MatteoG
Mi sembra di aver capito che la ricerca full text si adatta alla ricerca di
parole (e fa anche il controllo sulla sintassi e sul vocabolario) mentre a me
serve una ricerca di sottostringhe!

Esatto. Se questa è la tua esigenza l'indicizzazione FT non ti può aiutare e
devi rassegnarti affinchè le tue richieste siano soddisfatte da un table
scan.

Post by MatteoG
- Per fare una ricerca tra 82 x 68000 righe (che aumentano di 1000 righe al
mese circa)
sono circa 5.603.000 confronti divisio per 10 secondi corrispondono a
560.000 confronti al secondo.

La media è una cosa, il modo in cui SQL Server decide di risolvere una query
non è detto che mantenga un criterio di "proporzionalità".

Post by MatteoG
Se dovessi prendere dell'HW adeguato (10.000.000 di confronti al secondo)
che sia abbastanza potente per 3 anni di cosa dovrei disporre??

Il tuo ragionamento è fuori strada. La tabella di cui parli è un "mostrum"
in termini progettuali ma nonostante ciò il suo contenuto può essere stipato
interamente in RAM; con quel quantitativo di RAM e supponendo che la tua
tabella occupi una pagina intera (8k) per ciascun record (questo è il
"mostrum progettuale di cui parlavo" ed il fatto che 1 pagina = 1 record è
verosimile) significa che in RAM possono essere stipate fino a 250000 pagine
dati. La tua tabella ne contiene (oggi) 68000 ed ecco il motivo per cui dico
che la tabella, nonostante tutto, può essere contenuta tutta in RAM. E' in
RAM, infatti, che SQL Server accede alle pagine dati; le carica dal disco
alla prima necessità (a partire dall'avvio del servizio) e rimangono in RAM
fino a che questa non si esaurisce. Un processo di sistema periodicamente
provvede a rimuovere dalla RAM le pagine più vecchie per far posto alle
nuove MA SOLO SE NUOVE PAGINE DEVONO ESSERE CARICATE O SE IL SO RICHIEDE RAM
per altri processi. Pertanto se la query è eseguita frequentemente quelle
68000 pagine non verranno mai rimosse dalla memoria fino all'esaurimento
della stessa. Ovviamente dal momento che non fai menzione del complesso di
attività che vi sono sui dati non posso conoscere nulla del restante carico
del server e se ci sono altre 20 tabelle simili ecco che sarebbero pochi
anche 8 Gb di RAM.
Pertanto le uniche cose che puoi fare è dedicare un indice (preferibilmente
clustered accertandoti preventivamente che l'indice sulla pk sia non
clustered) ad uno di quei campi più efficienti, ovvero i campi INT/DATETIME.
Se le query oltre alla ricerca di sottostringhe (che come ti hanno detto un
search argument del tipo '%stringa%' non può essere in alcun modo
ottimizzato) utilizzano anche questi campi puoi ridurre la quantità di IO
(ovvero il numero delle pagine che vengono accedute in RAM) e, di
conseguenza, ridurre proporzionalmente i tempi di esecuzione. Qualunque
altro indice diverso dal clustered, in una tabella simile, difficilmente
verrà utilizzato se non per una ricerca "precisa" che restituisca pochissimi
record. Pertanto individua con la massima cura il campo più frequentemente
utilizzato dalle query tra quelli INT/DATETIME e crea su di esso un indice
clustered.
E' tutto quello che puoi fare se non puoi mettere mano alla struttura dati.

Post by MatteoG
Grazie ancora dell'attenzione!
Ciao Matteo

Bye

Luca Bianchi
Microsoft MVP - SQL Server