public inbox for [email protected]
help / color / mirror / Atom feedFrom: Peter J. Holzer <[email protected]>
To: [email protected]
Subject: Re: can stored procedures with computational sql queries improve API performance?
Date: Wed, 10 Jul 2024 16:33:42 +0200
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
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.
|_|_) | |
| | | [email protected] | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"
Attachments:
[application/pgp-signature] signature.asc (833B, 2-signature.asc)
download
view thread (2+ messages)
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected]
Subject: Re: can stored procedures with computational sql queries improve API performance?
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox