public inbox for [email protected]help / color / mirror / Atom feed
Re: can stored procedures with computational sql queries improve API performance? 4+ messages / 3 participants [nested] [flat]
* Re: can stored procedures with computational sql queries improve API performance? @ 2024-07-10 01:14 Guyren Howe <[email protected]> 0 siblings, 1 reply; 4+ messages in thread From: Guyren Howe @ 2024-07-10 01:14 UTC (permalink / raw) To: Krishnakant Mane <[email protected]>; +Cc: [email protected] <[email protected]> On Jul 9, 2024, at 17:58, Krishnakant Mane <[email protected]> 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). ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: can stored procedures with computational sql queries improve API performance? @ 2024-07-10 01:21 Krishnakant Mane <[email protected]> parent: Guyren Howe <[email protected]> 0 siblings, 2 replies; 4+ messages in thread From: Krishnakant Mane @ 2024-07-10 01:21 UTC (permalink / raw) To: Guyren Howe <[email protected]>; +Cc: [email protected] <[email protected]> On 7/10/24 06:44, Guyren Howe wrote: > On Jul 9, 2024, at 17:58, Krishnakant Mane <[email protected]> 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). 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. ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: can stored procedures with computational sql queries improve API performance? @ 2024-07-10 02:38 Guyren Howe <[email protected]> parent: Krishnakant Mane <[email protected]> 1 sibling, 0 replies; 4+ messages in thread From: Guyren Howe @ 2024-07-10 02:38 UTC (permalink / raw) To: Krishnakant Mane <[email protected]>; +Cc: [email protected] <[email protected]> On 7/10/24 06:44, Guyren Howe wrote: >> On Jul 9, 2024, at 17:58, Krishnakant Mane <[email protected]> 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). > > > 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. You might be surprised what you can do in one query. Feel free to share. ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: can stored procedures with computational sql queries improve API performance? @ 2024-07-10 13:13 Rob Sargent <[email protected]> parent: Krishnakant Mane <[email protected]> 1 sibling, 0 replies; 4+ messages in thread From: Rob Sargent @ 2024-07-10 13:13 UTC (permalink / raw) To: Krishnakant Mane <[email protected]>; +Cc: Guyren Howe <[email protected]>; [email protected] > On Jul 9, 2024, at 7:21 PM, Krishnakant Mane <[email protected]> wrote: > > >> On 7/10/24 06:44, Guyren Howe wrote: >>> On Jul 9, 2024, at 17:58, Krishnakant Mane <[email protected]> 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). > > > 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. > 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 > 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? > If the SP is fired in a loop or very frequently ( not monthly), yes > Regards. > > > ^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2024-07-10 13:13 UTC | newest] Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2024-07-10 01:14 Re: can stored procedures with computational sql queries improve API performance? Guyren Howe <[email protected]> 2024-07-10 01:21 ` Krishnakant Mane <[email protected]> 2024-07-10 02:38 ` Guyren Howe <[email protected]> 2024-07-10 13:13 ` Rob Sargent <[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