Improving stored procedure with multiple counts into output parameters

Question

I have a stored procedure that returns a number of OUT parameters that consist of results from SELECT COUNT(*) queries. The below code snippet runs fine, but seems slow.

I have up to 30 different OUT params, so it means I run 30 separate queries, which is probably why the query runs so slow.

Does anyone have any suggestions on how I can speed up this code?

PROCEDURE get_counts(
   count1 OUT INT,
   count2 OUT INT,
   count3 OUT INT,
   .. etc.
) IS 
   l_count1 INT;
   l_count2 INT;
   l_count3 INT;
   .. etc.
BEGIN
   SELECT COUNT(*) INTO l_count1 from table1 where condition_blah;
   SELECT COUNT(*) INTO l_count2 from table1 where condition_blah;
   SELECT COUNT(*) INTO l_count3 from table1 where condition_blah;
   ... etc

   count1 := l_count1;
   count2 := l_count2;
   count3 := l_count3;
   .. etc
END get_counts;

I don't think you can speed up the procedure. The procedure is slow because you run multiple selects not due to the number of OUT parameters. You have to optimize the queries, not the procedure. — Wernfried Domscheit
– Wernfried Domscheit, Commented Sep 20, 2019 at 14:06

Gordon Linoff · Accepted Answer · 2019-09-20 14:05:10Z

7

Use conditional aggregation:

SELECT SUM(CASE WHEN condition_blah THEN 1 ELSE 0 END),
       SUM(CASE WHEN condition_blah2 THEN 1 ELSE 0 END),
       . . .
INTO l_count1, l_count2, . . . ;
FROM table1

answered Sep 20, 2019 at 14:05

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Improving stored procedure with multiple counts into output parameters

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related