Subject: can stored procedures with computational sql queries improve API performance?

From: Krishnakant Mane <[email-masked]>
Date: Wed, 10 Jul 2024 06:28:46 +0530

Hello.

I have a straight forward question, but I am just trying to analyze the 
specifics.

So I have a set of queries depending on each other in a sequence to 
compute some results for generating financial report.

It involves summing up some amounts from tuns or of rows and also on 
certain conditions it categorizes the amounts into types (aka Debit 
Balance, Credit balance etc).

There are at least 6 queries in this sequence and apart from 4 input 
parameters. these queries never change.

So will I get any performance benefit by having them in a stored 
procedure rather than sending the queries from my Python based API?

Regards.

---

From: "Peter J. Holzer" <[email-masked]>
Date: Wed, 10 Jul 2024 16:33:42 +0200

On 2024-07-10 06:28:46 +0530, Krishnakant Mane wrote:
> So I have a set of queries depending on each other in a sequence to compute
> some results for generating financial report.

I am assuming that you aren't creating hundreds of financial reports per
second. So you care about performance because each report takes
significant time (seconds, maybe even minutes). Right?

> It involves summing up some amounts from tuns or of rows and also on certain
> conditions it categorizes the amounts into types (aka Debit Balance, Credit
> balance etc).
> 
> So will I get any performance benefit by having them in a stored procedure
> rather than sending the queries from my Python based API?

For just 6 queries I doubt that. You will save one round trip per query,
but that should only be a few milliseconds unless your database is on
the other side of the planet.

You might also get some performance improvement if your queries are
returning a significant amount of data which is only needed for
constructing further queries but doesn't enter the final report. In this
case keeping it in the database might be quite a bit faster than
transferring it back and forth between the database and the client.
OTOH, temporary tables or CTEs might be sufficient for that.

        hp

-- 
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | [email-masked]         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"