Template designed by

Set Based Thinking

Davide Mauridmauri@solidq.com www.davidemauri.it

brought to you by

Works with SQL Server from 6.5, on BI from 2003

Specialized in Data Solution Architecture, Database Design, Performance Tuning, BI

Microsoft SQL Server MVP

President of UGISS (Italian SQL Server UG)

Mentor @ SolidQ

Regular Speaker @ SQL Server events

Consulting & Training

Davide Mauri

3

Iniziamo con un problema apparentemente sempliceE vediamo dove ci porta

Dobbiamo sviluppare una sistema di online-ticketing per la prenotazione di posti a sedere in teatri / stadi / cinema, ecc. ecc.

Problema

Supponiamo di avere questa situazione

Arriva un gruppo di 5 persone: dove gli dico di sedersi?Che algoritmo implemento nel mio sistema?

Cinema, teatri e magazzini

Cinema, teatri e magazzini – Row by Row

3 4 5 6 7 9A. Imposta il «Contatore Posti Liberi» (CPL) a 1B.Memorizza il valore ordinale (n) del primo posto

libero trovato (PPL)C.Se il successivo posto libero ha ordinale n+1

allora aumenta il contatore CPLD.Altrimenti memorizza PPL e CPL da qualche parteE. Riparti da A

1 2 5 1

Dem

o Prova su strada

E’ il migliore?…oppure solamente il primo che ci è venuto in mente?

E’ veramente cosi semplice come ci sembra?…alla fine il codice è molto meno semplice di quanto ipotizzato!

E’ scalabile?Se lo vediamo allo stadio di Maracanà (95.000 posti a sedere) cosa succede?

Cinema, teatri e magazzini – Row by Row

Dem

o L’alternativa

Le performance migliorano di un ordine di grandezza!E di più di uno per quato riguarda gli I/O!!!!

Dobbiamo cambiare il modo di pensare alla soluzioneNon dobbiamo ragionare in termini di ciò che possiamo fare scrivendo il codice T-SQL

Dobbiamo pensare a come trovare la soluzione migliore da un punto di vista puramente “logico”

Considerazioni

Think in Sets!

Dobbiamo pensare all’intero insieme dei dati

In questo modo possiamo trovare una soluzione logica esprimibile in modo unicamente dichiarativoE non procedurale!

• Il codice è solamente un “tool”. E’ il mezzo, non il fine!

In questo modo permettiamo all’optimizer di fare il solo lavoro, altrimenti ci sostiuiamo a lui

Thinking in Sets

“Houston abbiamo un problema”Siamo abituati a pensare in modo procedurale (visto che la maggior parte dei linguaggi di programmazione lo è)

Dobbiamo quindi disimparare a pensare in questo modo ed imparare a pensare set-based!

Thinking in Sets

All’inizio sarà difficile, ma non preoccupateviAll’inizio ogni cosa è difficile!

Thinking in Sets

Un pò meno:Facile: Ma è chiaro chi dei due vincerà in unacompetizione!

Tutto quello di cuiabbiamo bisognoè esercizio!

Ora vedremo una serie di problemi reali, incontrati durante differenti consulenzeOpportunamente rimodellati e generalizzati

E vedremo come risolverli usando un approccio “Set-Based”, anche quando non sembra possibile che esista una soluzione in tal sensoVedrete che per la fine della sessione inizierete a “vedere la luce”

• Magari con un pò di mal di testa

Thinking in Sets

Iniz

iam

o!

Dati valore del prestito, inizio e fine dello stesso, frequenza di pagamento e interessi, generare tutte le righe corrispondenti a tutte le date di scadenza delle rate

Rate, Mutui & Prestiti

Loan Value# Rates

Payment Frequency

% Interest

€ 10.000,00 12 1 5,00% € 20.000,00 12 6 6,00% € 30.000,00 12 6 5,50%

Rate Date

€ 875,00 January-10

€ 875,00 February-10

€ 875,00 March-10

€ 875,00 April-19

€ 875,00 May-10

€ 875,00 June-10

€ 875,00 July-10

€ 875,00 August-10

€ 875,00 September-10

€ 875,00 October-10

€ 875,00 November-10

€ 875,00 December-10

Dem

o

Calcolo delle rate

Data una lista di macchine, restituire l’ultimo stato notificato nel log di lavoro

Raggruppamenti e valori correlati

ControllerABC

Controller StatusTimeSta

mpA StandBy 1001B StandBy 1001C StandBy 1003D Working 1004A Working 1004B StandBy 1006D StandBy 1009D Working 1010D Working 1011

Dem

o

Raggruppamenti e

valori correlati

Data una tabella contenente tutte le transazioni giornarliere, estrarre per ogni azione, per ogni ora, i valori di apertura, chiusura, ecc.

Azioni

SymbolID TransactionDateTime Price Volume

12008-01-01

09:15:21.000 75.800 2589

12008-01-01

09:25:44.000 68.200 4386

12008-01-01

09:29:31.000 74.300 2837

12008-01-01

09:34:42.000 68.900 2937

12008-01-01

09:39:13.000 72.300 4513

12008-01-01

09:43:35.000 67.300 838

12008-01-01

09:51:57.000 73.800 1380

12008-01-01

09:56:42.000 68.700 4190

tran_hourfrom_datetime to_datetime symbol_id high low volume open close

92008-01-01 09:15:21.000

2008-01-01 09:56:42.000 1 75.800 67.300 23670 75.800 68.700

Dem

o

Borsa & Azioni

Supponiamo di avere questa situazione

Arriva un gruppo di 5 persone: dove gli dico di sedersi?Che algoritmo implemento nel mio sistema?

Cinema, teatri e magazzini

Cinema, teatri e magazzini – Set Based

A.Se SQL Server potesse vedere l’immagine qui soprapotrebbe facilmente identificare dei gruppi

E quindi potremmo usare un semplice «Group By»B.Dobbiamo quindi trovare il modo di «convertire» questa

caratteristica «visuale» in un qualcosa di utilizzabile da SQL Server

Più che convertire...far emergere

Cinema, teatri e magazzini – Set Based

A.Enumeriamo tutti i postiB.Enumeriamo solo i posti liberiC.Facciamo una differenzaD.Ed ora possiamo fare un bel GROUP BY!

1 2 3 4 5 6 7 8 910…

1 2 3 4 5 6 7 …2 2 2 2 2 3 3

Dem

o

Cinema, teatri e

magazzini

Data una lista di macchine, raggruppare per range tutti gli stati riportati

Raggruppamenti e Range

Controller StatusTimeStam

pA StandBy 1001B StandBy 1002B StandBy 1003B StandBy 1004A Working 1005A Working 1006A Working 1007B Working 1008B Working 1009A StandBy 1010A StandBy 1011B StandBy 1012

Controller Status From To

AStandB

y 1001 1001

BStandB

y 1002 1004

AWorkin

g 1005 1007

BWorkin

g 1008 1009

AStandB

y 1010 1011

BStandB

y 1012 1012

Dem

o

Raggruppamenti e

range

Grazie a tutti per la partecipazione

Riceverete il link per il download a slide e demo via email nei prossimi giorni

Per contattarmi

dmauri@solidq.com

Grazie