Qual é a melhor abordagem para analisar uma grande quantidade de strings?

400 milhões de nomes é muito. Eu estou lá? ;-)

Meu pressentimento diz que usar substring não será muito mais lento do que codificar algo por meio do CLR. Sou um cara do SQL, fiz uma boa quantidade de análises simples no passado (2000 ou 2005) e estava envolvido no que seria um esquema de análise muito complicado (endereços, em todo o mundo) escrito em c e chamado por meio de um xproc até descobrirmos que o código "nativo" do protótipo não era mais rápido do que a mesma coisa escrita com funções tsql.

Se você quiser usar uma linguagem diferente de tsql, sugiro escrever um CLR em c# ou vb.net. Para coisas simples, não é difícil codificar em CLR. Passei de newb para ter alguns diretórios de trabalho e utilitários de arquivo em menos de uma manhã. Existem muitos exemplos de procedimentos clr simples na rede. e você não terá que aprender nada (ou instalar o visual studio) para escrevê-lo em tsql

Não importa o que aconteça, você terá que passar pela mesa pelo menos uma vez. Se você exportar, analisar e colocar de volta o que não é uma pequena quantidade de dados, isso é muito tempo. Você pode garantir que sua fonte não vai mudar nesse meio tempo?

Aqui está o que sempre parece surpreender a todos: o que acontece com os dados analisados? Onde isso acaba? Você pretende atualizar a linha, talvez haja colunas de sobrenome e nome que você não mostra em seu exemplo?

Se você fizer isso, e essas colunas forem nulas ou tiverem cadeias de comprimento zero nelas, você pode descobrir que o desempenho da instrução de atualização é muito ruim porque o sql pode ter que dividir as páginas para armazenar o sobrenome.

Em outras palavras, seu problema de desempenho não está analisando, mas armazenando os dados analisados. Muitas vezes, isso é pior do que inserir os dados em outra tabela. Além disso, todas essas divisões de página fragmentarão sua tabela e farão com que o desempenho da consulta caia, o que pode enfurecer seu dba porque ele terá que executar um procedimento de desfragmentação na tabela (grande).

Aqui está um último pensamento: você realmente precisa armazenar os dados analisados? Você pode se safar com uma coluna computada que calcula o sobrenome na hora? Esses são indexáveis, com certas condições , se você precisar disso. Outra abordagem seria uma exibição que expõe as colunas da tabela, bem como sua "coluna de sobrenome analisada".

Pode ser mais rápido escrever um pequeno script em outra linguagem como c# por exemplo, para extrair todos os dados da tabela e manipulá-los.

Você pode fazer o que quiser com os dados, enviando-os para outro lugar ou de volta ao banco de dados usando o BCP.

Se você tiver acesso a um conjunto de utilitários de shell unix (consulte gnu win32 se precisar obter alguns que sejam executados no Windows), poderá exportar a coluna TheNamee processá-la com um pipeline de shell nas linhas de:

cut -d, -f2 | sed 's/ //g' | sort | uniq

O processo de exportação será bastante caro, no entanto. No SQL Server, você pode criar uma tabela temporária com os sobrenomes usando uma consulta nas linhas de:

select distinct rtrim(ltrim (substring (Name, charindex (',', TheName) + 1, 100)))
  into #DistinctSurnames
  from TheNameTable

De qualquer maneira que você olhe, você está pronto para uma varredura de mesa. A exportação provavelmente seria mais cara do que o processamento distinto na consulta, portanto, suponho que a consulta seria mais rápida.

Tenho aprendido muito sobre o processamento do SQL Server e pensei em tentar isso com um Cursor. Meu processo de pensamento é criar outro banco de dados (talvez até outra instância) para que você possa truncar os logs conforme necessário, já que este é um banco de dados de "relatórios" e você deseja limitar a interação com os dados "Ativos".

O cursor seria uma boa ideia, na minha opinião, porque você pode fazer o processamento à medida que avança e, teoricamente, em uma única passagem. Não seria muito difícil

• Adicione um campo de contagem à tabela LastName, por exemplo, para contar ocorrências de nomes (nome, contagem).
• Adicione um contador de loop que limpe o log de transações após 1 milhão de loops.
• Adicione um divisor que divide os nomes em tabelas individuais (LastNameA, LastNameB,... LastNameOther, Invalid)

Merge é uma construção de 2008+, então isso funcionaria, afaik, se você criasse outra instância e vinculasse a 2000/2005 se não estivesse em 2008+:

/*
USE Master;
IF EXISTS(SELECT name FROM sys.databases WHERE name = 'Original') DROP Database Original;
GO

CREATE DATABASE Original;
GO

USE Original;
GO

IF OBJECT_ID('TheNameTable','U') is not null DROP Table TheNameTable;
GO
Create Table TheNameTable (NameID INT Primary Key Identity, TheName varchar(500));
GO
INSERT INTO TheNameTable Values('John, Wayne');
INSERT INTO TheNameTable Values('Wayne, Berry');
INSERT INTO TheNameTable Values('Bill, Murray');
INSERT INTO TheNameTable Values('Elvis, Presley');
INSERT INTO TheNameTable Values('Lady, Gaga');
INSERT INTO TheNameTable Values('Latoya, Jackson');
INSERT INTO TheNameTable Values('Michael, Jackson');
INSERT INTO TheNameTable Values('Tito, Jackson');
INSERT INTO TheNameTable Values('Randy, Jackson');
INSERT INTO TheNameTable Values('Prince');
GO
*/
SELECT * FROM TheNameTable;

USE Master;
IF EXISTS(SELECT name FROM sys.databases WHERE name = 'Reporting') DROP Database Reporting;
GO

CREATE DATABASE Reporting;
GO
ALTER DATABASE Reporting SET RECOVERY SIMPLE;
GO

USE Reporting;
GO

IF OBJECT_ID('LastName','U') is not null DROP Table LastName;
GO
Create Table LastName (Name varchar(500) PRIMARY KEY);
GO

DECLARE @TheName varchar(500);
DECLARE @LastName varchar(500);
DECLARE LastNameCursor Cursor
FOR 
Select TheName
From Original.dbo.TheNameTable;

Open LastNameCursor;

Fetch NEXT FROM LastNameCursor INTO @TheName;

WHILE @@FETCH_STATUS = 0
BEGIN
    IF (@TheName LIKE '%,%')
    BEGIN
        SET @LastName = right(@TheName,charindex(',',reverse(@TheName))-1);
        MERGE INTO dbo.LastName T
        USING (SELECT @LastName Name) S
                ON T.Name = S.Name
    --  WHEN MATCHED THEN
    --          Nothing to do here.
        WHEN NOT MATCHED
                THEN INSERT VALUES (@LastName);
    END

    FETCH NEXT FROM LastNameCursor INTO @TheName;
END

CLOSE LastNameCursor;
DEALLOCATE LastNameCursor;
GO

SELECT * FROM Reporting.dbo.LastName;
GO