Newsgroup: it.comp.software.database
Titolo: Tempi di esecuzione query sql2000

Ciao a tutti,
ho un quesito un po particolare, avrei bisogno di sapere, anche
orientativamente, se i tempi di esecuzione di una query come quella che
illustro dopo sono accettabili.
ho una tabella utenti con id, cognome, nome con 4.000.000 (4 milioni) di
record
"select * from utenti where cognome like '%partecognome%' and nome like
'%partenome%'"
su un athlon 1800+ con 256 mb ddr (windows 2000 prof. + sql server 2000
personal ed.) impiega ~14 sec restituendomi 50.000 record.
Naturalmente Cognome e Nome sono indicizzati. Il wizard per la creazione di
indici mi dice che è tutto ok.
A me come tempo sembra un po tanto, non ho trovato da nessuna parte come
ottimizzare le ricerche. Voi che ne dite?
grazie a tutti quelli che mi sapranno dare qualche dritta.
Stefano

 ottimizzare le ricerche.
Allora non hai cercato.
Vedi su 
www.soft-land.org/faq/optqry.html
Davide

 wrote in message
news:abbfr5$vtf$1...@lacerta.tiscalinet.it...

Stefano,
l'analisi delle prestazioni delle query, e la corretta selezione degli
indici, sono delle attivita' tutt'altro che banali.
Bisogna conoscere bene i meccanismi interni del prodotto (che sono
comunque completamente documentati), come sono implementati fisicamente
sia gli indici che il database, quali sono le strategie ha disposizione
del Query Processor (differenti algoritmi di join, di ordinamento, di
ricerca,
di fusione degli indici), quali sono le riflessioni che fa' il Query
Processor
per scegliere tra le varie strategie (selettivita' e densita' degli indici).
L'obiettivo principale e' quello di fornire i risultati facendo il minor
numero di operazioni di I/O (sia fisiche, sui dischi, sia logiche, nella
cache dei dati).
Capiti questi meccanismi si deve comunque trovare il break-even point
tra indicizzare tutto (rallentando le prestazioni di aggiornamento
ed aumentando lo spazio occupato dai dati) e non indicizzare niente
(rallentando le prestazioni in fase di selezione).
Esistono libri dedicati solo alle strategie per la definizione degli
indici.
Di seguito uno script con alcuni esempi, non pretendo assolutamente di
essere esaustivo, solo di darti un punto di partenza per capire come
analizzare piu' a fondo il problema.
--CUT HERE--
USE pubs
GO
SET SHOWPLAN_TEXT OFF
GO
-- creiamo una copia della tabella authors e duplichiamo un po' i dati
SELECT *
INTO Authors2
FROM dbo.Authors
GO
-- prima di tutto proviamo con una selezione semplice
SET SHOWPLAN_TEXT ON
GO
SELECT *
FROM dbo.Authors2
WHERE au_lname LIKE 'm%'
GO
/*
|--Table Scan(OBJECT:([pubs].[dbo].[Authors2]),
WHERE:(like([Authors2].[au_lname], 'm%', NULL)))
come si puo' vedere, non esistendo nessun indice, l'unica strategia per
risolvere la query
e' quella di fare un table scan, di leggere cioe' tutte le pagine contenenti
tutte le righe
verificando la condizione
*/
-- Creiamo ora un indice sulla colonna
SET SHOWPLAN_TEXT OFF
GO
CREATE INDEX ix_authors2_lname ON dbo.authors2 (au_lname)
GO
SET SHOWPLAN_TEXT ON
GO
SELECT *
FROM dbo.Authors2
WHERE au_lname LIKE 'm%'
GO
/*
  |--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([pubs].[dbo].[Authors2]))
       |--Index Seek(OBJECT:([pubs].[dbo].[Authors2].[ix_authors2_lname]),
SEEK:([Authors2].[au_lname] >= 'Lþ' AND [Authors2].[au_lname] < 'N'),
WHERE:(like([Authors2].[au_lname], 'm%', NULL)) ORDERED FORWARD)
in questo caso il Query Processor e' in grado di utilizzare l'indice che
abbiamo creato. L'indice viene attraversato scendendo dalla radice e viene
localizzata velocemente la pagina che contiene la stringa (tieni conto
che gli indici sono molo piatti, hanno cioe' pochi livelli) da quel punto
in poi vengono letti tutti i valori fino alla prima stringa che contiene
un valora che non soddisfa piu' la condizione (se noti l'output del
piano di esecuzione la LIKE 'm%' viene internamente trasformata nella
ricerca di un range >= 'Lþ' AND < 'N').
Come una inefficienza rimane il fatto che avendo selezionato
tutti i campi (*), deve recuparli facendo un operazione di Bookmark Lookup,
una lettura di una o piu' pagine che contengono i dati
*/
SELECT au_lname
FROM dbo.Authors2
WHERE au_lname LIKE 'm%'
GO
/*
  |--Index Seek(OBJECT:([pubs].[dbo].[Authors2].[ix_authors2_lname]),
SEEK:([Authors2].[au_lname] >= 'Lþ' AND [Authors2].[au_lname] < 'N'),
WHERE:(like([Authors2].[au_lname], 'm%', NULL)) ORDERED FORWARD)
selezionando solo il campo sul quale facciamo la ricerca, l'operazione di
Bookmark
Lookup sparisce perche' l'indice contiene gia' tutte le informazioni per
soddisfare
la richiesta sia per quanto riguarda la ricerca, sia per quanto riguarda le
informazioni da restituire (si dice che l'indice e' un "covering index")
*/
SELECT au_lname
FROM dbo.Authors2
WHERE au_lname LIKE '%m%'
GO
/*
  |--Index Scan(OBJECT:([pubs].[dbo].[Authors2].[ix_authors2_lname]),
WHERE:(like([Authors2].[au_lname], '%m%', NULL)))
utilizzando il carattere jolly in testa alla stringa da ricercare risulta in
una operazione di
Index Scan che significa leggere TUTTI i valori dell'indice e verificare la
condizione.
Nell'operazione precedente, di index seek, non vengono letti tutti i valori
dell'indice.
*/
-- vediamo qualcosa in piu' aggiungendo l'altra clausola
SELECT au_lname, au_fname
FROM dbo.Authors2
WHERE au_lname LIKE 'm%' and au_fname LIKE 's%'
GO
/*
  |--Filter(WHERE:(like([Authors2].[au_fname], 's%', NULL)))
       |--Bookmark Lookup(BOOKMARK:([Bmk1000]),
OBJECT:([pubs].[dbo].[Authors2]))
            |--Index
Seek(OBJECT:([pubs].[dbo].[Authors2].[ix_authors2_lname]),
SEEK:([Authors2].[au_lname] >= 'Lþ' AND [Authors2].[au_lname] < 'N'),
WHERE:(like([Authors2].[au_lname], 'm%', NULL)) ORDERED FORWARD)
come si puo' notare viene utilizzato l'indice per la prima clausola (index
seek),
poi vengono recuperate le righe con l'operazione di Bookmark Lookup
(serve il campo au_fname) ed infine viene fatto un filtro anche su au_fname
*/
-- inseriamo qualche dato in piu'
SET SHOWPLAN_TEXT OFF
GO
INSERT dbo.Authors2 SELECT * FROM dbo.Authors2
INSERT dbo.Authors2 SELECT * FROM dbo.Authors2
INSERT dbo.Authors2 SELECT * FROM dbo.Authors2
INSERT dbo.Authors2 SELECT * FROM dbo.Authors2
INSERT dbo.Authors2 SELECT * FROM dbo.Authors2
INSERT dbo.Authors2 SELECT * FROM dbo.Authors2
INSERT dbo.Authors2 SELECT * FROM dbo.Authors2
INSERT dbo.Authors2 SELECT * FROM dbo.Authors2
INSERT dbo.Authors2 SELECT * FROM dbo.Authors2
INSERT dbo.Authors2 SELECT * FROM dbo.Authors2
GO
SET SHOWPLAN_TEXT ON
GO
-- la tabella ha ora piu' di 23000 righe, vediamo cosa succede
SELECT au_lname, au_fname
FROM dbo.Authors2
WHERE au_lname LIKE 'm%' and au_fname LIKE 's%'
GO
/*
  |--Table Scan(OBJECT:([pubs].[dbo].[Authors2]),
WHERE:(like([Authors2].[au_lname], 'm%', NULL) AND
like([Authors2].[au_fname], 's%', NULL)))
il query processor ha determinato che e' piu' conveniente fare un table scan
piuttosto che index seek, bookmark lookup e filtro
*/
-- creiamo un indice anche sulla seconda colonna
SET SHOWPLAN_TEXT OFF
GO
CREATE INDEX ix_authors2_fname ON dbo.authors2 (au_fname)
GO
SET SHOWPLAN_TEXT ON
GO
SELECT au_lname, au_fname
FROM dbo.Authors2
WHERE au_lname LIKE 'm%' and au_fname LIKE 's%'
GO
/*
  |--Hash Match(Inner Join, HASH:([Bmk1000])=([Bmk1000]),
RESIDUAL:([Bmk1000]=[Bmk1000]))
       |--Index Seek(OBJECT:([pubs].[dbo].[Authors2].[ix_authors2_lname]),
SEEK:([Authors2].[au_lname] >= 'Lþ' AND [Authors2].[au_lname] < 'N'),
WHERE:(like([Authors2].[au_lname], 'm%', NULL)) ORDERED FORWARD)
       |--Index Seek(OBJECT:([pubs].[dbo].[Authors2].[ix_authors2_fname]),
SEEK:([Authors2].[au_fname] >= 'Rþ' AND [Authors2].[au_fname] < 'T'),
WHERE:(like([Authors2].[au_fname], 's%', NULL)) ORDERED FORWARD)
in questo caso il query processor ha deciso che la selettivita' dei due
indici e buona
e quindi puo' fare una Index Seek su ciascun indice e fare una merge dei
risultati.
nota bene che se fai una SELECT * anziche' SELECT au_lname, au_fname per il
query
processor rimane piu' efficiente fare una table scan
*/
-- proviamo a vedere cosa succedere con il carattere jolly in testa
SELECT au_lname, au_fname
FROM dbo.Authors2
WHERE au_lname LIKE '%m%' and au_fname LIKE '%s%'
GO
/*
  |--Table Scan(OBJECT:([pubs].[dbo].[Authors2]),
WHERE:(like([Authors2].[au_lname], '%m%', NULL) AND
like([Authors2].[au_fname], '%s%', NULL)))
gli indici non vengono utilizzati
*/
-- creiamo un indice composto
SET SHOWPLAN_TEXT OFF
GO
CREATE INDEX ix_authors2_bothnames ON dbo.authors2 (au_lname, au_fname)
GO
SET SHOWPLAN_TEXT ON
GO
SELECT au_lname, au_fname
FROM dbo.Authors2
WHERE au_lname LIKE '%m%' and au_fname LIKE '%s%'
GO
/*
  |--Index Scan(OBJECT:([pubs].[dbo].[Authors2].[ix_authors2_bothnames]),
WHERE:(like([Authors2].[au_lname], '%m%', NULL) AND
like([Authors2].[au_fname], '%s%', NULL)))
siamo tornati alla situazione precedente (perche' l'indice copre
compltamente la
richiesta), ma si tratta sempre di un Index Scan, meno efficiente di un
Index Seek,
inoltre se devi selezionare un altro campo...
*/
SELECT au_lname, au_fname, phone
FROM dbo.Authors2
WHERE au_lname LIKE '%m%' and au_fname LIKE '%s%'
GO
/*
  |--Table Scan(OBJECT:([pubs].[dbo].[Authors2]),
WHERE:(like([Authors2].[au_lname], '%m%', NULL) AND
like([Authors2].[au_fname], '%s%', NULL)))
si ritorna ad una table scan
attenzione anche ad una altra cosa....
*/
-- creiamo un clustered index...
SET SHOWPLAN_TEXT OFF
GO
CREATE CLUSTERED INDEX pk_authors2 ON dbo.authors2 (au_id)
GO
SET SHOWPLAN_TEXT ON
GO
SELECT au_lname, au_fname, phone
FROM dbo.Authors2
WHERE au_lname LIKE '%m%' and au_fname LIKE '%s%'
GO
/*
  |--Clustered Index Scan(OBJECT:([pubs].[dbo].[Authors2].[pk_authors2]),
WHERE:(like([Authors2].[au_lname], '%m%', NULL) AND
like([Authors2].[au_fname], '%s%', NULL)))
a prima vista sembrerebbe utilizzare un indice, e di fatto lo sta facendo,
ma solo perche'
in presenza di un indice clustered la tabella e' fisicamente implementata
con l'indice
(tutti i dati sono nelle pagine foglia dell'indice clustered), quindi di
fatto sta
leggendo tutte le pagine foglia dell'indice clustered cioe' un table scan
*/
--CUT HERE--
Alcune linee guida sono:
- evitare la selezione di tutti i campi (SELECT *) ma selezionare solo i
campi
che servono per soddisfare la query (se c'e' un indice che copre la query, o
se
ci sono piu' indici che fusi la coprono, si evitano le operazioni di
Bookmark Lookup
e/o table scan)
- evitare di utilizzare le ricerche con il carattere jolly (%) in testa
perche' nella migliore delle ipotesti viene fatta una index scan e non
una index seek
- evitare di selezionare grosse quantita' di dati. Attenzione al fatto che
spesso il server termina la Query velocemente e spende poi il resto del
tempo per comunicare le righe al client, e ci possono essere due trappole:
le righe sono veramente tante e ci vuole del tempo per spedirle, oppure
il client potrebbe essere troppo lento nel riceverle.
Come ti ho gia' detto queste sono solo degli spunti, l'argomento e'
molto piu' vasto ed oltre ai Books Online ci sono svariati libri
(ad esempio Inside SQL Server 2000, SQL Server 2000 Performance Tuning)
Ciao,
--
Gianluca Hotz
Backoffice MVP (SQL Server) MCP SQL Server and MCP Windows
Vice-President of the Italian User Group for SQL Server
Founding Member of the Italian User Group for .NET
http://www.ghotz.com
http://www.ugiss.org

Grazie di cuore a tutti quelli che mi hanno risposto, in modo particolare a
Gianluca Hotz.
Ho un bel po di materiale da studiare per approfondire e sperimentare.
grazie ancora.
Stefano