public inbox for [email protected]
help / color / mirror / Atom feedcan stored procedures with computational sql queries improve API performance?
2+ messages / 2 participants
[nested] [flat]
* can stored procedures with computational sql queries improve API performance?
@ 2024-07-10 00:58 Krishnakant Mane <[email protected]>
0 siblings, 1 reply; 2+ messages in thread
From: Krishnakant Mane @ 2024-07-10 00:58 UTC (permalink / raw)
To: [email protected]
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.
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: can stored procedures with computational sql queries improve API performance?
@ 2024-07-10 14:33 Peter J. Holzer <[email protected]>
parent: Krishnakant Mane <[email protected]>
0 siblings, 0 replies; 2+ messages in thread
From: Peter J. Holzer @ 2024-07-10 14:33 UTC (permalink / raw)
To: [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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2024-07-10 14:33 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-07-10 00:58 can stored procedures with computational sql queries improve API performance? Krishnakant Mane <[email protected]>
2024-07-10 14:33 ` Peter J. Holzer <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox