Buongiorno.
Mi trovo di fronte ad uno "strano" problema di prestazioni in merito ad una query. Cerco di spiegare bene il contesto.
Ho una web-application (un sito e-commerce) che permette all'utente collegato di effettuare una ricerca all'interno del catalogo articoli.
Esistono diverse tipologie di utente e ciascuno ha una visibilità più o meno ampia del catalogo (che può variare nel tempo).
Per "pilotare" la visibilità degli articoli, uso una tabella (Filtro) dove, in fase di login, vengono inseriti per l'utente l'elenco completo degli articoli che lui può vedere. Poi, nelle varie query, userò questa tabella per "filtrare" i risultati delle ricerche e di altre operazioni che possono essere effettuate (l'utente "standard" può vedere circa 10.000 articoli su un catalogo totale di quasi 60.000, non so se questa possa essere un'informazione utile).
Le uniche operazioni che modificano i dati all'interno della tabella Filtro sono:
- La login (che elimina preventivamente tutti gli articoli presenti per l'utente e poi la ripopola in base alle condizioni attuali)
- La logout (che elimina tutti gli articoli dell'utente, se è il caso [c'è tutto un controllo su eventuali sessioni multiple, non mi dilungo])
- La scadenza della sessione (che è esattamente come una logout)
Passiamo alla struttura delle tabelle coinvolte nel problema.
Articolo:
- ID INT (Primary Key)
- codiceNuovo VARCHAR(18)
- descrizione VARCHAR(80)
- ... altri dati su cui non mi dilungo
Filtro:
- idUtente INT (Primary Key)
- codiceNuovo VARCHAR(18) (PrimaryKey)
- ... altri dati su cui non mi dilungo
Disponibilita:
- codiceNuovo VARCHAR(18) (Primary Key)
- giacenza INT
- impegnato INT
- ... altri dati su cui non mi dilungo
La tabella Articolo possiede un campo ID (chiave primaria) per ragioni storiche (è una tabella che è nata su MySQL, che serviva ad altri scopi, che poi è stata importata così com'è su SQL Server per ragioni che non sto qui a spiegare). In realtà il campo ID sarebbe assolutamente superfluo in quanto il campo "codiceNuovo" da solo sarebbe già una chiave (tant'è che questo campo è indicizzato, per ovvie ragioni). Il campo descrizione, assieme ad altri campi, è incluso in un indice FullText.
Della tabella Filtro ho già parlato: qui dentro vengono inseriti tutti gli articoli "visibili" dal singolo utente (la chiave primaria, infatti, è composta dall'idUtente e il codice articolo).
Non credo ci sia nulla da dire sulla tabella Disponibilità visto che, dai test, non rientra nelle concause del problema.
Problema: ogni tanto gli utenti si lamentano che le ricerche "non funzionano" (non producono risultati). Ho, quindi, fatto diversi test e ho constatato che la query di ricerca ha dei problemi di performance: ogni tanto l'esecuzione della query impiega davvero troppo tempo, questo fa scattare il timeout che ho imposto per l'esecuzione della query nella webapp e, di conseguenza, il risultato all'utente è nullo (in realtà, la query produce dei risultati, ma decisamente troppo tardi).
Vediamo la query ("pippo" è ovviamente un dato variabile e <id dell'utente> ce l'ho in sessione):
SELECT TOP 360 A.ID, A.codiceNuovo, A.descrizione, (D.giacenza - D.impegnato) AS disponibile, ...
FROM Articolo A
LEFT JOIN CONTAINSTABLE(Articolo, *, 'FORMSOF(INFLECTIONAL, "pippo") OR FORMSOF(THESAURUS, "pippo")') AS Key_TBL ON A.ID = Key_TBL.[KEY]
INNER JOIN Filtro F ON (A.codiceNuovo = F.codiceNuovo AND F.idUtente = <id dell'utente>)
LEFT JOIN Disponibilita D ON (A.codiceNuovo = D.codiceNuovo)
WHERE CONTAINS((A.descrizione, A.Titolo, <altri campi dell'indice full-text>), '"pippo*"')
ORDER BY Key_TBL.RANK DESC
In condizioni normali questa query viene eseguita in circa 5 secondi. Nei casi in cui l'utente lamenta il problema, la stessa identica query impiega circa 45 secondi per ritornare il risultato. (tutti i test li ho eseguiti usando SQL Server Management Studio, quindi non dipendono dalla WebApp).
Quando mi sono trovato nella situazione descritta (query che impiega più di 40 secondi), ho provato a "togliere di mezzo" la tabella del filtro (come si può vedere è in INNER JOIN per poter "filtrare" i risultati in base alla visibilità dell'utente): come per magia la prestazione torna ai massimi livelli (meno di 5 secondi).
Analizzando la situazione ho notato che il problema sparisce (la query ritorna assolutamente prestante) se eseguo una ricostruzione (o una riorganizzazione) dell'indice della tabella Filtro.
ALTER INDEX ALL ON Filtro REORGANIZE
(posso usare anche REBUILD)
Ad occhio, quindi, sembrerebbe un problema di frammentazione dell'indice di quella tabella (ci può stare vista la quantità di record che vengono inseriti ed eliminati quotidianamente, anche nell'arco di una sola ora). La tabella Filtro può variare, infatti, come dimensione da 0 record a poco più di 1.000.000 in base alla quantità e tipologia di utenti che sono mediamente collegati.
Mi sarei aspettato, comunque, che il problema si presentasse in presenza di una forte frammentazione... invece noto che il problema si presenta anche con una frammentazione inferiore al 2%.
Per controllare la frammentazione dell'indice mi sono servito della query messa a disposizione dal MSDN:
SELECT a.object_id,
object_name(a.object_id) AS TableName,
a.index_id,
name AS IndedxName,
avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID ('MioDB')
, OBJECT_ID('Filtro')
, NULL
, NULL
, NULL) AS a
INNER JOIN sys.indexes AS b ON (a.object_id = b.object_id AND a.index_id = b.index_id)
A questo punto mi si sono prospettate due possibili strade:
1) Effettuare una riorganizzazione/ricostruzione dell'indice della tabella Filtro con una certa frequenza
2) Provare a modificare la tabella Articolo, togliendo di mezzo il campo ID (inutile), facendo diventare chiave primaria il campo "codiceNuovo"
Non so se la 2 possa portare qualche miglioria (chiedo a voi)
La 1) non mi piace, ma se serve la posso attuare molto facilmente.
Voi avete qualche suggerimento?
Grazie per l'attenzione posta fin qui (mi rendo conto di aver scritto un pippone, ma ho voluto dare più dettagli possibili).