Dicas de T-SQL – Função Minutos Úteis

Olá Pessoal,

Nesse post quero mostrar como conseguimos melhorar o desempenho de uma função que calcula os minutos úteis e o impacto na performance ao utilizar um loop com o comando WHILE. O intuito aqui também será mostrar como às vezes é importante pensar como podemos fazer uma mesma tarefa, mas de uma forma mais inteligente e eficiente.

OBS: Vou colocar o link para DOWNLOAD dos scripts no final do post.

Função Minutos Úteis – COM WHILE:

Em mais um caso do dia a dia, um dos nossos clientes informou que estava com muita lentidão em uma função que calcula os minutos úteis entre duas datas. Você poderia utilizar essa função por exemplo para calcular o tempo de espera/fila em minutos úteis.

Segue abaixo o código da função que chamei de “fncMinutos_Uteis_While”:

USE [Traces]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[fncMinutos_Uteis_While] (@Dt_Inicial DATETIME, @Dt_Final DATETIME)
RETURNS BIGINT 
AS
BEGIN
	DECLARE @Qt_Minutos BIGINT = 0
	
	-- Loop para somar minuto a minuto na data inicial até chegar na data final
	WHILE (@Dt_Inicial < @Dt_Final)
	BEGIN
		-- Valida se é um minuto 
		IF (
			DATEPART(WEEKDAY, @Dt_Inicial) NOT IN (1,7)										-- Desconsidera sábados e domingos
			AND (DATEPART(HOUR, @Dt_Inicial) >= 8 AND DATEPART(HOUR, @Dt_Inicial) < 18)		-- Valida se está entre as 8 e 18 horas
		)
		BEGIN
			-- Soma um minuto no resultado
			SELECT @Qt_Minutos = @Qt_Minutos + 1
		END

		-- Soma um minuto na data inicial
		SELECT @Dt_Inicial = DATEADD(MINUTE, 1, @Dt_Inicial)
	END

	-- Retorna a quantidade total de minutos úteis
	RETURN  @Qt_Minutos
END
GO

USE [Traces]

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE FUNCTION [dbo].[fncMinutos_Uteis_While] (@Dt_Inicial DATETIME, @Dt_Final DATETIME)

RETURNS BIGINT

BEGIN

DECLARE @Qt_Minutos BIGINT = 0

-- Loop para somar minuto a minuto na data inicial até chegar na data final

WHILE (@Dt_Inicial < @Dt_Final)

BEGIN

-- Valida se é um minuto

IF (

DATEPART(WEEKDAY, @Dt_Inicial) NOT IN (1,7) -- Desconsidera sábados e domingos

AND (DATEPART(HOUR, @Dt_Inicial) >= 8 AND DATEPART(HOUR, @Dt_Inicial) < 18) -- Valida se está entre as 8 e 18 horas

)

BEGIN

-- Soma um minuto no resultado

SELECT @Qt_Minutos = @Qt_Minutos + 1

END

-- Soma um minuto na data inicial

SELECT @Dt_Inicial = DATEADD(MINUTE, 1, @Dt_Inicial)

END

-- Retorna a quantidade total de minutos úteis

RETURN @Qt_Minutos

END

Analisando o código da função, verificamos que ela utiliza um loop com o WHILE para somar um minuto na data inicial até que ela atinja a data final.

Observações e Limitações:

Horário Útil -> 08 às 18 horas (17:59:59 horas mais especificamente), ou seja, são 10 horas úteis por dia;
Desconsidera sábados e domingos;
NÃO desconsidera horário de almoço;
NÃO desconsidera feriados;
Como o código dessa função é bem simples, isso facilita alguns tipos de ajustes. Por exemplo, para alterar o horário útil, basta alterar o IF dentro do WHILE. Ou para incluir o horário de almoço ou algum feriado, bastaria incluir mais alguns IFs dentro do WHILE também.

“Beleza Luiz, vamos fazer alguns testes pra ver se essa função funciona mesmo?” Bora!!!

USE Traces

SELECT 
	[dbo].[fncMinutos_Uteis_While] ('20200121 08:00', '20200121 12:00') AS Teste_1,
	[dbo].[fncMinutos_Uteis_While] ('20200121 06:00', '20200121 12:00') AS Teste_2,
	[dbo].[fncMinutos_Uteis_While] ('20200121 12:00', '20200121 18:00') AS Teste_3,
	[dbo].[fncMinutos_Uteis_While] ('20200121 12:00', '20200121 23:00') AS Teste_4,
	[dbo].[fncMinutos_Uteis_While] ('20200121 18:30', '20200121 20:00') AS Teste_5,
	[dbo].[fncMinutos_Uteis_While] ('20200121 08:30', '20200121 09:17') AS Teste_6,
	[dbo].[fncMinutos_Uteis_While] ('20200117 17:00', '20200120 09:30') AS Teste_7,
	[dbo].[fncMinutos_Uteis_While] ('20200121 08:00', '20200122 10:00') AS Teste_8

USE Traces

SELECT

[dbo].[fncMinutos_Uteis_While] ('20200121 08:00', '20200121 12:00') AS Teste_1,

[dbo].[fncMinutos_Uteis_While] ('20200121 06:00', '20200121 12:00') AS Teste_2,

[dbo].[fncMinutos_Uteis_While] ('20200121 12:00', '20200121 18:00') AS Teste_3,

[dbo].[fncMinutos_Uteis_While] ('20200121 12:00', '20200121 23:00') AS Teste_4,

[dbo].[fncMinutos_Uteis_While] ('20200121 18:30', '20200121 20:00') AS Teste_5,

[dbo].[fncMinutos_Uteis_While] ('20200121 08:30', '20200121 09:17') AS Teste_6,

[dbo].[fncMinutos_Uteis_While] ('20200117 17:00', '20200120 09:30') AS Teste_7,

[dbo].[fncMinutos_Uteis_While] ('20200121 08:00', '20200122 10:00') AS Teste_8

Teste 1: Pela manhã temos 4 horas úteis, ou seja, 4 * 60 = 240 minutos;

Teste 2: O horário das 06 às 08 horas não é útil, por isso o resultado é o mesmo do teste anterior;

Teste 3: Das 12 às 18 horas temos 6 horas úteis, ou seja, 6 * 60 = 360 minutos;

Teste 4: O horário das 18 às 23 horas não é útil, por isso o resultado é o mesmo do teste anterior;

Teste 5: Não temos horas úteis das 18:30 às 20:00 horas. Com isso, o resultado é 0;

Teste 6: Das 08:30 às 09:17 temos 47 minutos úteis;

Teste 7: Das 17:00 horas de uma sexta-feira (17/01/2020) até as 09:30 horas de segunda-feira (20/01/2020), temos 150 minutos úteis (60 minutos na sexta e 90 minutos na segunda);

Teste 8: Por fim, temos um dia inteiro útil (10 horas úteis) e mais duas horas do outro dia, ou seja 600 + 120 = 720.

“Show Luiz!!! Mas calma aí, se eu passar um intervalo de um mês, a função vai somar minuto a minuto de cada um desses dias, é isso mesmo? Isso não é muito custoso?”

Pois é! Agora imagine o tempo que será necessário para executar essa função para milhares de registros em uma query! Na versão otimizada irei explicar melhor uma forma de resolver esse problema.

Teste de Performance:

Em primeiro lugar, vamos criar uma tabela de teste com 2 mil registros com um campo ID e outro com uma data.

USE Traces

CREATE TABLE Teste_Minutos_Uteis (
	Id_Teste INT IDENTITY(1,1) NOT NULL,
	Dt_Referencia DATETIME NOT NULL
)

CREATE NONCLUSTERED INDEX SK01_Teste_Minutos_Uteis
ON Teste_Minutos_Uteis(Dt_Referencia)
WITH(FILLFACTOR=90, DATA_COMPRESSION = PAGE)

GO

INSERT INTO Teste_Minutos_Uteis
VALUES(GETDATE()-1)
GO 1000

INSERT INTO Teste_Minutos_Uteis
VALUES(GETDATE()-2)
GO 1000

SELECT * FROM Teste_Minutos_Uteis
ORDER BY Dt_Referencia

USE Traces

CREATE TABLE Teste_Minutos_Uteis (

Id_Teste INT IDENTITY(1,1) NOT NULL,

Dt_Referencia DATETIME NOT NULL

)

CREATE NONCLUSTERED INDEX SK01_Teste_Minutos_Uteis

ON Teste_Minutos_Uteis(Dt_Referencia)

WITH(FILLFACTOR=90, DATA_COMPRESSION = PAGE)

INSERT INTO Teste_Minutos_Uteis

VALUES(GETDATE()-1)

GO 1000

INSERT INTO Teste_Minutos_Uteis

VALUES(GETDATE()-2)

GO 1000

SELECT * FROM Teste_Minutos_Uteis

ORDER BY Dt_Referencia

Agora vamos fazer um SELECT nesses 2 mil registros e executar a função com o WHILE para cada um deles.

USE Traces

SET STATISTICS IO, TIME ON

DECLARE @Dt_Final DATETIME = GETDATE()

SELECT Dt_Referencia AS Dt_Inicial, @Dt_Final AS Dt_Final, [dbo].[fncMinutos_Uteis_While] (Dt_Referencia, @Dt_Final) AS Minutos_Uteis
FROM Teste_Minutos_Uteis
ORDER BY Minutos_Uteis

USE Traces

SET STATISTICS IO, TIME ON

DECLARE @Dt_Final DATETIME = GETDATE()

SELECT Dt_Referencia AS Dt_Inicial, @Dt_Final AS Dt_Final, [dbo].[fncMinutos_Uteis_While] (Dt_Referencia, @Dt_Final) AS Minutos_Uteis

FROM Teste_Minutos_Uteis

ORDER BY Minutos_Uteis

O tempo total de execução foi de mais de 11 segundos para apenas 2 mil registros. Agora imagine essa função com o WHILE sendo executada 1 milhão de vezes, isso iria demorar demaaaaais!!!

SQL Server Execution Times:
   CPU time = 7844 ms,  elapsed time = 11151 ms.

1 2	SQL Server Execution Times: CPU time = 7844 ms, elapsed time = 11151 ms.

“Eitaaa!!! E agora???” Veremos como podemos melhorar isso!!! #gogogo

Tabela Dias Úteis:

O primeiro passo para a nossa função otimizada será utilizar uma tabela chamada “Dias_Uteis”. Essa tabela é muito importante na otimização, sendo uma das responsáveis por eliminar a necessidade de utilizar o loop com o WHILE e melhorar consideravelmente a performance da função. Nós também desconsideramos alguns feriados nela.

Segue abaixo o script de criação da tabela:

OBS: Nesse exemplo, inseri apenas os dias úteis dos anos 2019 e 2020. Você pode inserir um período maior ajustando as variáveis abaixo.

USE [Traces]

CREATE TABLE [dbo].[Dias_Uteis] (
	Dt_Referencia DATETIME
)

CREATE CLUSTERED INDEX [PK_Dias_Uteis]
ON [dbo].[Dias_Uteis] (Dt_Referencia)
WITH(FILLFACTOR = 90, DATA_COMPRESSION = PAGE)

-- INSERE DIAS UTEIS NA TABELA -> 2019 E 2020
DECLARE @Dt_Inicial DATETIME = '20190101', @Dt_Final DATETIME = '20210101'

WHILE (@Dt_Inicial < @Dt_Final)
BEGIN
	-- DESCONSIDERA SABADO E DOMINGO
	IF (DATEPART(WEEKDAY, @Dt_Inicial) NOT IN (7, 1) )
	BEGIN
		INSERT INTO [dbo].[Dias_Uteis] VALUES(@Dt_Inicial)
	END

	SELECT @Dt_Inicial = DATEADD(DAY, 1, @Dt_Inicial)
END

-- REMOVE ALGUNS FERIADOS – FIQUE A VONTADE PARA INCLUIR OUTROS AQUI!!! 
DELETE [dbo].[Dias_Uteis]
WHERE Dt_Referencia IN ('20190101','20191225','20200101','20201225')

SELECT * FROM [dbo].[Dias_Uteis]
ORDER BY Dt_Referencia

USE [Traces]

CREATE TABLE [dbo].[Dias_Uteis] (

Dt_Referencia DATETIME

)

CREATE CLUSTERED INDEX [PK_Dias_Uteis]

ON [dbo].[Dias_Uteis] (Dt_Referencia)

WITH(FILLFACTOR = 90, DATA_COMPRESSION = PAGE)

-- INSERE DIAS UTEIS NA TABELA -> 2019 E 2020

DECLARE @Dt_Inicial DATETIME = '20190101', @Dt_Final DATETIME = '20210101'

WHILE (@Dt_Inicial < @Dt_Final)

BEGIN

-- DESCONSIDERA SABADO E DOMINGO

IF (DATEPART(WEEKDAY, @Dt_Inicial) NOT IN (7, 1) )

BEGIN

INSERT INTO [dbo].[Dias_Uteis] VALUES(@Dt_Inicial)

END

SELECT @Dt_Inicial = DATEADD(DAY, 1, @Dt_Inicial)

END

-- REMOVE ALGUNS FERIADOS – FIQUE A VONTADE PARA INCLUIR OUTROS AQUI!!!

DELETE [dbo].[Dias_Uteis]

WHERE Dt_Referencia IN ('20190101','20191225','20200101','20201225')

SELECT * FROM [dbo].[Dias_Uteis]

ORDER BY Dt_Referencia

Função Minutos Úteis – SEM WHILE (Otimizada):

Agora vamos para a versão otimizada da função que chamei de “fncMinutos_Uteis”. Não vou explicar todos os passos dela, mas deixei o código com muitos comentários para tentar facilitar o entendimento.

Basicamente, nós trocamos o WHILE por um SELECT na tabela “Dias_Uteis” e criamos uma lógica para fazer alguns cálculos considerando os horários dos extremos (08 e 18 horas).

Observações e Limitações:

Horário Útil -> 08 às 18 horas (17:59:59 horas mais especificamente), ou seja, são 10 horas úteis por dia;
Desconsidera sábados e domingos;
Desconsidera feriados.
NÃO desconsidera horário de almoço.
Como o código dessa função NÃO é tão simples, isso pode dificultar algum tipo de ajuste. Contudo, depois você pode entender o código e fazer as alterações necessárias.

“Legal Luiz, mas o resultado será o mesmo da função anterior?” Vamos repetir o teste e conferir isso agora!

USE Traces

SELECT 
	[dbo].[fncMinutos_Uteis] ('20200121 08:00', '20200121 12:00') AS Teste_1,
	[dbo].[fncMinutos_Uteis] ('20200121 06:00', '20200121 12:00') AS Teste_2,
	[dbo].[fncMinutos_Uteis] ('20200121 12:00', '20200121 18:00') AS Teste_3,
	[dbo].[fncMinutos_Uteis] ('20200121 12:00', '20200121 23:00') AS Teste_4,
	[dbo].[fncMinutos_Uteis] ('20200121 18:30', '20200121 20:00') AS Teste_5,
	[dbo].[fncMinutos_Uteis] ('20200121 08:30', '20200121 09:17') AS Teste_6,
	[dbo].[fncMinutos_Uteis] ('20200117 17:00', '20200120 09:30') AS Teste_7,
	[dbo].[fncMinutos_Uteis] ('20200121 08:00', '20200122 10:00') AS Teste_8

USE Traces

SELECT

[dbo].[fncMinutos_Uteis] ('20200121 08:00', '20200121 12:00') AS Teste_1,

[dbo].[fncMinutos_Uteis] ('20200121 06:00', '20200121 12:00') AS Teste_2,

[dbo].[fncMinutos_Uteis] ('20200121 12:00', '20200121 18:00') AS Teste_3,

[dbo].[fncMinutos_Uteis] ('20200121 12:00', '20200121 23:00') AS Teste_4,

[dbo].[fncMinutos_Uteis] ('20200121 18:30', '20200121 20:00') AS Teste_5,

[dbo].[fncMinutos_Uteis] ('20200121 08:30', '20200121 09:17') AS Teste_6,

[dbo].[fncMinutos_Uteis] ('20200117 17:00', '20200120 09:30') AS Teste_7,

[dbo].[fncMinutos_Uteis] ('20200121 08:00', '20200122 10:00') AS Teste_8

Conforme era esperado, o resultado ficou igual a função anterior! Show!

Teste de Performance:

Por fim, vamos fazer o teste final para validar se todas essas alterações irão trazer alguma melhoria na performance da função.

USE Traces

SET STATISTICS IO, TIME ON

DECLARE @Dt_Final DATETIME = GETDATE()

SELECT Dt_Referencia AS Dt_Inicial, @Dt_Final AS Dt_Final, [dbo].[fncMinutos_Uteis] (Dt_Referencia, @Dt_Final) AS Minutos_Uteis
FROM Teste_Minutos_Uteis
ORDER BY Minutos_Uteis

USE Traces

SET STATISTICS IO, TIME ON

DECLARE @Dt_Final DATETIME = GETDATE()

SELECT Dt_Referencia AS Dt_Inicial, @Dt_Final AS Dt_Final, [dbo].[fncMinutos_Uteis] (Dt_Referencia, @Dt_Final) AS Minutos_Uteis

FROM Teste_Minutos_Uteis

ORDER BY Minutos_Uteis

O tempo de execução que antes era de mais de 11 segundos, agora caiu pra quase 0 segundos. WOW!!!!

SQL Server Execution Times:
   CPU time = 188 ms,  elapsed time = 266 ms.

1 2	SQL Server Execution Times: CPU time = 188 ms, elapsed time = 266 ms.

Conclusão:

As ideias que pretendo deixar como conclusão são:

CUIDADO ao utilizar funções no SQL Server com muitos registros, pois isso pode afetar a performance das queries.
Podemos ver como um LOOP executado milhares de vezes pode ser muito custoso para o SQL Server.
Conseguimos observar também como às vezes podemos obter grandes ganhos na performance ao reescrever uma rotina de uma forma mais otimizada. Claro que nem sempre será possível fazer esse tipo de alteração, mas tente questionar um pouco mais o: “mas sempre foi assim e está funcionando…”.

Função COM WHILE:

SQL Server Execution Times:
   CPU time = 7844 ms,  elapsed time = 11151 ms.

1 2	SQL Server Execution Times: CPU time = 7844 ms, elapsed time = 11151 ms.

Função SEM WHILE:

SQL Server Execution Times:
   CPU time = 188 ms,  elapsed time = 266 ms.

1 2	SQL Server Execution Times: CPU time = 188 ms, elapsed time = 266 ms.

Nesse caso, tivemos uma redução de mais de 11 segundos e a função passou a executar quase instantaneamente! Agora imagine uma tela no sistema que demorava mais de 10 segundos passar a retornar de forma instantânea, os usuários e clientes agradecem!!!

A DICA FINAL É: PENSE FORA DA CAIXA!!!

Melhorias futuras:

Segue abaixo alguns pontos que podem ser melhorados no futuro nessa função:

Desconsiderar o horário de almoço, quando for necessário;
Parametrizar o intervalo do horário útil para facilitar alterações no código. Com isso, você poderia alterar mais facilmente o seu horário útil. Por exemplo, de 08 às 19 horas, ou 07 às 22 horas, e por aí vai.
Fique à vontade para sugerir melhorias também =)

Download:

Segue abaixo os links para DOWNLOAD dos scripts utilizados no post:

[fncMinutos_Uteis]

https://github.com/luizvitorf/SQLServer/blob/master/Scripts/T-SQL/%5BfncMinutos_Uteis%5D.sql

[fncMinutos_Uteis_While]

https://github.com/luizvitorf/SQLServer/blob/master/Scripts/T-SQL/%5BfncMinutos_Uteis_While%5D.sql

[fncMinutos_Uteis] – Tabela Dias Uteis e Testes

https://github.com/luizvitorf/SQLServer/blob/master/Scripts/T-SQL/%5BfncMinutos_Uteis%5D%20-%20Tabela%20Dias%20Uteis%20e%20Testes.sql

Espero que tenha gostado e que isso também possa ser útil no seu dia a dia. Até o próximo post!

Me siga no LinkedIn e YouTube para ficar por dentro das novidades.

Abraço,

Luiz Vitor França Lima

Consultor SQL Server

Função Minutos Úteis – COM WHILE:

Observações e Limitações:

Teste de Performance:

Tabela Dias Úteis:

Função Minutos Úteis – SEM WHILE (Otimizada):

Observações e Limitações:

Teste de Performance:

Conclusão:

Melhorias futuras:

Download:

Compartilhe isso:

Deixe uma respostaCancelar resposta