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

On 2024-07-10 06:28:46 +0530, Krishnakant Mane wrote:
> 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.

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).
>
> 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?

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.
|_|_) | |
| | | hjp(at)hjp(dot)at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"