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

On Jul 9, 2024, at 17:58, Krishnakant Mane <kkproghub@gmail.com> wrote:
>
> 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
intotypes (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
Pythonbased API? 

Almost certainly.

Doing it all in a stored procedure or likely even better a single query will remove all of the latency involved in
goingback and forth between your app and the database. 

Insofar as the queries you are running separately access similar data, a single query will be able to do that work
once.

There are other potential benefits (a smaller number of queries reduces planning time, for example).