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

From: Guyren Howe <[email-masked]>
Date: Tue, 9 Jul 2024 18:14:42 -0700

On Jul 9, 2024, at 17:58, Krishnakant Mane <[email-masked]> 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 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?

Almost certainly.

Doing it all in a stored procedure or likely even better a single query will remove all of the latency involved in going back 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).

---

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

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

Basically there are if else conditions and it's not just the queries but 
the conditional sequence in which they execute.

So one single query won't do the job.

But Thank you for confirming my understanding.

I believe that the fact that stored procedures are compiled also makes 
them perform faster, is that correct?

Regards.

---

From: Guyren Howe <[email-masked]>
Date: Tue, 9 Jul 2024 19:38:37 -0700

On 7/10/24 06:44, Guyren Howe wrote:
> Basically there are if else conditions and it's not just the queries but the conditional sequence in which they execute.
> 

You might be surprised what you can do in one query. Feel free to share.

---

From: Rob Sargent <[email-masked]>
Date: Wed, 10 Jul 2024 07:13:09 -0600

> On Jul 9, 2024, at 7:21 PM, Krishnakant Mane <[email-masked]> wrote:
> 
> 
Are you processing the results of each of the queries in your python code before sending the next query?  If so, i don't think you will see much improvement per query 

> I believe that the fact that stored procedures are compiled also makes them perform faster, is that correct?
> 
If the SP is fired in a loop or very frequently ( not monthly), yes