martedì 12 giugno 2007
#
Oggi è una
giornata importante, almeno per un paio di motivi.
Il primo è il saluto che faccio alla Community di
UgiDotNet per lo spazio che mi ha dedicato, gli amici ed i colleghi con cui ho
condiviso il muro.
Davide, dopo uno
splendido lavoro, ha terminato la messa in linea di strumenti che possono
chiamarsi tali per una community.
E' quindi con molto piacere che porto (sposto) il
mio blog all'interno del posto più naturale che possa
avere: la community di UGISS.
Il secondo motivo è la partenza della SQL Conference
2007.
In questo momento, infatti, blog in tempo reale
dalla prima sessione della conferenza.
Quindi il nuovo blog dovrebbe (ri)partire al meglio
proprio con qualche post da Segrattle!
Grazie
UGI!
giovedì 31 maggio 2007
#
Ok, il titolo è un pò strano, ma non sapevo come
metterlo meglio ;-)
Ieri un cliente mi ha prospettato una domanda
interessante...
Supponiamo di avere una tabella [spostamenti] dove l'utente
memorizza i clienti che visita, quindi la data (quando) e la citta
(dove).
Supponiamo quindi che io, oggi, memorizzi visite
come:
cliente A - mattina - brescia
cliente B - mattina -
brescia
cliente C - pomeriggio - milano
cliente D - sera -
brescia
...
La domanda è:
come faccio a visualizzare le città
visitate in modo tale che se faccio più incontri nello stesso luogo la città mi
appaia una volta sola?
Ovvero: non mi interessa quanti clienti ho visitato
nella città "A", quanto avere sotto mano il percorso (tragitto)
fatto...
Interessante... mi sono divertito.
Ecco come
risolverei:
USE tempdb
GO
create table spostamenti
(
idRecord int primary key identity(1,1),
data datetime,
citta varchar(30)
)
go
insert spostamenti values ('20070531 9:00:00','brescia')
insert spostamenti values ('20070531 10:00:00','brescia')
insert spostamenti values ('20070531 10:30:00','brescia')
insert spostamenti values ('20070531 12:00:00','milano')
insert spostamenti values ('20070531 14:00:00','brescia')
insert spostamenti values ('20070531 14:10:00','desenzano')
insert spostamenti values ('20070531 14:30:00','brescia')
insert spostamenti values ('20070531 15:00:00','brescia')
insert spostamenti values ('20070531 16:00:00','brescia')
insert spostamenti values ('20070531 17:00:00','verona')
insert spostamenti values ('20070531 19:00:00','brescia')
-- Questa la mia tabella di spostamenti
select * from spostamenti order by data
/*
Risultato:
idRecord data citta
----------- ----------------------- ------------------------------
1 2007-05-31 09:00:00.000 brescia
2 2007-05-31 10:00:00.000 brescia
3 2007-05-31 10:30:00.000 brescia
4 2007-05-31 12:00:00.000 milano
5 2007-05-31 14:00:00.000 brescia
6 2007-05-31 14:10:00.000 desenzano
7 2007-05-31 14:30:00.000 brescia
8 2007-05-31 15:00:00.000 brescia
9 2007-05-31 16:00:00.000 brescia
10 2007-05-31 17:00:00.000 verona
11 2007-05-31 19:00:00.000 brescia
(11 row(s) affected)
*/
-- Questa la tabella con un check per verificare se sono stato più volte o meno
select * , (select count(1) from spostamenti S2 where S2.citta = S1.citta and S2.idRecord = S1.idRecord+1) as N from spostamenti S1
/*
Risultato:
idRecord data citta N
----------- ----------------------- ------------------------------ -----------
1 2007-05-31 09:00:00.000 brescia 1
2 2007-05-31 10:00:00.000 brescia 1
3 2007-05-31 10:30:00.000 brescia 0
4 2007-05-31 12:00:00.000 milano 0
5 2007-05-31 14:00:00.000 brescia 0
6 2007-05-31 14:10:00.000 desenzano
0
7 2007-05-31 14:30:00.000 brescia 1
8 2007-05-31 15:00:00.000 brescia 1
9 2007-05-31 16:00:00.000 brescia 0
10 2007-05-31 17:00:00.000 verona
0
11 2007-05-31 19:00:00.000 brescia 0
(11 row(s) affected)
*/
-- Questo il risultato finale
select citta from
(
select * , (select count(1) from spostamenti S2 where S2.citta = S1.citta and S2.idRecord = S1.idRecord+1) as N from spostamenti S1
) T
where N = 0
order by T.data
drop table spostamenti
go
/*
Risultato:
citta
------------------------------
brescia
milano
brescia
desenzano
brescia
verona
brescia
(7 row(s) affected)
*/
Praticamente con la seconda query siamo in grado di avere le
città che hanno la successiva identica con un valore = 1.
In questo modo,
filtrando per quel campo = 0, abbiamo solo le città che vogliamo
visualizzare.
Diciamo che
è un pò il "classico" problema del: "... voglio resettare un contatore al
cambio di articolo / fornitore / cliente / ..."
Commenti?
giovedì 10 maggio 2007
#
Per colorare a righe alternate un oggetto tabella è
sufficiente utilizzare la funzione RowNumber scrivendo, ad
esempio:
=iif(RowNumber(nothing) mod 2 = 0, "red",
"green")
Nelle matrici la RowNumber non può funzionare...
quindi dobbiamo realizzarla "a mano".
Per colorare le righe quindi:
1) ci serve una colonna che, tramite la funzione
RunningValue ed il CountDistinct possa farmi contare le righe in maniera univoca
e quindi, tramite la classica "... mod 2 = 0 ..." distinguere le righe pari e le
righe dispari.
Ad esempio, aggiungo al report una colonna in cui
visualizzare (proprietà text):
= iif(
RunningValue(Fields!XXXXXX.Value,CountDistinct,Nothing) mod 2 = 0, "#dedede",
"#ededed")
Dove XXXXX è il campo che mi da l'univocità di
riga.
Se l'univocità di riga la ottengo da più colonne (supponete di fare avere
colonne "cognome" e "nome"), il parametro di RunningValue deve essere la
concatenazione delle colonne:
= iif( RunningValue( (Fields!cognome.Value & Fields!nome.Value)
,CountDistinct,Nothing) mod 2 = 0, "red", "green")
2) rendiamo invisibile questa nuova colonna
3) nella riga della matrice impostiamo la proprietà BackgroundColor come:
= ReportItems!YYYYYY.Value
Dove YYYYYY è il nome che abbiamo dato alla nuova cella che abbiamo creato
tramite il punto 1
N.B.: se la matrice ha attivi i subtotali e se il background di queste celle
è imposato come trasparente, il colore sarà identico a quello dell'ultima riga
dati precedente
Sembra
che a Redmond i motori siano parecchio caldi....
Dopo quello che abbiamo visto al summit MVP
qualcosa comincia ad uscire.
Qui e qui...
sabato 28 aprile 2007
#
Il Query Optimizer di SQL
Server è un gioiellino (sicuramente migliorabile come tutte le cose).
E' il
componente in grado di esaminare le nostre query e le condizioni delle
interrogazioni e di valutare la strada migliore da percorrere per arrivare al
risultato richiesto.
Alcune volte però il Query Optimizer deve essere
aiutato.
Uno di questi casi riguarda tutte quelle condizioni in cui chiediamo
la negazione di qualcosa, ovvero quando utilizziamo operatori come:
<>,
NOT EXISTS, NOT IN, NOT LIKE, ...
Perchè?
Pechè SQL Server lavora meglio se gli
chiediamo l'esistenza di una riga, e non la sua "non esistenza".
Se possibile, quindi, potrebbe essere conveniente
trasformare la negazione in affermazione ;-)
Partire da una negazione simile a:
... where
P.idCategoria NOT IN (1,3,5,12,13)
Per arrivare ad un'affermazione
come:
... where P.idCategoria IN (select idRecord from categorie where
idRecord NOT IN (1,3,5,12,13))
La valutazione poi, come sempre, dipende dalla mole
di dati che andremmo ad interrogare.
Sicuramente tenete sempre presente (ed
analizzate) statistiche e piani escuzione.
Sono i nostri migliori
amici!
Vediamo un esempio:
-- utilizzo il db temporaneo
use tempdb
go
-- creo una tabella categorie
create table categorie
(
idRecord tinyint primary key identity(1,1),
codice char(1),
descrizione varchar(20)
)
go
-- creo una tabella prodotti
create table prodotti
(
idRecord int primary key identity(1,1),
idCategoria tinyint foreign key references categorie(idRecord),
descrizione varchar(50)
)
go
-- inserisco categorie
set nocount on
insert categorie values ('A','Categoria A')
insert categorie values ('B','Categoria B')
insert categorie values ('C','Categoria C')
insert categorie values ('D','Categoria D')
insert categorie values ('E','Categoria E')
insert categorie values ('F','Categoria F')
insert categorie values ('G','Categoria G')
insert categorie values ('H','Categoria H')
insert categorie values ('I','Categoria I')
insert categorie values ('L','Categoria L')
insert categorie values ('M','Categoria M')
insert categorie values ('N','Categoria N')
insert categorie values ('O','Categoria O')
insert categorie values ('P','Categoria P')
insert categorie values ('Q','Categoria Q')
insert categorie values ('R','Categoria R')
insert categorie values ('S','Categoria S')
insert categorie values ('T','Categoria T')
insert categorie values ('U','Categoria U')
insert categorie values ('V','Categoria V')
insert categorie values ('Z','Categoria Z')
-- Inserisco 420.000 righe
declare @i int
set @i = 0
while @i < 20000
begin
insert prodotti
select C.idRecord, P.descrizione from categorie C
cross join (select 'P ' + convert(varchar(25), getdate(), 121) as descrizione) p
set @i = @i + 1
end
go
create index idx_categoria on Prodotti (idCategoria)
--> abilitare la visualizzazione del piano di esecuzione
set statistics time on
set statistics io on
select count(C.descrizione), C.descrizione
from prodotti P
join categorie C on P.idCategoria = C.idRecord
where P.idCategoria NOT IN (1,3,5,12,13)
group by C.descrizione
set statistics io off
set statistics time off
/*
Table 'prodotti'. Scan count 21, logical reads 655
Table 'categorie'. Scan count 1, logical reads 2
SQL Server Execution Times: CPU time = 521 ms, elapsed time = 1765 ms.
*/
set statistics time on
set statistics io on
select count(C.descrizione), C.descrizione
from prodotti P
join categorie C on P.idCategoria = C.idRecord
where P.idCategoria IN (select idRecord from categorie where idRecord NOT IN (1,3,5,12,13))
group by C.descrizione
set statistics io off
set statistics time off
/*
Table 'prodotti'. Scan count 16, logical reads 499
Table 'categorie'. Scan count 1, logical reads 34
SQL Server Execution Times: CPU time = 271 ms, elapsed time = 1168 ms.
*/
-- faccio pulizia:
drop table prodotti
drop table categorie
go
venerdì 27 aprile 2007
#
Il tema
dell'indicizzazione dei dati è un tema interessantissimo, molto vasto, molto
richiesto e, spesso, poco conosciuto.
SQL Server utilizza, per definire il miglior piano
di esecuzione per le nostre interrogazioni, delle statistiche
in grado di fornirgli informazioni sulla selettività degli
indici.
A cosa serve la selettività? A capire se quel determinato
indice è utile o no.
La selettività viene valutata tramite un
valore di densità e, tramite questo, il query optimizer decide
se prendere in considerazione o meno un indice.
Un'alta densità indica che l'indice è poco
selettivo (una chiave primaria o una colonna UNIQUE hanno il massimo livello di
densità) e quindi usabile.
Una colonna che ha solo pochi valori (pensiamo ad
una colonna bit), per questo motivo, ha una altissima selettività e quindi una
piccola (piccolissima) utilità.
Come dire: sarebbe utile fare un indice sulla
colonna sesso di una tabella anagrafica?
SQL Server fornisce uno strumento in grado di
visualizzare la densità di un indice, il comando:
DBCC Show_Statistics (miaTabella,
mioIndice)
Vediamo un esempio...
-- utilizzo il tempdb per test
use tempdb
go
-- creo tabella di analisi
create table test
(
idRecord int identity(1,1),
valore1 int,
valore2 int,
constraint pk primary key clustered (idRecord)
)
go
set nocount on
-- inserisco 5000 righe di prova
declare @i int
set @i = 0
while @i < 5000
begin
insert test values (case when (@i%2 = 0) then 0 else 1 end, @i)
set @i = @i + 1
end
go
-- creo un indice sulla prima colonna
create index idx_pocoSelettivo on test (valore1)
-- creo un indice sulla seconda colonna
create index idx_moltoSelettivo on test (valore2)
go
-- questa è la mia tabella
select * from test
-- visualizzo le statistiche associato al primo indice (quello poco selettivo)
DBCC Show_Statistics ('test', 'idx_pocoSelettivo')
-- visualizzo le statistiche associato al secondo indice (quello molto selettivo)
DBCC Show_Statistics ('test', 'idx_moltoSelettivo')
--> Abilitare la visualizzazione del piano di esecuzione
-- Eseguo una query sulla colonna valore1
/*
Il piano di esecuzione mi mostra un clusterd index scan (l'indice, talmente poco selettivo, non gli serve a nulla!)
*/
set statistics io on
select * from test where valore1 = 1
set statistics io off
/*
Info statistiche di I/O:
Table 'test'. Scan count 1, logical reads 15
*/
-- Eseguo la stessa query con la stessa clausola where
-- sulla stessa colonna valore1 FORZANDO l'utilizzo
-- del mio indice poco selettivo:
set statistics io on
select * from test with (index (idx_pocoSelettivo)) where valore1 = 1
set statistics io off
/*
Info statistiche di I/O:
Table 'test'. Scan count 1, logical reads 5007 (!!!)
*/
-- faccio pulizia
drop table test
go
Anche Luca, MVP di SQL Server oltre che grande amico, ha
scritto un altro post sull'argomento (che consiglio di leggere): Misurare l'utilità di un indice.
giovedì 26 aprile 2007
#
Ho sentito spesso dire: "Per recuperare dati,
le operazioni di seek sono sempre più veloci delle operazioni di
scan".
Questo è sempre vero? Fate attenzione: no.
Perchè?
Perchè se la mia interrogazione riguarda
una consitente porzione di dati, diciamo una percentuale rilevante, le
operazioni di scan sono nettamente da preferire (e ben più veloci) di operazioni
di seek che, invece, sono il massimo nel momento in cui devo recuperare poche ed
isolate righe.
mercoledì 25 aprile 2007
#
Il mirroring di database è una bellissima (nuova)
funzionalità (qualcuno la chiama "il cluster dei poveri" :-)).
SQL
Server 2005 fornisce un wizard per poter configurare correttamente la macchina
Principal, il Mirror e, se servisse, il Witness (che può anche essere inserito
in un secondo momento).
Se qualcuno è passato dal newsgroup
microsoft.public.it.sql avrà forse letto del mio "amore" per queste procedure
guidate che cerco immancabilmente di evitare...
Ecco un altro
motivo...
Il wizard del mirroring parte dal presupposto (e
non vi segnala nulla in caso contrario) che tutte le istanze stiano girando
sotto lo stesso domain user account.
Se così non fosse l'utilizzo di
ENDPOINT con certificato digitale diventa
indispensabile!
Quindi, diventa indispensabile configurare il
mirroring via script T-SQL (Vedere l'istruzione CREATE
CERTIFICATE e, quindi, CREATE ENDPOINT).
Personalmente preferisco comunque configurare
sempre tramite script... so cosa faccio, so come lo faccio, so perchè lo
faccio ;-)
venerdì 20 aprile 2007
#
Un'interessante novità di SQL Server 2005 è quella di poter disabilitare un indice.
Può essere utile nei casi in cui si voglia mettere offline un indice mantenendo i metadati all'interno del database.
La domanda del post è: posso disabilitare un indice clustered?
La risposta: si! Ma a-t-t-e-n-z-i-o-n-e: equivale a disabilitare la tabella stessa e quindi a renderla irraggiungibile!
-- eseguo test sul db temp
use tempdb
GO
-- creazione di una tabella di test
create table test
(
idRecord int identity(1,1),
valore varchar(20),
CONSTRAINT PK_test PRIMARY KEY NONCLUSTERED (idRecord)
)
GO
-- creazione indice clustered sulla tabella
create clustered index idx_valore on test(valore)
GO
-- inserimento dati di prova
insert test values ('a')
insert test values ('b')
insert test values ('c')
insert test values ('d')
-- vedo la tabella
select * from test
-- ATTENZIONE: disabilito l'indice clustered!
-- (attenzione al warning che ci da SQL Server)
ALTER INDEX idx_valore ON test DISABLE
GO
-- vedo la tabella (NON funziona!!!)
select * from test
/*
Questo l'errore:
Msg 8655, Level 16, State 1, Line 3
The query processor is unable to produce a plan because the index 'idx_valore' on table or view 'test' is disabled.
*/
-- ricostruisco l'indice (il contrario di DISABLE è REBUILD!)
ALTER INDEX idx_valore ON test REBUILD
GO
-- vedo la tabella (ok)
select * from test
-- faccio pulizia
drop table test
GO
Spesso non si ricorda che un report, con qualsiasi
strumento esso venga costruito, rappresenta dati in un particolare e preciso
momento.
Ovvero uno stato che può, eventualmente, cambiare
un attimo dopo.
Per questo motivo penso sia importante fornire
sempre informazioni circa la data di escuzione e l'utente che ha eseguito il
report.
Personalmente risolvo questa esigenza grazie al
footer del report, alcune variabili globali e tre oggetti textBox che valorizzo
così:
= "Data esecuzione: " &
Globals!ExecutionTime.ToLongDateString & " : " &
Globals!ExecutionTime.ToLongTimeString
= "Utente: " & User!UserID
=
"Pag. " & Globals!PageNumber & " di " &
Globals!TotalPages
... oltre
questo imposto sempre il Language del report in maniera opportuna (così che le
date vengano formattate nelle corrette impostazioni internazionali)
;-)