SQL Server - Como as páginas de dados são armazenadas ao usar um índice clusterizado

Question

Bob Klimes

Asked: 2018-01-10 11:37:06 +0800 CST2018-01-10 11:37:06 +0800 CST 2018-01-10 11:37:06 +0800 CST

Dividir duas strings delimitadas na mesma ordem sem função

772

Estou tentando dividir duas colunas com strings delimitadas em linhas. As posições dos valores em cada string estão relacionadas, então estou tentando dividi-lo para que os valores relacionados estejam em uma linha. Não consigo usar a função porque não consigo criar objetos no banco de dados

Aqui está a tabela e os dados de amostra

CREATE TABLE #temp
(id   INT,
 keys VARCHAR(50),
 vals VARCHAR(50)
);

INSERT INTO #temp
VALUES
(1, '1,2,3', 'one,two,three'),
(2, '4,5,6', 'four,five,six'),
(3, '7,8,9', 'seven,eight,nine');

e minha saída desejada seria

ID  key  val
1   1    one
1   2    two
1   3    three
2   4    four
2   5    five
2   6    six
3   7    seven
3   8    eight
3   9    nine

Consegui que a consulta funcionasse se eu dividisse apenas uma coluna, então defino dois CTEs com row_number e uno em ID e row_number. Isso fornece a saída desejada, mas minha tabela ao vivo é muito grande e eu esperava uma maneira de passar pela tabela apenas uma vez, em vez de duas.

with keys as(
SELECT id,keys,vals,
       keys.keyid.value('.', 'VARCHAR(8000)') AS keyid,
      row_number() over(order by (select null)) as rn
FROM
(SELECT id,keys,vals,
           CAST('<Keys><key>'+REPLACE(keys, ',', '</key><key>')+'</key></Keys>' AS XML) AS tempkeys
    FROM #temp
) AS temp
CROSS APPLY tempkeys.nodes('/Keys/key') AS keys(keyid)),
vals as(
SELECT id,keys,vals,
       vals.val.value('.', 'VARCHAR(8000)') AS valid,
      row_number() over(order by (select null)) as rn
FROM
(SELECT id,keys,vals,
           CAST('<vals><val>'+REPLACE(vals, ',', '</val><val>')+'</val></vals>' AS XML) AS tempvals
    FROM #temp
) AS temp
CROSS APPLY tempvals.nodes('/vals/val') AS vals(val))


SELECT k.id, k.keyid, v.valid
FROM keys AS k
     INNER JOIN vals AS v
     ON k.id = v.id
        AND k.rn = v.rn;

2 respostas

Voted

Aaron Bertrand · Answer 1 · 2018-01-10T12:51:00+08:00

Crie a função em msdbou em outro lugar.

CREATE FUNCTION dbo.SplitTwoStringsWithSameOrder
(
    @List1  varchar(50),
    @List2  varchar(50),
    @Delim  varchar(10)
)
RETURNS TABLE
AS
    RETURN
    (
      WITH src(r) AS 
      (
        SELECT 1 UNION ALL SELECT r + 1 FROM src WHERE r < 10
      ),
      Numbers(Number) AS 
      (
        SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) 
        FROM src AS s1, src AS s2 -- add more if you need longer strings
      ),
      parsed(s1,s2,r1,r2)
      AS
      (
        SELECT
          SUBSTRING(@List1, n1.Number, CHARINDEX(@Delim, @List1 
            + @Delim, n1.Number) - n1.Number),
          SUBSTRING(@List2, n2.Number, CHARINDEX(@Delim, @List2 
            + @Delim, n2.Number) - n2.Number),
          r1 = ROW_NUMBER() OVER (ORDER BY n1.Number),
          r2 = ROW_NUMBER() OVER (ORDER BY n2.Number)
        FROM Numbers AS n1, Numbers AS n2
        ON  n1.Number <= LEN(@List1)
        AND n2.Number <= LEN(@List2)
        AND SUBSTRING(@Delim + @List1, n1.Number, LEN(@Delim)) = @Delim
        AND SUBSTRING(@Delim + @List2, n2.Number, LEN(@Delim)) = @Delim
      )
      SELECT s1, s2, r1, r2 FROM parsed WHERE r1 = r2
    );

Em seguida, como @gbn observou, faça referência a ele pelo nome de 3 partes sempre que sua consulta tiver que ser executada.

CREATE TABLE #temp
(id   INT,
 keys VARCHAR(50),
 vals VARCHAR(50)
);

INSERT INTO #temp
VALUES
(1, '1,2,3', 'one,two,three'),
(2, '4,5,6', 'four,five,six'),
(3, '7,8,9', 'seven,eight,nine');

SELECT t.id, f.s1, f.s2 FROM #temp AS t
  CROSS APPLY msdb.dbo.SplitTwoStringsWithSameOrder(keys, vals, ',') AS f
  ORDER BY t.id, f.r1;
GO

DROP TABLE #temp;

Resultados:

O plano resultante, mostrado no Plan Explorer (disclaimer: I'm the Product Manager) , não é a coisa mais bonita que já vi ( clique para ampliar um pouco ):

Mas há exatamente uma varredura de #temp (custo de 4%). Os maiores custos são dois tipos e um carretel, e há alguma E/S devido a uma mesa de trabalho que não tenho certeza se é evitável.

Se você SABE que terá apenas 50 caracteres em qualquer uma dessas strings, então você poderá obter um plano muito mais simples com uma Numberstabela embutida (as pessoas se opõem a isso, mas são muito úteis e estão quase sempre na memória se você as referenciar o suficiente). Isso não ajuda na E/S, mas remover o CTE recursivo e outras construções de construir os números dentro da função é bastante útil para a CPU etc.

Primeiro, a tabela de números:

DROP TABLE dbo.Numbers;

;WITH n AS
(
    SELECT
        TOP (50) rn = ROW_NUMBER() OVER
        (ORDER BY [object_id])
    FROM sys.all_columns 
    ORDER BY [object_id]
)
SELECT [Number] = rn - 1
INTO dbo.Numbers
FROM n;

CREATE UNIQUE CLUSTERED INDEX n ON dbo.Numbers([Number]);

Em seguida, uma segunda versão da função:

CREATE FUNCTION dbo.SplitTwoStringsWithSameOrder2
(
    @List1  varchar(50),
    @List2  varchar(50),
    @Delim  nvarchar(10)
)
RETURNS TABLE
AS
    RETURN
    (
      WITH parsed(s1,s2,r1,r2)
      AS
      (
        SELECT
          SUBSTRING(@List1, n1.Number, CHARINDEX(@Delim, @List1 
            + @Delim, n1.Number) - n1.Number),
          SUBSTRING(@List2, n2.Number, CHARINDEX(@Delim, @List2 
            + @Delim, n2.Number) - n2.Number),
          r1 = ROW_NUMBER() OVER (ORDER BY n1.Number),
          r2 = ROW_NUMBER() OVER (ORDER BY n2.Number)
        FROM dbo.Numbers AS n1
        INNER JOIN dbo.Numbers AS n2
        ON  n1.Number <= LEN(@List1)
        AND n2.Number <= LEN(@List2)
        AND SUBSTRING(@Delim + @List1, n1.Number, LEN(@Delim)) = @Delim
        AND SUBSTRING(@Delim + @List2, n2.Number, LEN(@Delim)) = @Delim
      )
      SELECT s1, s2, r1, r2 FROM parsed WHERE r1 = r2
    );
GO

Aqui está o plano mais simples que resulta (novamente, clique para ampliar ):

O plano ainda tem duas operações de classificação, mas o spool se foi, ainda há apenas uma varredura de #tempe, em meus testes limitados, os números de custo (números de custo absolutos, não %) foram sempre melhores.

Eu não sei exatamente qualquer uma delas será dimensionada com muito mais linhas, mas vale a pena testar, e se você comparar isso com outras soluções e não puder dimensionar bem, talvez seja um ponto que você reconsidere o design (armazene essas relacionalmente em vez de como conjuntos separados por vírgulas).

Filipe Fernando Souza · Answer 2 · 2020-11-24T07:54:54+08:00

Eu entrei no mesmo problema com um grande número de linhas e quatro colunas de lista.

As soluções anteriores não funcionam para mim.

A solução de @AaronBertrand tem um problema com um número diferente de elementos na lista. O problema pode ser resolvido adicionando uma partição no ROW_NUMBER:

r1 = ROW_NUMBER() OVER (PARTITION BY n2.Number ORDER BY n1.Number)

No entanto, ainda não treino para mim por causa do meu grande número de linhas e elementos.

Eu criei o seguinte script para resolver meu problema sem usar uma função:

DROP TABLE IF EXISTS #temp

CREATE TABLE #temp
(
    id   INT,
    keys VARCHAR(4000),
    vals VARCHAR(4000)
);

INSERT INTO #temp
VALUES
(1, '1,2,3', 'one,two,three'),
(2, '4,5,6', 'four,five,six'),
(3, '7,8,9', 'seven,eight,nine');

DECLARE @delim VARCHAR(4000) = ',';

WITH split AS (
    SELECT
        id
        ,CONVERT(VARCHAR(4000), CONCAT(keys, @delim)) AS keys
        ,CONVERT(VARCHAR(4000), CONCAT(vals, @delim)) AS vals
        ,1 AS iniciokeys
        ,COALESCE(NULLIF(CHARINDEX(@delim, keys, 1), 0), LEN(keys)) AS fimkeys
        ,CONVERT(VARCHAR(4000), RTRIM(LTRIM(SUBSTRING(keys, 1, COALESCE(NULLIF(CHARINDEX(@delim, keys, 1), 0), LEN(keys)) - 1)))) AS vkeys
        ,1 AS iniciovals
        ,COALESCE(NULLIF(CHARINDEX(@delim, vals, 1), 0), LEN(vals)) AS fimvals
        ,CONVERT(VARCHAR(4000), RTRIM(LTRIM(SUBSTRING(vals, 1, COALESCE(NULLIF(CHARINDEX(@delim, vals, 1), 0), LEN(vals)) - 1)))) AS vvals
    FROM #temp
    WHERE LEN(keys) > 0
        AND LEN(vals) > 0
    UNION ALL
    SELECT
        id
        ,CONVERT(VARCHAR(4000), keys) AS keys
        ,CONVERT(VARCHAR(4000), vals) AS vals
        ,CONVERT(INT, fimkeys) + 1 AS iniciokeys
        ,COALESCE(NULLIF(CHARINDEX(@delim, keys, fimkeys + 1), 0), LEN(keys)) AS fimkeys
        ,CONVERT(VARCHAR(4000), RTRIM(LTRIM(SUBSTRING(keys, fimkeys + 1, COALESCE(NULLIF(CHARINDEX(@delim, keys, fimkeys + 1), 0), LEN(keys))-fimkeys-1)))) AS vkeys
        ,CONVERT(INT, fimvals) + 1 AS iniciovals
        ,COALESCE(NULLIF(CHARINDEX(@delim, vals, fimvals + 1), 0), LEN(vals)) AS fimvals
        ,CONVERT(VARCHAR(4000), RTRIM(LTRIM(SUBSTRING(vals, fimvals + 1, COALESCE(NULLIF(CHARINDEX(@delim, vals, fimvals + 1), 0), LEN(vals))-fimvals-1)))) AS vvals
    FROM split
    WHERE fimkeys < LEN(keys)
        AND fimvals < LEN(vals)
)
SELECT
    id
    ,vkeys
    ,vvals
FROM split
ORDER BY id
    ,vkeys
OPTION(MAXRECURSION 32767)

Resultados:

Plano de consulta:

Como você pode ver, o plano de consulta é muito simples e possui apenas uma varredura de tabela em #temp.

A solução também é bastante escalável.

Dividir duas strings delimitadas na mesma ordem sem função

conectar ao servidor PostgreSQL: FATAL: nenhuma entrada pg_hba.conf para o host

Como fazer a saída do sqlplus aparecer em uma linha?

Selecione qual tem data máxima ou data mais recente

Como faço para listar todos os esquemas no PostgreSQL?

Listar todas as colunas de uma tabela especificada

Como usar o sqlplus para se conectar a um banco de dados Oracle localizado em outro host sem modificar meu próprio tnsnames.ora

Como você mysqldump tabela (s) específica (s)?

Listar os privilégios do banco de dados usando o psql

Como inserir valores em uma tabela de uma consulta de seleção no PostgreSQL?

Como faço para listar todos os bancos de dados e tabelas usando o psql?

Dividir duas strings delimitadas na mesma ordem sem função

2 respostas

relate perguntas