此存储过程需要很长时间才能运行？

问题描述：

DECLARE @run_date DATETIME 

SELECT @run_date = '2016/05/30' 

SELECT PAYMENT_LINE.active, 
     PAYMENT_LINE.alloc_date, 
     PAYMENT_LINE.allocation_ind, 
     PAYMENT_LINE.amount, 
     PAYMENT_LINE.fund_charge_id, 
     PAYMENT_LINE.paid_td, 
     PAYMENT_LINE.payment_id, 
     PAYMENT_LINE.payment_line_id, 
     PAYMENT_LINE.payment_type_code, 
     PAYMENT_LINE.period, 
     PAYMENT_LINE.reference_id, 
     PAYMENT_LINE.reversal_date, 
     PAYMENT_LINE.src_id 

     INTO #pym 
FROM PAYMENT_LINE 
WHERE alloc_date IS NOT NULL 
     AND alloc_date < @run_date 

/*DELETE FROM #pym WHERE reversal_date IS NULL*/ 



DELETE 
FROM #pym 
WHERE reversal_date > @run_date 



SELECT payment_id, 
     Total = SUM(amount) 

     INTO #pym1 
FROM #pym 
GROUP BY 
     payment_id 



SELECT PAYMENT.payment_id, 
     PAYMENT.payment_amount, 
     Total 

     INTO #pym2 
FROM #pym1, 
     PAYMENT 
WHERE PAYMENT.payment_id = #pym1.payment_id 
GROUP BY 
     PAYMENT.payment_id 
HAVING Total > PAYMENT.payment_amount 



SELECT MANUFACTURER.manufacturer_id, 
     MANUFACTURER.manufacturer_name, 
     alloc_date, 
     PAYMENT.payment_id, 
     PAYMENT.payment_amount, 
     Total 

     INTO #pym3 
FROM #pym2, 
     PAYMENT, 
     MANUFACTURER, 
     PAYMENT_LINE 
WHERE Total > PAYMENT.payment_amount 



SELECT payment_id, 
     Total_payment = SUM(payment_amount - Total) 

     INTO #pym4 
FROM #pym3 
GROUP BY 
     payment_id 



SELECT MANUFACTURER.manufacturer_id, 
     MANUFACTURER.manufacturer_name, 
     alloc_date, 
     PAYMENT.payment_id, 
     PAYMENT.payment_amount, 
     Total, 
     Total_payment 
FROM #pym4, 
     #pym2, 
     PAYMENT, 
     MANUFACTURER, 
     PAYMENT_LINE

[不良习惯踢：使用旧样式的JOIN（http://sqlblog.com/blogs/aaron_bertrand/archive/2009/10/08/bad-habits-to-kick-using -old-style-joins.aspx） - 旧式*逗号分隔的表*样式列表被替换为ANSI - ** 92 ** SQL标准中的* proper * ANSI'JOIN'语法（** more超过20年**之前），它的使用不鼓励 –

你在这里做了很多无用的工作，你为什么要继续创建这些临时表，然后立即删除它们？还请提供一些样本数据和预期产出。 –

最后一个'SELECT'没有任何'JOIN'谓词...为什么？ – Devart

答

我已经看过这个查询，如果没有更多的信息继续下去，这真的没什么意义。前三个查询;

SELECT PAYMENT_LINE.active, 
    PAYMENT_LINE.alloc_date, 
    PAYMENT_LINE.allocation_ind, 
    PAYMENT_LINE.amount, 
    PAYMENT_LINE.fund_charge_id, 
    PAYMENT_LINE.paid_td, 
    PAYMENT_LINE.payment_id, 
    PAYMENT_LINE.payment_line_id, 
    PAYMENT_LINE.payment_type_code, 
    PAYMENT_LINE.period, 
    PAYMENT_LINE.reference_id, 
    PAYMENT_LINE.reversal_date, 
    PAYMENT_LINE.src_id 

    INTO #pym 
FROM PAYMENT_LINE 
WHERE alloc_date IS NOT NULL 
    AND alloc_date < @run_date 

DELETE 
FROM #pym 
WHERE reversal_date > @run_date 



SELECT payment_id, 
    Total = SUM(amount) 

    INTO #pym1 
FROM #pym 
GROUP BY 
    payment_id

均能被组合成这样;

SELECT payment_id, 
     Total = SUM(amount) 
     INTO #pym 
FROM PAYMENT_LINE 
WHERE alloc_date IS NOT NULL 
     AND alloc_date < @run_date 
     AND reversal_date <= @run_date 
GROUP BY payment_id

你真的需要阅读正确的连接语法，你在这里使用的是真正老的语法;

SELECT PAYMENT.payment_id, 
    PAYMENT.payment_amount, 
    Total 

    INTO #pym2 
FROM #pym1, 
    PAYMENT 
WHERE PAYMENT.payment_id = #pym1.payment_id 
GROUP BY 
    PAYMENT.payment_id 
HAVING Total > PAYMENT.payment_amount

请在这里阅读亚伦Bertrands文章http://sqlblog.com/blogs/aaron_bertrand/archive/2009/10/08/bad-habits-to-kick-using-old-style-joins.aspx

您还需要注意笛卡尔连接和他们意味着什么，你最终的查询会弄乱你的数据在很多方面;

SELECT MANUFACTURER.manufacturer_id, 
    MANUFACTURER.manufacturer_name, 
    alloc_date, 
    PAYMENT.payment_id, 
    PAYMENT.payment_amount, 
    Total, 
    Total_payment 
FROM #pym4, 
    #pym2, 
    PAYMENT, 
    MANUFACTURER, 
    PAYMENT_LINE

您还应该阅读使用表别名，这将使您的角色在长期内更简单;

When to use SQL Table Alias

我认为你需要重新审视你正在试图做的，问问自己，你正在使用的临时表的逻辑是否有必要的。对我来说，看起来你的数据将会是一团糟。考虑这是否可以全部放入一个查询中，每个部分都是您加入的子选择部分，以便检索您需要的数据;

http://www.techrepublic.com/article/use-sql-subselects-to-consolidate-queries/

此存储过程需要很长时间才能运行？

相关推荐