Descobri que usar TVPs para passar vários valores para um procedimento armazenado é muito mais fácil e rápido, especialmente ao lidar com algumas colunas e algumas centenas de linhas, pois elimina a necessidade de análise de dados adicional. Por que alguém escolheria XML ou JSON em vez de TVPs? Em quais casos de uso eles oferecem melhor desempenho ou vantagens? Se tivesse uma chance, o que você escolheria entre os três? Obrigado!
Que consulta posso usar para determinar se um procedimento armazenado está sendo recompilado toda vez que é executado no SQL Server 2012? Suspeito que alguns dos nossos procedimentos armazenados podem estar sendo recompilados, mas não tenho certeza de como verificar. Obrigado!
temos um banco de dados no SQL Server 2019 com compatibilidade definida como 110 (SQL Server 2012). Temos um procedimento armazenado que está sendo recompilado com frequência, e notei que estamos usando muitas tabelas temporárias. Criamos uma tabela temporária, inserimos dados e, em seguida, adicionamos um índice nela para consultas posteriores. Suspeito que isso pode estar causando as recompilações. As tabelas temporárias acionam recompilações e, em caso afirmativo, em que condições? Obrigado!
Estou tentando acessar uma tabela em nosso servidor de relatórios (réplica secundária) de nosso servidor de warehouse por meio de um servidor vinculado. Tudo estava funcionando bem até a semana passada, mas agora está gerando um erro “não foi possível localizar estatísticas nos catálogos do sistema” .
Depois de alguma pesquisa, parece que preciso descartar e recriar uma estatística que está causando esse problema. No entanto, estou hesitante em fazê-lo em nosso servidor de produção ao vivo. Ainda não estou certo sobre o que está causando exatamente esse problema.
Alguém poderia explicar o que pode estar acontecendo e se há uma solução alternativa que não envolva a eliminação e a recriação de estatísticas? Eu realmente aprecio sua ajuda e tempo. Obrigado!
Eu tenho a seguinte tabela
Create table #products
(
productId int,
regionId int,
date datetime,
active bit
)
insert into #products values
(1,20,'10/10/2020',1),(1,20,'12/10/2022',0),(1,20,'5/8/2023',1),
(2,20,'11/11/2020',0),(2,20,'12/12/2022',1),(2,20,'5/8/2023',0),
(3,20,'10/10/2020',1),(3,20,'12/10/2022',0),(3,20,'5/8/2023',1)
Estou tentando descobrir quais produtos estavam ativos entre 1º de março de 2023 e 30 de março de 2023. Observando os dados fornecidos, apenas o ID do produto 2 estava ativo durante esse período. Como posso escrever uma consulta SQL para recuperar essas informações com precisão? Tentei algumas consultas, mas não estou obtendo os resultados esperados. Agradeço antecipadamente pelo seu tempo!
Eu tenho a seguinte consulta onde procuro linhas que não sejam iguais a 1 para a coluna istrue. No entanto, os resultados incluem apenas registros com 0 e omitem aqueles com nulo. Embora eu esteja ciente de que usar (istrue! = 1 ou istrue is null) produziria o resultado esperado, estou curioso sobre o mecanismo subjacente que faz com que o SQL Server exclua valores nulos. Você poderia fornecer informações sobre esse comportamento? Obrigado!
create table #test
(
id int,
val varchar(100),
istrue bit
)
insert into #test values
(1, 'test-1', 1),
(2, 'test-2', 0),
(3, 'test-3', 1),
(4, 'test-4', 0),
(5, 'test-5', null),
(6, 'test-6', null)
select * from #test
where istrue != 1
drop table if exists #test
Eu tenho as seguintes tabelas
-- Create the #product table
CREATE TABLE #product
(
id UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWID(),
name VARCHAR(100),
createdDate DATE
);
-- Create the #tmpproducts table
CREATE TABLE #tmpProducts
(
rownum INT PRIMARY KEY IDENTITY(1,1),
name VARCHAR(100),
createdDate DATE
);
-- Insert data into #tmpSignings
INSERT INTO #tmpProducts (name, createdDate)
VALUES
('John Doe', '2023-11-22'),
('Jane Smith', '2023-11-23'),
('Alice Johnson', '2023-11-24'),
('Bob Williams', '2023-11-25'),
('Eva Brown', '2023-11-26'),
('Michael Davis', '2023-11-27'),
('Sophia Miller', '2023-11-28'),
('William Wilson', '2023-11-29'),
('Olivia Jones', '2023-11-30'),
('Daniel White', '2023-12-01');
Tenho 10 linhas em #tmpProducts e meu objetivo é inserir essas linhas na tabela #product. Para cada linha inserida com sucesso na tabela de produtos, preciso associar o ID recém-gerado ao rownum correspondente na tabela #tmpProducts. Como posso fazer isso? Qualquer ajuda é muito apreciada. Obrigado!
Resultado esperado:
#tmpProdutos :
#produtos :
resultado final :
Tenho duas tabelas, #customer e #abbr. A tabela #customer consiste em duas colunas: 'id' e 'name', enquanto a tabela #abbr inclui entradas para abreviações e seus correspondentes formulários completos. Meu objetivo é substituir todas as abreviações nos nomes dos clientes pelos respectivos formulários completos. Por exemplo, se o nome de um cliente for 'Object ME CT', espero que o resultado seja 'Object Medical Control'. No entanto, com minha consulta atual, ela produz duas linhas: 'Object ME Control' e 'Object Medical CT'. Meu objetivo é uma única linha por 'id', onde todas as abreviações sejam substituídas por seus formulários completos da tabela #abbr. Como posso conseguir isso? Obrigado!
create table #customer
(
id int identity(1,1)
,cname varchar(100)
)
create table #abbr
(
abbr varchar(100),
fname varchar(100)
)
insert into #customer values('Assign ME CT'),('Assign ME'),('Assign CT')
insert into #abbr values ('ME','Medical'),('CT','Control'),('MMT','Metro')
select *, REPLACE(c.cname, a.abbr,a.fname)
from #customer c
join #abbr a
on c.cname like '%'+a.abbr+'%'
where id = 1
Eu tenho duas tabelas - Pedidos e Produtos. Cada pedido pode ter 0 ou mais produtos.
--drop table orders
--drop table products
CREATE TABLE Orders (
OrderId INT PRIMARY KEY,
OrderDate DATE,
CustomerName VARCHAR(50)
);
CREATE TABLE Products (
ProductId INT PRIMARY KEY,
ProductName VARCHAR(50),
Price DECIMAL(10, 2),
OrderId INT,
FOREIGN KEY (OrderId) REFERENCES Orders(OrderId)
);
INSERT INTO Orders (OrderId, OrderDate, CustomerName)
VALUES (1, '2023-01-01', 'John Doe'),
(2, '2023-01-02', 'Jane Smith'),
(3, '2023-01-03', 'Michael Johnson'),
(4, '2023-01-04', 'Emily Davis'),
(5, '2023-01-05', 'Robert Wilson'),
(6, '2023-01-06', 'Olivia Thompson'),
(7, '2023-01-07', 'William Anderson'),
(8, '2023-01-08', 'Sophia Martinez'),
(9, '2023-01-09', 'James Hernandez'),
(10, '2023-01-10', 'Ava Lee'),
(11, '2023-01-11', 'Benjamin Walker'),
(12, '2023-01-12', 'Mia Lewis'),
(13, '2023-01-13', 'Ethan Hall'),
(14, '2023-01-14', 'Charlotte White'),
(15, '2023-01-15', 'Alexander King'),
(16, '2023-01-16', 'Amelia Scott'),
(17, '2023-01-17', 'Daniel Green'),
(18, '2023-01-18', 'Harper Adams'),
(19, '2023-01-19', 'Matthew Clark'),
(20, '2023-01-20', 'Luna Rodriguez'),
(21, '2023-01-21', 'Henry Carter'),
(22, '2023-01-22', 'Lily Hill'),
(23, '2023-01-23', 'Sebastian Turner'),
(24, '2023-01-24', 'Avery Ward'),
(25, '2023-01-25', 'Scarlett Foster'),
(26, '2023-01-26', 'Joseph Diaz'),
(27, '2023-01-27', 'Victoria Morris'),
(28, '2023-01-28', 'David Sullivan'),
(29, '2023-01-29', 'Penelope Price'),
(30, '2023-01-30', 'Jackson Brooks');
INSERT INTO Products (ProductId, ProductName, Price, OrderId)
VALUES
-- Products for OrderId 1
(1, 'Product A', 10.99, 1),
(2, 'Product B', 19.99, 1),
-- Products for OrderId 2
(3, 'Product C', 5.99, 2),
-- Products for OrderId 3
(4, 'Product D', 7.50, 3),
(5, 'Product E', 14.99, 3),
(6, 'Product F', 8.99, 3),
-- Products for OrderId 4
(7, 'Product G', 12.99, 4),
(8, 'Product H', 9.99, 4),
(9, 'Product I', 6.49, 4),
-- Products for OrderId 5
(10, 'Product J', 18.50, 5),
(11, 'Product K', 11.99, 5),
-- Products for OrderId 6
(12, 'Product L', 7.99, 6),
(13, 'Product M', 13.99, 6),
-- Products for OrderId 7
(14, 'Product N', 9.99, 7),
-- Products for OrderId 8
(15, 'Product O', 16.99, 8),
(16, 'Product P', 14.50, 8),
-- Products for OrderId 9
(17, 'Product Q', 8.99, 9),
(18, 'Product R', 6.99, 9),
-- Products for OrderId 10
(19, 'Product S', 12.99, 10),
-- Products for OrderId 11
(20, 'Product T', 7.50, 11),
-- Products for OrderId 12
(21, 'Product U', 9.99, 12),
(22, 'Product V', 11.99, 12),
-- Products for OrderId 13
(23, 'Product W', 15.99, 13),
(24, 'Product X', 8.50, 13),
-- Products for OrderId 14
(25, 'Product Y', 9.99, 14),
-- Products for OrderId 15
(26, 'Product Z', 12.99, 15),
(27, 'Product AA', 7.99, 15),
(28, 'Product BB', 14.99, 15),
-- Products for OrderId 16
(29, 'Product CC', 11.50, 16),
(30, 'Product DD', 9.99, 16);
Requisito:
Escreva uma consulta que filtre a customerNamecoluna da Orderstabela ou ProductNamea coluna da Productstabela com base no filtro passado na consulta e a ordene OrderDatee busque o número de pedidos solicitados. Aqui está a consulta que escrevi, mas não realiza o que estou procurando:
declare @filter varchar(100) = 't'
declare @skiprows int = 5
declare @limitrows int = 5
select *
from dbo.orders o
left join dbo.products p
on o.orderId = p.orderId
where (customerName like '%'+@filter+'%'
or productName like '%'+@filter+'%')
order by orderdate desc
offset @skiprows rows
fetch next @limitrows rows only
Resultado:
Os parâmetros @ limitrowse @ skiprowssão usados para determinar o conjunto de resultados desejado. No entanto, na consulta fornecida, pular as primeiras 5 linhas e retornar as próximas 5 linhas não fornece o resultado esperado. O objetivo é pular os primeiros 5 IDs de pedidos distintos e, em seguida, selecionar os próximos 5 IDs de pedidos distintos. Posteriormente, o conjunto de resultados deve ser unido à tabela Produtos para recuperar todos os produtos correspondentes. É importante observar que o conjunto de resultados final pode conter mais de 5 linhas, pois cada pedido pode ter vários produtos. Se alguém puder fornecer orientações sobre como fazer isso, seria muito apreciado. Obrigado!
Tenho a seguinte tabela e dados:
CREATE TABLE myTable (
ID INT IDENTITY(1,1) PRIMARY KEY,
Column1 VARCHAR(50),
Column2 VARCHAR(50),
Column3 VARCHAR(50),
Column4 VARCHAR(50),
Column5 VARCHAR(50),
Column6 VARCHAR(50),
Column7 VARCHAR(50),
Column8 VARCHAR(50),
Column9 VARCHAR(50),
Column10 VARCHAR(50)
)
DECLARE @i INT = 1
DECLARE @j INT = 1
DECLARE @distinct_value_count INT = 20
DECLARE @distinct_value_count_with_more_rows INT = 3
DECLARE @rows_per_distinct_value INT = (20000 - (@distinct_value_count_with_more_rows * 2000)) / (@distinct_value_count - @distinct_value_count_with_more_rows)
WHILE @i <= @distinct_value_count
BEGIN
DECLARE @current_rows_per_value INT = @rows_per_distinct_value
IF @i <= @distinct_value_count_with_more_rows
BEGIN
SET @current_rows_per_value = @rows_per_distinct_value + 2000
END
SET @j = 1
WHILE @j <= @current_rows_per_value
BEGIN
INSERT INTO myTable (Column1, Column2, Column3, Column4, Column5, Column6, Column7, Column8, Column9, Column10)
VALUES ('Value' + CAST(@i AS VARCHAR(2)),
'Value' + CAST(@j AS VARCHAR(5)),
'Value' + CAST(@j + 1 AS VARCHAR(5)),
'Value' + CAST(@j + 2 AS VARCHAR(5)),
'Value' + CAST(@j + 3 AS VARCHAR(5)),
'Value' + CAST(@j + 4 AS VARCHAR(5)),
'Value' + CAST(@j + 5 AS VARCHAR(5)),
'Value' + CAST(@j + 6 AS VARCHAR(5)),
'Value' + CAST(@j + 7 AS VARCHAR(5)),
'Value' + CAST(@j + 8 AS VARCHAR(5)))
SET @j = @j + 1
END
SET @i = @i + 1
END
Alter Table dbo.myTable
Add Column11 varchar(50), Column12 varchar(50)
Alter Table dbo.myTable
Add dateModified datetime
Update dbo.myTable
set Column11 = Column1
,Column12 = Column1
Update Top (10) dbo.myTable
Set Column11 = 'Value7'
Where Column1 = 'Value1'
Update Top (10) dbo.myTable
Set Column12 = 'Value7'
Where Column1 = 'Value1'
Update Top (10) dbo.myTable
Set Column11 = 'Value6'
Where Column1 = 'Value1'
Update Top (10) dbo.myTable
Set Column12 = 'Value6'
Where Column1 = 'Value1'
Update Top (10) dbo.myTable
Set Column11 = 'Value5'
Where Column1 = 'Value1'
Update Top (10) dbo.myTable
Set Column12 = 'Value5'
Where Column1 = 'Value1'
Update dbo.myTable
set dateModified = getdate() + ID
CREATE NONCLUSTERED INDEX [Idx_col] ON [dbo].[myTable]
(
[Column1] ASC,
[Column11] ASC,
[Column12] ASC,
[dateModified] ASC
)
INCLUDE([Column5],[Column6])
Tenho que filtrar com base em algumas colunas e retornar todas as colunas da tabela. Para fazer isso, tenho um índice que cobre as colunas que precisam ser filtradas. Estou dividindo a consulta em duas partes:
Obtenha todas as linhas de chave primária que satisfaçam o filtro e armazene-as em uma tabela temporária. Essa consulta usa o índice não clusterizado.
Junte esta tabela temporária de volta à tabela principal na coluna de chave primária para que o índice clusterizado seja usado para obter todas as colunas.
No entanto, estou enfrentando um problema quando tento fazer isso. No primeiro cenário, estou obtendo todas as linhas filtradas em uma tabela temporária e, quando a incluo de volta na tabela principal, está fazendo uma varredura de índice clusterizado. No segundo cenário, estou obtendo apenas as 50 primeiras linhas na tabela temporária e, quando as incluo na tabela principal, está fazendo uma busca de índice clusterizado. Estou confuso sobre por que isso está acontecendo. Em ambos os casos, não há índice na tabela temporária. Agradeço se alguém puder me ajudar a entender o que está acontecendo. Obrigado!
Cenário 1:
SELECT id
INTO #tmpId
FROM myTable
WHERE Column1= 'Value1'
AND Column11 In( 'Value1','Value5','Value6', 'Value7')
And Column12 In ('Value1','Value6')
And dateModified > dateAdd(day,-5, getdate())
SELECT *
FROM myTable m
JOIN #tmpId t
ON m.id = t.id
drop table if exists #tmpId
Plano de Execução Cenário 1: https://www.brentozar.com/pastetheplan/?id=rkDAD-aLh
Cenário 2:
SELECT id
INTO #tmpId
FROM myTable
WHERE Column1= 'Value1'
AND Column11 In( 'Value1','Value5','Value6', 'Value7')
And Column12 In ('Value1','Value6')
And dateModified > dateAdd(day,-5, getdate())
Order by dateModified desc offset 0 rows fetch next 50 rows only
SELECT *
FROM myTable m
JOIN #tmpId t
ON m.id = t.id
drop table if exists #tmpId
Plano de Execução do Cenário 2: https://www.brentozar.com/pastetheplan/?id=rJVbuWaLh
Eu tenho a seguinte tabela e dados nela. Eu tenho dois índices nele: um que contém apenas algumas colunas da cláusula where e o outro contém todas as colunas da cláusula where.
Estou confuso em relação ao comportamento observado ao utilizar diferentes índices em minha consulta. Especificamente, quando emprego o primeiro índice, que contém apenas algumas colunas da cláusula where, noto um baixo número de leituras lógicas, mas um alto número de linhas lidas. Por outro lado, quando uso o índice contendo todas as colunas da cláusula where, o número de leituras lógicas aumenta enquanto o número de linhas lidas diminui. No primeiro cenário, existem predicados residuais, fazendo com que o mecanismo verifique todas as linhas recuperadas da operação de busca. Porém, no segundo cenário, o motor busca diretamente os valores necessários. Diante disso, eu anteciparia um número menor de leituras lógicas no segundo caso. Eu apreciaria qualquer insight sobre por que isso está acontecendo. Obrigado!
CREATE TABLE myTable (
ID INT IDENTITY(1,1) PRIMARY KEY,
Column1 VARCHAR(50),
Column2 VARCHAR(50),
Column3 VARCHAR(50),
Column4 VARCHAR(50),
Column5 VARCHAR(50),
Column6 VARCHAR(50),
Column7 VARCHAR(50),
Column8 VARCHAR(50),
Column9 VARCHAR(50),
Column10 VARCHAR(50)
)
DECLARE @i INT = 1
DECLARE @j INT = 1
DECLARE @distinct_value_count INT = 20
DECLARE @distinct_value_count_with_more_rows INT = 3
DECLARE @rows_per_distinct_value INT = (20000 - (@distinct_value_count_with_more_rows * 2000)) / (@distinct_value_count - @distinct_value_count_with_more_rows)
WHILE @i <= @distinct_value_count
BEGIN
DECLARE @current_rows_per_value INT = @rows_per_distinct_value
IF @i <= @distinct_value_count_with_more_rows
BEGIN
SET @current_rows_per_value = @rows_per_distinct_value + 2000
END
SET @j = 1
WHILE @j <= @current_rows_per_value
BEGIN
INSERT INTO myTable (Column1, Column2, Column3, Column4, Column5, Column6, Column7, Column8, Column9, Column10)
VALUES ('Value' + CAST(@i AS VARCHAR(2)),
'Value' + CAST(@j AS VARCHAR(5)),
'Value' + CAST(@j + 1 AS VARCHAR(5)),
'Value' + CAST(@j + 2 AS VARCHAR(5)),
'Value' + CAST(@j + 3 AS VARCHAR(5)),
'Value' + CAST(@j + 4 AS VARCHAR(5)),
'Value' + CAST(@j + 5 AS VARCHAR(5)),
'Value' + CAST(@j + 6 AS VARCHAR(5)),
'Value' + CAST(@j + 7 AS VARCHAR(5)),
'Value' + CAST(@j + 8 AS VARCHAR(5)))
SET @j = @j + 1
END
SET @i = @i + 1
END
Alter Table dbo.myTable
Add Column11 varchar(50), Column12 varchar(50)
Alter Table dbo.myTable
Add dateModified datetime
Update dbo.myTable
set Column11 = Column1
,Column12 = Column1
Update Top (10) dbo.myTable
Set Column11 = 'Value7'
Where Column1 = 'Value1'
Update Top (10) dbo.myTable
Set Column12 = 'Value7'
Where Column1 = 'Value1'
Update Top (10) dbo.myTable
Set Column11 = 'Value6'
Where Column1 = 'Value1'
Update Top (10) dbo.myTable
Set Column12 = 'Value6'
Where Column1 = 'Value1'
Update Top (10) dbo.myTable
Set Column11 = 'Value5'
Where Column1 = 'Value1'
Update Top (10) dbo.myTable
Set Column12 = 'Value5'
Where Column1 = 'Value1'
Update dbo.myTable
set dateModified = getdate() + ID
CASO 1: LEITURA LÓGICA BAIXA, MAS O NÚMERO DE LINHAS É ALTO
Create NonClustered Index Idx_col1
On myTable(Column1, Column11)
Include (Column5, Column6,Column12, dateModified)
set Statistics io on
SELECT Column1
, Column11
, Column5
, Column6
, Column12
FROM myTable
WHERE Column1= 'Value1'
AND Column11 In( 'Value1','Value5','Value6', 'Value7')
And Column12 In ('Value1','Value6')
And dateModified > dateAdd(day,-5, getdate())
Plano de execução: https://www.brentozar.com/pastetheplan/?id=Sywz516Hh
Estatísticas de IO:
(2803 rows affected)
Table 'myTable'. Scan count 4, logical reads 34, physical reads 1, page server reads 0, read-ahead reads 23, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
(1 row affected)
CASO 2: LEITURA LÓGICA ALTA, MAS O NÚMERO DE LINHAS LIDAS É BAIXO
Create NonClustered Index Idx_col2 on myTable(Column1, Column11,Column12, dateModified)
Include (Column5, Column6)
set Statistics io on
SELECT Column1
, Column11
, Column5
, Column6
, Column12
FROM myTable
WHERE Column1= 'Value1'
AND Column11 In( 'Value1','Value5','Value6', 'Value7')
And Column12 In ('Value1','Value6')
And dateModified > dateAdd(day,-5, getdate())
Plano de Execução: https://www.brentozar.com/pastetheplan/?id=HyWbi1aSh
Estatísticas de IO:
(2803 rows affected)
Table 'myTable'. Scan count 8, logical reads 47, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Eu tenho uma tabela com uma coluna contendo objetos JSON. Dentro desses objetos, preciso localizar a chave typecom valor 3. Uma vez encontrada, preciso atualizar a chave pricecom um novo valor de 99 no Objeto JSON aninhado data. O preço para os outros tipos deve permanecer inalterado. Eu apreciaria muito qualquer ajuda. Obrigado!
-- Create the new table
CREATE TABLE ModifiedTable (
accountIdentifier UNIQUEIDENTIFIER,
settings NVARCHAR(MAX)
);
-- Insert the modified values
INSERT INTO ModifiedTable (accountIdentifier, settings)
Values (
'8E9B45D7-8AEC-EA11-8B03-000D3A12F259',
'[{"type":3,"data":{"required":false,"price":0.5,"display_name":false}},{"type":5,"data":{"required":true,"scaling_factor":2.5, "price":1,"date_format":"yyyy-MM-dd"}}]'
),
(
'C03D12B1-8BEC-EA11-8B03-000D3A12F259',
'[{"type":7,"data":{"required":true,"scaling_factor":1.75,"tooltip":"Sample tooltip", "price":1}},{"type":4,"data":{"required":false,"scaling_factor":1.2,"char_limit":50,"multi_line":true}},{"type":3,"data":{"required":false,"price":0.7,"display_name":false}}]'
);
-- Select from the modified table
SELECT * FROM ModifiedTable;
--drop table ModifiedTable
Eu tenho um procedimento armazenado que está produzindo planos ligeiramente diferentes para parâmetros diferentes. Eu esperaria que ele usasse o mesmo plano que foi gerado quando foi executado pela primeira vez.
CREATE TABLE myTable (
ID INT IDENTITY(1,1) PRIMARY KEY,
Column1 VARCHAR(50),
Column2 VARCHAR(50),
Column3 VARCHAR(50),
Column4 VARCHAR(50),
Column5 VARCHAR(50),
Column6 VARCHAR(50),
Column7 VARCHAR(50),
Column8 VARCHAR(50),
Column9 VARCHAR(50),
Column10 VARCHAR(50)
)
DECLARE @i INT = 1
DECLARE @j INT = 1
DECLARE @distinct_value_count INT = 20
DECLARE @distinct_value_count_with_more_rows INT = 3
DECLARE @rows_per_distinct_value INT = (20000 - (@distinct_value_count_with_more_rows * 2000)) / (@distinct_value_count - @distinct_value_count_with_more_rows)
WHILE @i <= @distinct_value_count
BEGIN
DECLARE @current_rows_per_value INT = @rows_per_distinct_value
IF @i <= @distinct_value_count_with_more_rows
BEGIN
SET @current_rows_per_value = @rows_per_distinct_value + 2000
END
SET @j = 1
WHILE @j <= @current_rows_per_value
BEGIN
INSERT INTO myTable (Column1, Column2, Column3, Column4, Column5, Column6, Column7, Column8, Column9, Column10)
VALUES ('Value' + CAST(@i AS VARCHAR(2)),
'Value' + CAST(@j AS VARCHAR(5)),
'Value' + CAST(@j + 1 AS VARCHAR(5)),
'Value' + CAST(@j + 2 AS VARCHAR(5)),
'Value' + CAST(@j + 3 AS VARCHAR(5)),
'Value' + CAST(@j + 4 AS VARCHAR(5)),
'Value' + CAST(@j + 5 AS VARCHAR(5)),
'Value' + CAST(@j + 6 AS VARCHAR(5)),
'Value' + CAST(@j + 7 AS VARCHAR(5)),
'Value' + CAST(@j + 8 AS VARCHAR(5)))
SET @j = @j + 1
END
SET @i = @i + 1
END
Create NonClustered Index Idx_col on MyTable(Column1)
sp_recompile 'dbo.MyTable'
Create or Alter Procedure dbo.tmp_testProc(
@inValue VarChar(50)
)
As
Begin
Set NoCount On
Select Id
Into #tmpCol
From MyTable
Where Column1 = @inValue
Select t.*
From MyTable t
Join #tmpCol tmp
On t.Id = tmp.Id
Order by t.Id
End
--Scenario 1
exec dbo.tmp_testProc @inValue = 'Value1'
--Scenario 2
exec dbo.tmp_testProc @inValue = 'Value5'
Estou executando o procedimento armazenado com valor de parâmetro = 'Value1'para a primeira execução e depois com 'Value5'. Aqui estão os planos de execução para ambos os cenários:
Plano de Execução do Cenário 1 : https://www.brentozar.com/pastetheplan/?id=r1H-3HONh
Plano de Execução do Cenário 2 : https://www.brentozar.com/pastetheplan/?id=r1p42SOE3
Previ que o segundo cenário empregaria o mesmo plano do primeiro, mas observei que o plano é diferente. Fiquei com a impressão de que o otimizador reutiliza o mesmo plano gerado para o parâmetro inicial definido para todas as execuções subsequentes. Portanto, não tenho certeza do motivo pelo qual o otimizador está criando planos diferentes nessa instância. Agradeceria qualquer esclarecimento sobre este assunto. Obrigado pela sua ajuda.
Tenho uma tabela com 150 milhões de registros e 8 índices. Um dos índices está ativado userId. Muitas das consultas atuais são filtradas userIde, portanto, esse índice funciona perfeitamente. Para uma das consultas, tenho que expandir a cláusula where para incluir colunas regionIde . productIdTenho duas opções: posso criar um novo índice com (userId, regionId, productId)ou adicionar essas novas colunas ao índice existente. Qual seria uma opção melhor? Se eu modificar o índice existente, isso afetará as outras consultas que já usam userId?
Meu objetivo:
quero que minha consulta atual que expande a cláusula where seja incluída RegionIde productIdexecutada mais rapidamente, mas não quero que minhas outras consultas que tenham apenas userIda cláusula where sejam afetadas. Se eu adicionar um novo índice, (userid, regional, productid)posso fazer isso, mas não tenho certeza se estou duplicando o índice, pois já tenho um índice existente no userIdqual posso expandir para incluir regionide productId. Mas não tenho certeza sobre as consequências de modificar o índice existente.
Obrigado pelo seu tempo!
Tenho três tabelas: product, users, and orders
product: lista de todos os produtos e seus status. Esta tabela tem um campo chamado previousProductId. O código de um produto pode ser alterado várias vezes e, se for, previousProductIdconterá o código do predecessor imediato. Por exemplo, se um produto tem o código 1057 e então uma nova linha é adicionada para este produto com um novo código 1060 então o previousLineId terá um código 1057. Isso se repete toda vez que uma nova linha com um novo código é adicionada para um produto existente.
users: lista de todos os usuários
orders: lista de todos os pedidos feitos pelos usuários
Agora tenho que escrever uma consulta para obter todos os pedidos com produtos ativos. Preciso garantir que todos os usuários que solicitaram as versões anteriores de um produto ativo também sejam incluídos no conjunto de resultados. Quero usar o CTE recursivo para subir na cadeia para recuperar todos os IDs de produtos anteriores de um determinado produto e recuperar as informações necessárias dessa maneira, mas esse método parece um pouco volumoso. Eu queria saber se existe uma maneira melhor e mais simples de fazer isso. Obrigado!
Create table #product
(
productId Int primary key Identity(1,1),
name varchar(100),
price numeric(18,5),
previousProductId Int,
active bit
)
Create table #users
(
userId Int Primary key,
name varchar(100)
)
Create table #orders
(
orderId Int primary key,
userId Int,
productId Int
)
Insert Into #product values ('Sony TV', 200.23, null, 0), ('Sony Tv New', 240.43, 1, 0), ('Apple Watch', 300.45, null, 1)
,('Samsung Mobile',1050, null,0),('Sony TV Advanced', 400, 2,1)
Insert Into #users values (1,'John'), (2,'Mary'), (3,'Kevin'), (4,'Joe'), (5,'Andy'),(6,'Jim'),(7,'Pam')
Insert Into #orders values (1, 1, 1), (2, 2, 1),(3, 3,2), (4,4,3), (5,5,3),(6,6,4),(7,7,5)
select t_p.productId, t_p.name, t_p.price, t_p.previousProductId, t_p.active
,t_u.name as username, t_o.orderId
from #product t_p
join #orders t_o
on t_p.productId = t_o.productId
join #users t_u
on t_u.userID = t_o.userId
where t_p.active = 1
drop table if exists #product
drop table if exists #orders
drop table if exists #users
Resultado esperado:
Temos um procedimento armazenado que usa SUMuma coluna Int na SELECTinstrução. Temos alguns casos em que, para um dos valores de parâmetro, a SUM (coluna inteira) excede o intervalo Int e, portanto, gera um erro
Erro de estouro aritmético ao converter expressão em tipo de dados int
A solução para isso seria converter a coluna em bigint na função sum, ou seja, Sum(Convert(BigInt, column)). Mas como isso está acontecendo para alguns casos específicos neste momento e não para todos os casos, estou preocupado em acabar penalizando os outros casos em que essa conversão não é necessária. Quanta diferença de desempenho a conversão costuma fazer? Existe alguma outra maneira de lidar com esse problema? Obrigado!
Eu tenho um procedimento armazenado usp_appdataque é usado pela tela de carregamento do aplicativo, bem como por vários outros locais do aplicativo. Este SP consome muitos recursos e retorna muitos dados. Estou planejando fazer uma cópia deste procedimento armazenado usp_appdata_initiale usá-lo apenas para a tela de carregamento do aplicativo e usar o sp original usp_appdatapara todas as outras chamadas. Dividir o mesmo procedimento em vários procedimentos e usar cada um deles para uma finalidade específica oferece algum benefício de desempenho? Obrigado!
Considere o seguinte cenário:
- 10.000 usuários tentam criar um perfil em nosso aplicativo simultaneamente ao mesmo tempo e os dados devem ser inseridos na tabela, userProfile.
- O SP que lida com essa inserção leva meio segundo para ser executado.
Do meu entendimento de bloqueio e transações, esta tabela será bloqueada para cada inserção. Isso significa que, quando o perfil do último usuário for criado, levará um tempo. Como o SQL Server lida com atualizações e bloqueios simultâneos? Eu apreciaria qualquer insight sobre isso. Obrigado!
Eu tenho uma situação em que tenho que decidir entre receber XML como um parâmetro de entrada para o procedimento armazenado ou uma lista de valores separados por vírgula e analisá-los usando uma função com valor de várias tabelas.
Logicamente falando, eu preferiria XML em vez de udfs porque udfs podem causar muitos problemas de desempenho. Mas vejo que o XML no back-end também usa funções com valor de tabela para analisar o xml. Eu queria saber se o XML é realmente melhor do que o UDF de várias tabelas e, em caso afirmativo, o que o torna melhor?
CREATE FUNCTION [dbo].[tmp_array_commaValues] ( @string varchar(4000))
RETURNS @final TABLE(Value varchar(100))
AS
begin
WITH tmp_cte(start, stop) AS
(
SELECT 1, CHARINDEX(',' , @string )
UNION ALL
SELECT stop + 1, CHARINDEX(',' ,@string , stop + 1)
FROM tmp_cte
WHERE stop > 0
)
insert into @final
SELECT SUBSTRING(@string , start, CASE WHEN stop > 0 THEN stop-start ELSE 4000 END) AS stringValue
FROM tmp_cte
return
end
/* query */
Select value
From [dbo].[tmp_array_commaValues]('TAG,ABC,ZYX,BAG,TRY,LAG,LEAD,NETHERLANDS,TAG1,ABC1,ZYX1,BAG1,TRY1,SUN12,NANGE1')
/********** XML *********/
Declare @xml XML = '<root><id>Tag</id><id>Lag</id>
<id>Tag1</id><id>Lag1</id>
<id>Tag2</id><id>Lag2</id>
<id>Tag3</id><id>Lag3</id>
<id>Tag4</id><id>Lag4</id>
<id>Tag5</id><id>Lag5</id>
<id>Tag6</id><id>Lag6</id>
<id>Tag7</id><id>Lag7</id>
</root>'
select t.c.value('.','varchar(100)') as string
From @xml.nodes('root/id/text()') As t(c)
Plano de execução para UDF: https://www.brentozar.com/pastetheplan/?id=SJpX2DMwi
Plano de execução para XML: https://www.brentozar.com/pastetheplan/?id=ByQv2PMvo
Tenho 3 tabelas:
test_productInfo: contém informações sobre o produto
test_productCreator: contém todos os usuários do nosso sistema
test_productOwner: contém informações sobre quem é o proprietário de um(s) produto(s). Uma tabela de referência cruzada entre test_productInfo e test_productCreator.
Meu procedimento armazenado leva um productCreatorId ou vários productCreatorIds e uma chave de classificação e retorna as primeiras 1.000 linhas de test_productInfo(todas as colunas) classificadas pela chave de classificação passada. Infelizmente, tenho que selecionar tudo.
Colei a consulta atual que temos abaixo e aqui está o plano de execução: https://www.brentozar.com/pastetheplan/?id=SyKgEEVNj
Estou recebendo dados de 20 usuários. A consulta atual tem classificação transbordando em tempdb.
Adicionei um novo índice não clusterizado em productId, que é a chave de clustering, e incluí todas as chaves de classificação. Modifiquei a consulta para selecionar apenas productId ao classificar para que esse novo índice possa ser usado e armazenei os resultados em uma tabela temporária. Em uma consulta subsequente estou adicionando todas as colunas necessárias e fazendo uma classificação novamente. Esta consulta funciona bem para usuários que têm mais de 1.000 linhas porque não estou classificando mais de 1.000 linhas quando todas as colunas estão sendo selecionadas. Para usuários com menos de 1000 linhas, estou acessando a tabela productInfo duas vezes, mas não observei muita diferença no tempo entre minha consulta atual e a consulta modificada. Aqui está o plano de execução para a consulta modificada: https://www.brentozar.com/pastetheplan/?id=SyA_SVEVs
Com a nova consulta modificada, estou tentando reduzir os derramamentos de classificação e estou vendo algumas melhorias, especialmente quando há mais de 1000 linhas.
Faz sentido criar um índice não clusterizado em uma chave de cluster com algumas colunas incluídas? existe uma maneira melhor de reescrever minha consulta atual?
--drop table test_productInfo
--drop table test_productOwner
--drop table test_productCreator
Create Table test_productInfo
(
productId uniqueIdentifier Primary Key,
productName varchar(100),
productRegion varchar(100),
productCreatedDt Datetime,
productUpdatedDt Datetime,
productStatus varchar(100),
isProductsold bit,
productLineNumber Int,
productLineId uniqueIdentifier,
productAddress varchar(100),
productAddress2 varchar(100),
productCity varchar(100),
productState varchar(100),
productCountry varchar(100),
productZip Varchar(100),
productLatitude varchar(100),
productLongitude varchar(100)
)
Create Table test_productOwner
(
productOwnerId Int Identity(1,1),
productId uniqueIdentifier,
productCreatorId uniqueIdentifier,
Createddate datetime,
modifieddate datetime
)
Create Table test_productCreator
(
productCreatorId uniqueIdentifier primary key,
productCreatorName varchar(100),
createddate datetime,
modifieddate datetime
)
ALTER TABLE test_productOwner ADD CONSTRAINT PK_test_productOwner PRIMARY KEY NONCLUSTERED(productOwnerId)
create clustered index ix_test_productOwner_productId On dbo.test_productOwner(productId)
create nonclustered index ix_test_productOwner_test_productCreatorId On dbo.test_productOwner(productCreatorId)
/* Insert data */
Declare @i int = 1, @j int = 1
Declare @newIdi uniqueidentifier, @newIdj uniqueidentifier
While @i < 100
Begin
set @newIdi = newid()
Insert Into dbo.test_productCreator values (@newIdi, 'ABC'+convert(varchar(10),@i), getdate(),getdate())
while @j <= 2300
Begin
set @newIdj = newId()
Insert Into dbo.test_productInfo values (@newIdj, 'ProductName'+convert(varchar(10),@i)+convert(varchar(10),@j), 'Region1',getdate(),getdate(),'completed', 1, @i,newId()
,'Address1'+convert(varchar(10),@i)+convert(varchar(10),@j), 'Address2'+convert(varchar(10),@i)+convert(varchar(10),@j), 'los angeles','ca','USA','22231','26.45','33.23')
insert into dbo.test_productOwner values (@newIdj, @newIdi, getdate(), getdate())
set @j = @j + 1
End
set @i = @i + 1
set @j = 1
End
**CURRENT Query:**
set statistics io on
Declare @sort varchar(100) = 'PRODMODIFIED'
Declare @PageNumber int = 1, @PageSize int = 1000
Declare @prodCreator Table(productCreatorId Uniqueidentifier)
insert into @prodCreator
select top 10 productCreatorId
from dbo.test_productCreator
Select p_pi.*
from test_productInfo p_pi
join test_productowner p_po
on p_pi.productId = p_po.productId
join @prodcreator p_pc
on p_po.productCreatorId = p_pc.productCreatorId
order by case when @sort = 'PRODMODIFIED' then p_pi.productUpdatedDt
when @sort = 'PRODCREATED' then p_pi.productCreatedDt
when @sort = 'PRODNAME' then p_pi.productName
end
OFFSET @PageSize * (@PageNumber - 1) ROWS
FETCH NEXT @PageSize ROWS ONLY
**MODIFIED Query:**
create nonclustered index idx_non_productInfo on dbo.productInfo(productId) include(productUpdatedDt, productCreatedDt, productName)
set statistics io on
Declare @sort varchar(100) = 'PRODMODIFIED'
Declare @PageNumber int = 1, @PageSize int = 1000
Declare @prodCreator Table(productCreatorId Uniqueidentifier)
Create Table #tmpProduct (productId uniqueidentifier)
insert into @prodCreator
select top 10 productCreatorId
from dbo.test_productCreator
Insert Into #tmpProduct
Select p_pi.productId
from test_productInfo p_pi
join test_productowner p_po
on p_pi.productId = p_po.productId
join @prodcreator p_pc
on p_po.productCreatorId = p_pc.productCreatorId
order by case when @sort = 'PRODMODIFIED' then p_pi.productUpdatedDt
when @sort = 'PRODCREATED' then p_pi.productCreatedDt
when @sort = 'PRODNAME' then p_pi.productName
end
OFFSET @PageSize * (@PageNumber - 1) ROWS
FETCH NEXT @PageSize ROWS ONLY
Select p_pi.*
from #tmpProduct tmp_pi
join test_productInfo p_pi
on tmp_pi.productId = p_pi.productId
join test_productowner p_po
on p_pi.productId = p_po.productId
join @prodcreator p_pc
on p_po.productCreatorId = p_pc.productCreatorId
order by case when @sort = 'PRODMODIFIED' then p_pi.productUpdatedDt
when @sort = 'PRODCREATED' then p_pi.productCreatedDt
when @sort = 'PRODNAME' then p_pi.productName
end
If object_id('tempdb..#tmpProduct') is not null
begin
drop table #tmpProduct
end