主页 / dba / 问题 / 13674
Accepted
datagod
Asked: 2012-02-23 09:04:48 +0800 CST

解析大量字符串的最佳方法是什么？

  • 772

我有一个包含超过 400,000,000 条记录的表，我正在寻找有关如何快速解析它的建议。

TheNameTable
(
  NameID  int primary key,
  TheName varchar(500)
)

名称存储如下：“FirstName，LastName”（不是我的表，只是我必须使用的）

我需要提取一个唯一的姓氏列表。我最初的想法是分批处理表（比如一次 50,000 条记录），使用 NameID 来控制批范围。然后，我将使用 SQL 的内置字符串函数在“，”处断开字符串并保留字符串的右半部分。

right(TheName,charindex('.',reverse(TheName))-1)

我有一种感觉，这仍然需要很长时间。

有没有人有其他想法？

简单地导出数据并在数据库外处理文件是否值得？

我采用的解决方案：

按照建议，我创建了两个计算列。一个用于名字，一个用于姓氏。它们没有持久化，因为我的空间有限。

alter table TheNameTable 
add LastName as substring(TheName, charindex(',',TheName)+1,1000)

alter table TheNameTable 
add FirstName as left(TheName,charindex(',',TheName)-1)

我要求管理员临时增加 RAM，他们将 VM 提高到 32GB。

我创建了一个新表，其中包含 FirstName 和 LastName 列。我在列上放置了一个唯一的复合索引，但指定了 IGNORE_DUP_KEY = ON。

我刚刚插入了前 1,000,000 条记录。它过滤掉了 125,000 个重复项。整个语句运行了 9 秒。

这就是我想要的速度！

sql-server

4 个回答

  1. Best Answer

    4亿个名字是很多。我在里面吗？;-)

    我的直觉表明，使用子字符串不会比通过 CLR 编写代码慢得多。我是一名 SQL 专家，过去（2000 年或 2005 年）我已经完成了相当多的简单解析，并且我参与了一个非常复杂的解析方案（全球地址） c 并通过 xproc 调用，直到我们发现原型“本机”代码并不比使用 tsql 函数编写的相同代码快。

    如果您想使用 tsql 以外的语言，我建议您在 c# 或 vb.net 中编写 CLR。对于简单的事情，在 CLR 中编写代码并不难。我在不到一个早上的时间里从 newb 变成了几个工作目录和文件实用程序。网上有很多简单的 clr 程序的例子。而且您不必学习任何东西（或安装 Visual Studio）就可以在 tsql 中编写它

    无论如何，您必须至少检查一次桌子。如果你导出，然后解析然后放回不是少量的数据，那是很多时间。你能保证你的来源不会同时改变吗？

    这似乎总是在每个人身上偷偷摸摸：解析的数据会发生什么？它在哪里结束？您是否打算更新该行，也许您的示例中没有显示姓氏和名字列？

    如果你这样做了，并且这些列当前为空或其中的长度为零，你可能会发现更新语句的性能非常糟糕，因为 sql 可能必须拆分页面来存储姓氏。

    换句话说，您的性能问题不是解析，而是存储解析的数据。通常，这比将数据插入另一个表更糟糕。此外，所有这些页面拆分都会使您的表碎片化并导致查询性能下降，这可能会激怒您的 dba，因为他/她必须在（大）表上运行碎片整理过程。

    这是最后一个想法：你真的需要存储解析的数据吗？您可以使用一个即时计算姓氏的计算列吗？如果您需要，这些是可索引的，在某些条件下。另一种方法是公开表列以及“解析的姓氏列”的视图。

    • 6

  2. 用另一种语言（例如 c#）编写一个小脚本可能会更快，例如从表中提取所有数据然后对其进行操作。

    然后，您可以对数据执行任何操作，或者将其发送到其他地方，或者使用 BCP 返回数据库。

    • 3

  3. 如果您可以访问一组 unix shell 实用程序（如果您需要获得在 Windows 上运行的实用程序，请参阅gnu win32TheName ），您可以导出该列并使用 shell 管道处理它，如下所示：

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

    不过，出口过程将非常昂贵。在 SQL Server 上，您可以使用以下查询创建一个带有姓氏的临时表：

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

    不管你怎么看，你都准备好进行表扫描了。导出可能比查询上的不同处理更昂贵，因此猜测查询会更快。

    • 3

  4. 我一直在学习很多关于 SQL Server 处理的知识，并认为我会用 Cursor 来尝试一下。我的想法是创建另一个数据库（甚至可能是另一个实例），以便您可以根据需要截断日志，因为这是一个“报告”数据库，并且您希望限制与“实时”数据的交互。

    在我看来，光标将是一个好主意，因为您可以随时进行处理，理论上可以一次性完成。不会太难

    • 将计数字段添加到 LastName 表，例如，对出现的姓名进行计数（姓名、计数）。
    • 添加一个循环计数器，在 100 万次循环后清除事务日志。
    • 添加将名称拆分为单个表的分隔符（LastNameA、LastNameB、... LastNameOther、Invalid）

    Merge 是一个 2008+ 构造，所以如果您创建了另一个实例并链接到 2000/2005（如果您不在 2008+），这将起作用，afaik：

    /*
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
    • 0

相关问题