Blog do Alex Pimenta

Hoje vou falar sobre alguns problemas que detectei além de algumas facetas que uso no meu dia a dia.

“Parameter Sniffing”

Algumas situações fazem com que o SQL Server Engine não consiga mapear o melhor plano de execução nas consultas feitas dentro de Stored Procedures, passando os parâmetros na cláusula where.

Exemplo:

CREATE PROC [dbo].[spExemplo] (@pcModelo VARCHAR (3) = 'MOD')
AS SELECT *
FROM [dbo].[Tabela] TAB (NOLOCK)
WHERE (TAB.Modelo = @pcModelo OR @pcModelo = 'MOD');

Este é um típico caso onde o engine do SQL Server não consegue encontrar o melhor plano de execução. Para entender mais sobre o problema, procure sobre “Parameter Sniffing”.

Como resolvemos?

Existem duas formas. Ou você usa a cláusula Option com a opção Optimize for Unknown. Ou declara variáveis locais e coloca a variável local na cláusula Where ao invés do parâmetro da Stored Procedure.

Solução 1:

CREATE PROC [dbo].[spExemplo] (@pcModelo VARCHAR (3) = 'MOD')
AS SELECT * FROM [dbo].[Tabela] TAB (NOLOCK)
WHERE (TAB.Modelo = @pcModelo OR @pcModelo = 'MOD')
OPTION (OPTIMIZE FOR UNKNOWN);

Solução 2:

CREATE PROC [dbo].[spExemplo] (@pcModelo VARCHAR (3) = 'MOD')
AS
DECLARE @lModelo AS VARCHAR (3);
SET @lModelo = @pcModelo;

SELECT *
FROM [dbo].[Tabela] TAB (NOLOCK)
WHERE (TAB.Modelo = @lModelo OR @lModelo = 'MOD');

Pesquisa de strings dentro de Stored Procedures

CREATE FUNCTION [dbo].[fnCountChar] (
@String AS TEXT,
@charToFind AS VARCHAR(100) )
RETURNS INT
AS BEGIN
DECLARE @Count AS INT = 0;
DECLARE @Lastpos As INT = CHARINDEX(@charToFind, @String);
IF (@Lastpos>0) BEGIN
  SET @Count = 1;
  WHILE @Lastpos >0 BEGIN
    SET @Count = @Count + 1;
    SET @Lastpos = CHARINDEX(@charToFind, @String, @Lastpos+1);
  END;
END;
RETURN @Count;
END
GO

DECLARE @String AS VARCHAR(8000) = 'CARACTER_A_SER_PROCURADO';
SELECT o.[name], line=[dbo].[fnCountChar](SUBSTRING(c.[text],0,CHARINDEX(@String, c.[text])),CHAR(13)),[text]=SUBSTRING(REPLACE(REPLACE(REPLACE(REPLACE(c.[text], CHAR(13), ' '), CHAR(10), ''), CHAR(9), ''), ' ', ' '),CHARINDEX(@String, REPLACE(REPLACE(REPLACE(REPLACE(c.[text], CHAR(13), '|'), CHAR(10), ''), CHAR(9), ' '), ' ', ' '))-50, 100) FROM MSCIS_Temp.sys.syscomments c JOIN MSCIS_Temp.sys.sysobjects o on o.id = c.id WHERE o.[xtype] = 'P' AND REPLACE(REPLACE(REPLACE(c.[text],CHAR(13),' '),CHAR(10),' '),CHAR(9),' ') like '%' + @String + '%';
GO

DROP FUNCTION [dbo].[fnCountChar];

Executando uma Stored Procedure em um Select From

SELECT *
FROM OPENQUERY(sszsdev06,'exec [Database].[dbo].[StoredProcedure] 111,''TEXTO''');

Note que:

‘exec [Database].[dbo].[StoredProcedure] 111,”TEXTO”’

É o mesmo que:

exec [Database].[dbo].[StoredProcedure] 111,’TEXTO’

Fazendo split de string delimitada

DECLARE @sNomes varchar(max)= 'Alex,Pimenta,Desenvolvedor,SQL,DOT NET';
DECLARE @sDelimitador CHAR(1) = ',';
DECLARE @xml XML = N'<root><strings>' + replace(@sNomes,@sDelimitador,'</strings><strings>') + '</strings></root>';
SELECT Tab.S.value('.','varchar(17)') AS [Coluna] FROM @xml.nodes('//root/strings') as Tab(S);

Pivot Table – Consultas Cruzadas.

Imaginem que temos uma tabela de alunos e cada aluno tem suas notas mensalmente.

Uma consulta neste cenário nos retornaria o resultado linearmente, repetindo o nome de cada aluno com suas notas mensalmente. Existe uma forma de mostrarmos uma linha com o nome do aluno e suas notas mensalmente em colunas. Para isso usamos o Pivot Table.

Vamos demonstrar. Primeiro, preparamos as tabelas:

CREATE TABLE tb_temp_alunos (
id INT NOT NULL PRIMARY KEY,
nome VARCHAR (50));
GO

INSERT INTO tb_temp_alunos VALUES (1, 'Alex Pimenta');
INSERT INTO tb_temp_alunos VALUES (2, 'Thatiane Pimenta');
INSERT INTO tb_temp_alunos VALUES (3, 'Andressa Pimenta');
INSERT INTO tb_temp_alunos VALUES (4, 'Estela Pimenta');
INSERT INTO tb_temp_alunos VALUES (5, 'Maria Pimenta');
GO

CREATE TABLE tb_temp_notas (
id INT NOT NULL IDENTITY (1,1) PRIMARY KEY,
id_nome INT NOT NULL,
mes VARCHAR(20),
nota INT,
FOREIGN KEY (id_nome) REFERENCES tb_temp_alunos);
GO

INSERT INTO tb_temp_notas (id_nome, mes, nota) VALUES (1, 'Janeiro', 10);
INSERT INTO tb_temp_notas (id_nome, mes, nota) VALUES (1, 'Fevereiro', 9);
INSERT INTO tb_temp_notas (id_nome, mes, nota) VALUES (1, 'Março', 8);
INSERT INTO tb_temp_notas (id_nome, mes, nota) VALUES (1, 'Abril', 5);
INSERT INTO tb_temp_notas (id_nome, mes, nota) VALUES (1, 'Maio', 9);
INSERT INTO tb_temp_notas (id_nome, mes, nota) VALUES (1, 'Junho', 10);
INSERT INTO tb_temp_notas (id_nome, mes, nota) VALUES (2, 'Janeiro', 5);
INSERT INTO tb_temp_notas (id_nome, mes, nota) VALUES (2, 'Fevereiro', 6);
INSERT INTO tb_temp_notas (id_nome, mes, nota) VALUES (2, 'Março', 7);
INSERT INTO tb_temp_notas (id_nome, mes, nota) VALUES (2, 'Abril', 5);
INSERT INTO tb_temp_notas (id_nome, mes, nota) VALUES (2, 'Maio', 9);
INSERT INTO tb_temp_notas (id_nome, mes, nota) VALUES (2, 'Junho', 8);
INSERT INTO tb_temp_notas (id_nome, mes, nota) VALUES (3, 'Janeiro', 4);
INSERT INTO tb_temp_notas (id_nome, mes, nota) VALUES (3, 'Fevereiro', 2);
INSERT INTO tb_temp_notas (id_nome, mes, nota) VALUES (3, 'Março', 8);
INSERT INTO tb_temp_notas (id_nome, mes, nota) VALUES (3, 'Abril', 10);
INSERT INTO tb_temp_notas (id_nome, mes, nota) VALUES (3, 'Maio', 10);
INSERT INTO tb_temp_notas (id_nome, mes, nota) VALUES (3, 'Junho', 10);
INSERT INTO tb_temp_notas (id_nome, mes, nota) VALUES (4, 'Janeiro', 1);
INSERT INTO tb_temp_notas (id_nome, mes, nota) VALUES (4, 'Fevereiro', 2);
INSERT INTO tb_temp_notas (id_nome, mes, nota) VALUES (4, 'Março', 5);
INSERT INTO tb_temp_notas (id_nome, mes, nota) VALUES (4, 'Abril', 8);
INSERT INTO tb_temp_notas (id_nome, mes, nota) VALUES (4, 'Maio', 9);
INSERT INTO tb_temp_notas (id_nome, mes, nota) VALUES (4, 'Junho', 10);
INSERT INTO tb_temp_notas (id_nome, mes, nota) VALUES (5, 'Janeiro', 1);
INSERT INTO tb_temp_notas (id_nome, mes, nota) VALUES (5, 'Fevereiro', 1);
INSERT INTO tb_temp_notas (id_nome, mes, nota) VALUES (5, 'Março', 10);
INSERT INTO tb_temp_notas (id_nome, mes, nota) VALUES (5, 'Abril', 10);
INSERT INTO tb_temp_notas (id_nome, mes, nota) VALUES (5, 'Maio', 9);
INSERT INTO tb_temp_notas (id_nome, mes, nota) VALUES (5, 'Junho', 9);
GO

Agora se fizermos um consulta teremos o resultado linear:

SELECT nm.id, nome, mes, nota
FROM tb_temp_alunos nm
JOIN tb_temp_notas nt ON (nt.id_nome=nm.id)

Nosso objetivo é tornar a visão agrupado por usuário, mostrando suas notas mensalmente na horizontal.

DECLARE @COLUMNS VARCHAR (6000) = STUFF(( SELECT DISTINCT TOP 100 PERCENT
'],[' + nm.mes FROM tb_temp_notas nm ORDER BY '],[' + nm.mes FOR XML PATH('')), 1, 2, '') + ']';
DECLARE @SQL VARCHAR(max) = 'select * from (select nm.id, nome, mes, nota from tb_temp_alunos nm join tb_temp_notas nt on (nt.id_nome=nm.id)) tbl pivot (sum(nota) for tbl.mes in (' + @COLUMNS + ')) pvt';

EXEC(@SQL);