Archivi categoria: T-SQL

Esempi di SQL Graph Database su GitHub

Facebooktwittergoogle_plusredditlinkedin

SQL Server offre da sempre gli strumenti per gestire i dati gerarchici e le relazioni tra le entità. A volte, però, le relazioni tra le entità possono diventare complesse. Pensiamo ad una relazione molti-a-molti, i database relazionali non dispongono di soluzioni native per questo tipo di legame, che viene comunemente implementato con una tabella di associazione.

SQL Server 2017, grazie all’introduzione di Graph Database, permette di esprimere certi tipi di query più facilmente rispetto ad un database relazionale puro.

Alcuni giorni fa, Perry Skountrianos (@pskountrianos) ha confermato il “pull request” che ho fatto sul repository Microsoft/sql-server-samples di GitHub. Questo repository contiene esempi di codice che spiegano come utilizzare alcune funzionalità di SQL Server, SQL Azure e Azure Data Warehouse. Ogni esempio include il relativo file README.md che spiega come eseguire ed utilizzare il codice.

Nella directory relativa a SQL Graph (sql-server-samples/samples/demos/sql-graph/recommendation-system) troverete tre demo.

La prima spiega come creare e popolare i nuovi oggetti “Graph” ovvero due nuovi tipi di tabelle “Node” e “Edge”. La seconda demo ha l’obiettivo di introdurre la nuova clausola MATCH (T-SQL) usata per eseguire query su Graph DB attraversando i nodi e le relazioni creati nella prima demo. Nell’ultima demo verrà mostrato come costruire un sistema di raccomandazione per le offerte di vendita (basato sul database WideWorldImporters), in grado di determinare i prodotti consigliati sulla base del prodotto che il cliente sta acquistando oppure ha appena acquistato sul nostro ipotetico e-commerce, uno scenario tipico di utilizzo di SQL Graph.

Hai sentito parlare di SQL Graph e vuoi saperne di più? Questi esempi, disponibili su GitHub, sono un ottimo punto di partenza!

Buon divertimento!

Conversioni implicite: cosa sono e che impatto hanno sulle nostre query

Facebooktwittergoogle_plusredditlinkedin

Come credo alla maggior parte di voi, spesso, anche a me capita di dover mantenere codice scritto da qualcun altro. Uno degli aspetti che (ri)trovo con una certa frequenza è che, spesso, non prestiamo attenzione a come scriviamo le nostre query, sottovalutando l’impatto che queste possono avere sul nostro sistema.

Proprio recentemente mi sono imbattuto in una serie di batch (dalle semplici query a complesse procedure) dove non si era prestata la dovuta attenzione all’utilizzo dei tipi dato (ad esempio nella definizione di variabili e costanti, ma anche nelle colonne delle stesse tabelle), andando di fatto a creare qualche inconveniente, oltre che di mera natura estetica (e quindi di qualità del codice), anche (e soprattutto) di natura prestazionale. Buona parte di questi problemi era dovuta all’utilizzo frequente delle funzioni di conversione CAST e CONVERT (dovuti a probabili errori di modellazione delle tabelle come ad esempio stessa colonna in due tabelle differenti ma con differente tipo dato), ma la parte più critica e rilevante era dovuta  alla presenza di una miriade di conversioni implicite.

Affrontiamo la questione per gradi e facciamo un piccolo passo indietro, partendo dalle basi, cercando di capire cosa siano e che impatto possano avere nei nostri sistemi.

Per “conversione” si intende quell’ “operazione con cui si traduce un valore espresso in una determinata unità di misura in un altro valore, espresso in un’altra unità di misura” (Wikipedia).

Riadattando opportunamente la definizione, possiamo dire che all’interno del nostro contesto una conversione è quell’operazione tramite cui è possibile tradurre un valore espresso in un determinato tipo dato in un valore espresso in un altro tipo dato.

Nel mettere in relazione dunque due tipi dato qualsiasi si possono sostanzialmente verificare tre casi:

  • La conversione non è consentita:
    • non è ad esempio possibile convertire un tipo dato BIT in un tipo dato DATE
    • l’esecuzione forzata di una tale conversione produce un errore (“Explicit conversion from data type bit to date is not allowed.“)
  • La conversione è consentita e deve avvenire esplicitamente
    • ovvero è possibile solo tramite l’utilizzo delle funzioni CAST o CONVERT (come ad es. tra CHAR e BINARY)
  • La conversione è consentita ed avvenire in modo implicito
    • ovvero avviene in maniera automatica senza bisogno di utilizzare le funzioni CAST o CONVERT  (come ad es. tra CHAR e NCHAR)
    • l’automazione è gestita direttamente dal Query Processor (QP) ed avviene tramite l’ausilio della funzione riservata CONVERT_IMPLICIT

Combinando tutte le possibili coppie di tipo dato otteniamo una matrice dove possiamo leggere se e come la conversione può avvenire. Questa matrice è visibile sulla documentazione online nella pagina di documentazione dei metodi CAST e CONVERT.

NB: Si noti che la matrice contiene anche qualche caso particolare, però non rilevante ai fini della nostra analisi e comunque riconducibile ad una delle tre casistiche sopra riportate.

Chi di voi si è già preso la briga di “sbirciare” la matrice delle conversioni avrà già notato a colpo d’occhio quanto siano numerose le casistiche in cui una conversione implicita si possa verificare. Ecco dunque che diventa essenziale conoscere gli scenari di errore più comuni in modo da poterli evitare o comunque affrontare in modo sereno e consapevole: vediamo qualche esempio pratico tramite l’ausilio del database AdventureWorks.

Proviamo ad eseguire la seguente query:

SELECT BusinessEntityID, JobTitle, HireDate
FROM HumanResources.Employee
WHERE NationalIDNumber = '112457891'

Questo il piano di esecuzione:

Ad una prima occhiata non sembrerebbe presentare nulla di particolare. Ma in realtà esplorando i dettagli dell’operazione “Index Seek” notiamo che il QP ha applicato la funzione CONVERT_IMPLICIT  alla nostra costante riscrivendo di fatto il Seek Predicate. Ciò è dovuto all’utilizzo di un qualificatore di stringa non coerente con il tipo dato della colonna “NationalIDNumber” (NVARCHAR(15)). In particolare abbiamo utilizzato una stringa delimitata da apici singoli (‘112457891′) la quale viene identificata dal sistema come una stringa non-Unicode, mentre avremmo dovuto utilizzare una stringa delimitata da apici preceduti da una lettera N (maiuscola) (N’112457891’) la quale indica invece una stringa di caratteri Unicode e quindi in accordo con il tipo dato della colonna.

Correggendo dunque la query nel seguente modo

SELECT BusinessEntityID, JobTitle, HireDate
FROM HumanResources.Employee
WHERE NationalIDNumber = N'112457891'

possiamo notare come il “Seek Predicate” sia molto più semplice e non faccia più utilizzo della funzione di conversione implicita.

Tuttavia, se concludessimo qui la nostra analisi, tralasceremmo l’aspetto rilevante della questione, ovvero la valutazione dell’impatto che una conversione implicita può avere sulle scelte operate dal QP. A tal proposito vediamo cosa succede se, ad esempio, utilizziamo una costante numerica al posto di una costante stringa all’interno della nostra query:

SELECT BusinessEntityID, JobTitle, HireDate
FROM HumanResources.Employee
WHERE NationalIDNumber = 112457891

Osserviamo il piano d’esecuzione:

Ullalla!!! La conversione implicita, questa volta, ha prodotto un cambiamento decisamente impattante e negativo: l’operazione che prima era una “Index Seek” è ora diventata una “Index Scan”!

Questo degrado è giustificato dal fatto che l’utilizzo di una funzione (sia esso implicito o esplicito) all’interno di un predicato di filtro compromette la cossiddetta SARGability dell’intera clausola e di fatto vanifica l’utilizzo di ogni eventuale indice disponibile sulla nostra tabella, obbligandoci ad effetuarne una intera scansione.

Possiamo dunque concludere che quando nei piani di esecuzione delle nostre query compaiono delle conversioni implicite, queste sono sovente un’indicazione di scarse performance nonchè dei possibili indicatori di errori di design dei nostri schema. Riallacciandomi all’introduzione, aggiungo che è dunque bene prestare molta attenzione al modo in cui scriviamo le nostre query perchè (talvolta inconsapevolmente) potremmo creare delle situazioni molto spiacevoli e, magari, di non facile identificazione!

Ultima nota… alcuni tipi dato (ad es. quelli testuali) sono strutturati secondo particolari bit-pattern (la COLLATION) e che i passaggi tra differenti Collation comportano una conversione implicita!

Fate attenzione!!

Come calcolare il check-digit di un barcode in T-SQL

Facebooktwittergoogle_plusredditlinkedin

Chi ha avuto l’opportunità di sviluppare software per la movimentazione delle merci, sa che per identificare, memorizzare e gestire in modo efficiente la movimentazione dei prodotti all’interno di un magazzino, è necessario adottare un sistema di movimentazione basato su codici a barre.

Un codice a barre è la rappresentazione grafica di una sequenza di numeri e altri simboli. La rappresentazione consiste di linee (barre) e spazi. Un codice a barre è tipicamente composto da cinque parti, una di queste è il carattere di controllo, noto anche come cifra di controllo.

L’articolo seguente descrive l’algoritmo di calcolo della cifra di controllo di un codice a barre UCC/EAN, confronta due possibili implementazioni per determinare la cifra di controllo di un barcode EAN13 utilizzando il linguaggio T-SQL..

How to calculate barcode check-digit using T-SQL language

Buona lettura!

TOP(n) WITH TIES, nuova feature? No, é sempre esistita!

Facebooktwittergoogle_plusredditlinkedin

C’è ancora chi si meraviglia davanti all’opzione WITH TIES.

Molti non sanno che esiste o ne sottovalutano l’utilità.

Consideriamo la query

SELECT TOP 3 Name, ListPrice 
FROM SalesLT.Product 
ORDER BY ListPrice ASC;

Questa query ritornerà i primi 3 articoli ordinati per ListPrice crescente.

L’opzione WITH TIES indica che, invece di restituire solo il numero richiesto di righe, la query restituirà anche tutte le righe aventi lo stesso valore dell’ultima riga in base ai criteri di ordinamento (OrderDate, nel nostro caso). Questo significa che si potrebbero ottenere più righe rispetto a quelle richieste, ma la selezione delle righe diventa di tipo deterministico (al contrario del caso precedente).

SELECT TOP 3 WITH TIES Name, ListPrice 
FROM SalesLT.Product 
ORDER BY ListPrice ASC;

A questo punto potrebbero venire molte idee su come poter utilizzare questa opzione.

Enjoy your sql.

 

 

Uso “nascosto” del tempdb

Facebooktwittergoogle_plusredditlinkedin

Qualche settimana fa, mentre ero al lavoro, mi sono ritrovato a dover risolvere un problema apparentemente non molto strano, ma che tuttavia nasconde qualche retroscena interessante. I fatti sono stati più o meno questi:

Circa a metà mattinata mi è stato segnalato un problema di prestazioni su uno dei sistemi che abbiamo in gestione nel nostro team di lavoro; dopo alcuni semplici controlli è stato subito chiaro che il rallentamento era sostanzialmente dovuto ad un problema di contency sul tempdb. “Beh.. abbastanza semplice!” – ho subito pensato! – “la colpa è mia perché (ahi ahi ahi) non ho ancora fatto lo split del file dati sul tempdb!”.

Così, alla prima occasione utile, ho provveduto a splittare i file (4 nel mio caso) pensando di aver già praticamente risolto il problema! Il primo giorno tutto ok, il secondo anche, ma il terzo… immaginerete la sorpresa nel sentirmi dire che si stava ripresentando esattamente lo stesso problema! Ovviamente, da tecnico, ho voluto (e dovuto) verificare, perché il primo pensiero è stato che la causa dei (nuovi) rallentamenti fosse un’altra. Ma la sorpresa è stata doppia: non solo c’era ancora un problema di contency sul tempdb, ma era apparentemente più grave di quello segnalatomi qualche giorno prima. Utilizzando la store procedure sp_whoisactive di Adam Machanic, sono facilmente risalito al comando che stressava maggiormente il tempdb: una banale stored procedure per la scrittura di log!

Eccola:

CREATE PROCEDURE dbo.WriteLog
    @level INT,
    @message NVARCHAR(MAX)
AS
BEGIN
    SET NOCOUNT ON;
    IF EXISTS (SELECT * FROM dbo.LogLevel WHERE [Level] >= @level)
        INSERT INTO [dbo].[Log] ([Level], [Message], [TimeStamp]) VALUES (@level, @message, GETDATE());
END;

… eh??? … ma dove sarebbe l’accesso al tempdb?!

La risposta alla domanda è abbastanza semplice (anche se non del tutto intuitiva) e riguarda l’uso (implicito) delle “Worktables” (qui la definizione ufficiale), ma per scoprirlo dobbiamo scendere più in dettaglio e dare un’occhiata al piano di esecuzione:

Uso nascosto del tempdb Old Plan

Quello che balza subito all’occhio è che per valutare l’esistenza (funzione EXISTS) del set di dati definito dalla query “SELECT * FROM dbo.LogLevel WHERE [Level] >= @level” viene utilizzato l’operatore “Nested Loops”. In questa situazione, Query Optimizer produce un piano di esecuzione finalizzato alla determinazione della prima riga nel minor tempo possibile e presuppone che i dati siano distribuiti uniformemente. L’operatore Nested Loops fornisce spesso il modo più veloce per restituire un piccolo numero di righe (altri operatori come Hash Join o Merge Join sono più performanti su grandi volumi di dati). Qui, la scelta di utilizzare l’operatore Nested Loops è dovuta al fatto che il Query Optimizer ha applicato il pattern Row Goal, tipico per l’implementazione della clausola EXISTS. L’output viene utilizzato da uno step successivo del piano di esecuzione (Compute Scalar) per poter effettuare il test di esistenza (COND WITH QUERY). La seconda query (INSERT) invece utilizza un piano di esecuzione del tutto lineare senza presentare nulla di anomalo.

Quando l’operazione di join, implementata da Nested Loops, non potrà essere eseguita in memoria, l’Engine utilizzerà una worktable (come abbiamo osservato in questo esempio) per memorizzare temporaneamente il risultato di tale algoritmo al fine di renderlo disponibile per lo step successivo.

Più in generale è possibile dire che l’engine di SQL Server può decidere di utilizzare una worktable per poter completare qualsiasi operazione logica di uno statement SQL.

  • UNION, EXCEPT, INTERSECT
  • CTE
  • Sub-query
  • Funzioni
  • Viste
  • Variabili tabella
  • Operazioni di ordinamento
  • Raggruppamenti
  • etc etc

sono tutti ottimi candidati all’utilizzo di working tables.

Ritornado al caso esemplificato, il problema era dunque legato all’utilizzo della funzione EXISTS, la quale probabilmente sia appoggiava al tempdb per valutare l’esistenza del set di dati definito dalla query “SELECT * FROM dbo.LogLevel WHERE [Level] >= @level“.

Un numero molto elevato di scritture di log (applicativi) nei momenti di picco di utilizzzo del sistema producevano un numero altrettanto elevato di accessi al tempdb e conseguenti latch che, di fatto, accodavano tutte le richieste (peraltro in un contesto dove il tempdb è già molto sollecitato da operazioni ben più rilevanti del semplice logging!).

La soluzione più efficace, in questo caso, è stata quella di elimiare l’utilizzo della funzione EXISTS e riscrivere la stored procedure nel seguente modo:

ALTER PROCEDURE dbo.WriteLog
    @level INT,
    @message NVARCHAR(MAX)
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO [dbo].[Log] ([Level], [Message], [TimeStamp])
    SELECT TOP 1 @level, @message, GETDATE()
    FROM dbo.LogLevel
    WHERE [Level] >= @level;
END;

Il piano di esecuzione diventa dunque:

Uso nascosto del tempdb New Plan

Come si può facilmente vedere, oltre ad aver unito le due query in una sola, scompare in questo modo anche l’utilizzo dell’operatore Nested Loop e, scorrendo più attentamente il piano di esecuzione, possiamo concludere che non dovrebbero esserci ulteriori step “a rischio di Worktable”!