CODEWITHSUNDEEP
sqloracle-databasestored-proceduresplsql
user2254180
user2254180

Reputation: 856

Improving stored procedure with multiple counts into output parameters

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;

Upvotes: 0

Views: 533

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270351

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

Upvotes: 7

Related Questions