主页 / dba / 问题 / 18238
Accepted
mdoyle
Asked: 2012-05-22 12:51:04 +0800 CST

用基于集合的方法替换光标

  • 772

如果可能的话，我希望在特定的存储过程中替换我的基于游标的解决方案。如果有任何区别，则它在 SQL Server 2008 R2 上运行。我正在寻找一种算法而不是精确的代码。

背景：

SP 是通过直邮或电子邮件发送邮件的公司系统的一部分。邮件包含一个个性化代码，收件人可以在访问商家时输入该代码以获得特别折扣或优惠。跟踪代码使用情况并向商家提供有关对各种邮件的响应的汇总报告。“客户”被定义为具有唯一名字、姓氏和地址的邮件的目标；如果没有地址，则电子邮件替换地址。

有争议的表格如下（简化版）：

CREATE TABLE job (
    id INT PRIMARY KEY IDENTITY (1,1),
    job_num VARCHAR(32) NOT NULL,
    mailing_id INT NOT NULL,
    personal_code NVARCHAR(50) NOT NULL,
    fname NVARCHAR(50) NOT NULL,
    lname NVARCHAR(50) NOT NULL,
    email NVARCHAR(50),
    address NVARCHAR(50),
    city NVARCHAR(50),
    state CHAR(2),
    zip NVARCHAR(10),
    extra NVARCHAR(150)
);

CREATE TABLE customer (
    id INT PRIMARY KEY IDENTITY (1,1),
    fname NVARCHAR(50) NOT NULL,
    lname NVARCHAR(50) NOT NULL,
    email NVARCHAR(50),
    address NVARCHAR(50),
    city NVARCHAR(50),
    state CHAR(2),
    zip NVARCHAR(10)
);

CREATE TABLE personal_code (
    id INT PRIMARY KEY IDENTITY (1,1),
    customer_id INT NOT NULL,
    mailing_id INT NOT NULL,
    personal_code NVARCHAR(50) NOT NULL,
    email NVARCHAR(50),
    FOREIGN KEY (customer_id) REFERENCES customer(id)
);

CREATE TABLE personal_code_extra (
    personal_code_id INT PRIMARY KEY,
    extra NVARCHAR(150),
    FOREIGN KEY (personal_code_id) REFERENCES personal_code(id)
);

作业表由一个外部进程填充，该进程参与创建将向其发送邮件的地址列表。然后它调用我希望优化的 SP，传递 job_num。然后，SP 从具有该 job_num 的作业表中读取所有记录，并将存储在那里的数据插入到其他三个表中。

正如预期的那样，客户表是存储各种邮件收件人数据的地方。

personal_code 表存储与邮件相关的个人代码数据。每个代码都与特定的邮件以及特定的客户相关联。目前，SP 为作业表打开一个游标。然后它遍历每一行，并为每一行执行以下操作：

  1. 如果 address 不为 null，则将 @customer_id 设置为 customer.id，其中 fname、lname 和 address 匹配；否则，它将其设置为等于 customer.id，其中 fname、lname 和电子邮件匹配并且地址为空或空。
  2. 此时，如果@customer_id 为空，则没有匹配的记录，将向客户添加一条新记录。@customer_id 设置为 scope_identity()。
  3. 一条记录被插入到personal_code 中。
  4. 如果 job.extra 不为空，则将一条记录插入到 personal_code_extra 中，（当然）使用第 3 步中插入生成的 id。

customer 和 personal_code 是数据库中最大的表，分别有 4000 万和 6400 万条记录。即使对客户的查询具有覆盖索引，对工作中的每一行进行单独搜索也必须减慢速度。我非常想放弃光标并用基于集合的方法替换这种 RBAR 方法。阻止我这样做的原因是不得不为personal_code 插入使用新创建的customer_id，以及为“extras”表使用新创建的personal_code_id。如果不是这样，我可以做类似的事情

INSERT INTO personal_code (fields)
SELECT (fields)
FROM job j
INNER JOIN customer c ON j.fname = c.fname AND j.lname = c.lname AND j.address = c.address
WHERE j.address IS NOT NULL;

INSERT INTO personal_code (fields)
SELECT (fields)
FROM job j
INNER JOIN customer c ON j.fname = c.fname AND j.lname = c.lname AND j.email = c.email
WHERE j.address IS NULL;

INSERT INTO personal_code (fields)
SELECT (fields) -- but won't have a value for customer_id !
FROM job j
LEFT JOIN customer c ON either_address_or_email
WHERE c.id IS NULL;

以及如何以基于集合的方法处理“额外”内容的插入，我目前不知道。提前感谢您的任何想法。

编辑：每个请求添加的光标代码。这是简化的，但具有所有必需品——希望我的编辑是准确的。

DECLARE job_cur CURSOR FOR
SELECT mailing_id, personal_code, email, fname, lname, address, city, state, zip, extra
FROM job
WHERE job_num = @job_no;

OPEN job_cur;

FETCH NEXT FROM job_cur INTO @mailing_id, @personal_code, @email, @fname, @lname, @address, @city, @state, @zip, @extra;

WHILE @@FETCH_STATUS = 0
BEGIN

    IF ISNULL(@address, '') != ''
    SET @customer_id = (
        SELECT id 
        FROM customer 
        WHERE fname = @fname
        AND lname = @lname
        AND address = @address
    );
    ELSE
    SET @customer_id = (
        SELECT id 
        FROM customer 
        WHERE fname = @fname
        AND lname = @lname
        AND email = @email
        AND (address IS NULL OR address = '')
    ); 

    IF @customer_id IS NULL
    BEGIN

        INSERT INTO customer (
            fname, lname, address, city, state, zip, email
        )
        VALUES (
            @fname, @lname, @address, @city, @state, @zip, @email
        );

        SET @customer_id = SCOPE_IDENTITY();

    END

    INSERT INTO personal_code (
        customer_id, mailing_id, personal_code, email
    )
    VALUES (
        @customer_id, @mailing_id, @personal_code, @email
    );
    SET @personal_code_id = SCOPE_IDENTITY();

    IF @extra IS NOT NULL           
        INSERT INTO personal_code_extra (
            personal_code_id, extra
        )
        VALUES (
            @personal_code_id, @extra
        );

    FETCH NEXT FROM job_cur INTO @mailing_id, @personal_code, @email, @fname, @lname, @address, @city, @state, @zip, @extra;

END
sql-server sql-server-2008-r2

2 个回答

  1. Best Answer

    我可能会以蛮力的方式做到这一点，并添加索引以支持这些不存在的连接。插入所有不存在的客户后，对待新客户和老客户并没有太大的好处：

    INSERT dbo.Customer(fname, lname, address, city, state, zip, email)
    SELECT fname, lname, address, city, state, zip, email
     FROM dbo.job AS j
     WHERE job_no = @job_no
     AND NOT EXISTS
     (
        SELECT 1 FROM dbo.Customer
        WHERE fname = j.fname
        AND lname = j.lname
        AND (address = j.address OR email = j.email)
     );

INSERT INTO dbo.personal_code (customer_id, mailing_id, personal_code, email)
SELECT c.customer_id, j.mailing_id, j.personal_code, c.email
  FROM dbo.Customer AS c
  INNER JOIN dbo.job AS j
  ON c.fname = j.fname AND c.lname = j.lname 
  AND (c.address = j.address OR c.email = j.email)
  WHERE j.job_no = @job_no;

INSERT dbo.personal_code_extra(personal_code_id, extra)
SELECT pc.personal_code_id, j.extra
  FROM dbo.personal_code AS pc
  INNER JOIN dbo.Customer AS c
  ON pc.customer_id = c.customer_id
  INNER JOIN dbo.job AS j
  ON c.fname = j.fname AND c.lname = j.lname 
  AND (c.address = j.address OR c.email = j.email)
  WHERE j.job_no = @job_no;
    • 4

  2. 我认为您需要 OUTPUT 子句来返回任何新的记录 ID。进一步取决于您想要做什么，您可能有兴趣使用 MERGE 语句而不仅仅是插入。例如，电子邮件保持不变但地址已更改的人可以更新，而不是像您正在做的那样添加新记录。

    • 0

相关问题