Windows Functions – Part 2 (Running Aggregations)
Galera, ontem eu escrevi sobre windows functions e até cheguei a mostrar um exemplo como running aggregations podem ser facilmente executadas utilizando a clausula OVER() + ORDER BY.
Desde o SQL Server 2005 temos suporte a clausula OVER(), porém ela não esta totalmente implementada, na verdade o que temos é o seguinte:
A tabela acima foi criada com base no artigo que o Itzik escreveu em 2007, quando ele solicitou a inclusão das Windows Functions no SQL Server 2008, coisa que hoje sabemos que não aconteceu, você pode ler o artigo aqui.
Podemos observar que a clausula ORDER BY não é suportada nas functions de agregação.
Diferença de Performance
Para ilustrar o quão importante e poderoso é o suporte a Windows Functions vamos fazer um comparativo de performance entre SQL Server e PostGreSQL.
Ok eu sei que a comparação vai ser injusta já que o SQL Server não tem suporte a Windows Functions e o PostGreSQL tem (me sinto tão mal em dizer isso 😦 ). Mas mesmo assim o propósito é testar a função e não o banco de dados.
Para simular este teste isso criei uma tabela chamada tblLancamentos e inseri 200 mil linhas nela, depois criei a mesma tabela no PostgreSQL e inserir os mesmo registros nela. Segue abaixo o script que usei para fazer isso.
SET NOCOUNT ON; GO IF OBJECT_ID('tblLancamentos') IS NOT NULL DROP TABLE tblLancamentos GO -- Tabela de Lançamentos para exemplificar o Subtotal CREATE TABLE tblLancamentos (ID_Conta Integer, DataLancamento Date, ValorLancamento Float) GO CREATE CLUSTERED INDEX ixC ON tblLancamentos (ID_Conta, DataLancamento) GO -- Insere os registros INSERT INTO tblLancamentos (ID_Conta, DataLancamento, ValorLancamento) SELECT (ABS(CHECKSUM(NEWID())) / 10000000), CONVERT(VarChar, GetDate() - (ABS(CHECKSUM(NEWID())) / 1000000.),112), (ABS(CHECKSUM(NEWID())) / 1000000.) GO 100000 INSERT INTO tblLancamentos (ID_Conta, DataLancamento, ValorLancamento) SELECT (ABS(CHECKSUM(NEWID())) / 10000000), CONVERT(VarChar, GetDate() - (ABS(CHECKSUM(NEWID())) / 1000000.),112), (ABS(CHECKSUM(NEWID())) / 1000000.) * -1 GO 100000
Abaixo o script para inserir os dados no PostGreSQL:
SELECT IDENTITY(int, 1,1) ID, 'INSERT INTO tblLancamentos (ID_Conta, DataLancamento, ValorLancamento) ' + 'VALUES('+''''''+ CONVERT(Varchar, ID_COnta) +''''',' + ''''''+ CONVERT(Varchar, DataLancamento, 112) +''''',' + ''''''+ CONVERT(Varchar, ValorLancamento) +'''''' + ')' AS St INTO #TMP FROM tblLancamentos GO CREATE CLUSTERED INDEX IX ON #TMP (ID) GO declare @i Int = 0, @st Varchar(MAX) select top 1 @i = id, @st = st from #tmp where id > @i order by id while @@rowCount > 0 begin Set @st = 'EXEC (''' + @st + ''') AT LINKED_EDB' print @I exec (@st) select top 1 @i = id, @st = st from #tmp where id > @i order by id end GO
Primeiro para que fiquemos felizes, vamos comparar as mesmas soluções em ambos os bancos e ver que o SQL Server é MELHOR 😉
SELECT a.ID_Conta, a.DataLancamento, a.ValorLancamento, (SELECT SUM(ValorLancamento) FROM tblLancamentos AS b WHERE a.ID_Conta = b.ID_Conta AND a.DataLancamento >= b.DataLancamento) AS Saldo FROM tblLancamentos AS a ORDER BY a.ID_Conta, a.DataLancamento, a.ValorLancamento GO
Podemos observar que o SQL Server levou 14 segundos para rodar a consulta:
No PostgreSQL o a consulta foi retornada em 35 segundos (bora arredondar pra cima né galera? ;-))
Agora a parte triste, vejamos o tempo ao utilizar a clausula OVER() com ORDER BY.
Para começo de conversa a consulta é muito mais simples:
SELECT ID_Conta, DataLancamento, ValorLancamento, SUM(ValorLancamento) OVER(ORDER BY DataLancamento ASC) AS Saldo FROM tblLancamentos AS a ORDER BY a.ID_Conta, a.DataLancamento, a.ValorLancamento
O tempo no banco gratuito chamado ProsgreSQL? Bem… é… ummm, quer dizer… onde? quem? do que que eu estava falando mesmo? Aaaa deixa pra lá vai…
Galera, me desculpa mas agora vou precisar sair porque preciso levar minha vó na aula Jiu-jitsu, ela tem campeonato semana que vem e não podemos dar mole.
Abraços…