This stored procedure take a long time to run?

Question

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

[Bad habits to kick : using old-style JOINs](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins) - that old-style *comma-separated list of tables* style was replaced with the *proper* ANSI `JOIN` syntax in the ANSI-**92** SQL Standard (**more than 20 years** ago) and its use is discouraged — marc_s, May 31 '16 at 09:26
You're doing a lot of useless work here, why would you keep creating these temp tables and then delete them right away? Please also provide some sample data and expected output. — Rich Benner, May 31 '16 at 09:28

score 1 · Answer 1 · edited Nov 11 '18 at 13:46

I've taken a look at this query and it's really not making much sense without some more information to go on. The first three queries;

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

Could all be combined into this;

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

You really need to be reading up on correct join syntax, you're using the really old syntax here;

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

Please read Aaron Bertrand's article here

Bad habits to kick : using old-style JOINs

You also need to pay attention to cartesian joins and what they entail, your final query is going to mess up your data in lots of ways;

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

You should also read up on using table aliases, these will make your role much simpler in the long term;

When to use SQL Table Alias

I think you need to revisit what you're trying to do and ask yourself whether the temp table logic you're using is necessary. To me, it looks like your data is going to be a total mess from this. Consider whether this could all be put into one query with each section being a sub-select that you join to in order to retrieve the data you need;

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

This stored procedure take a long time to run?

1 Answers1