Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable
From: Rob Sargent <robjsargent@gmail.com>
Mime-Version: 1.0 (1.0)
Subject: Re: can stored procedures with computational sql queries improve API performance?
Date: Wed, 10 Jul 2024 07:13:09 -0600
Message-Id: <B33971DD-9219-4C76-8DA1-911F416F10EE@gmail.com>
References: <c039e269-49d4-4d27-913b-412fdd4c3cda@gmail.com>
Cc: Guyren Howe <guyren@gmail.com>, pgsql-general@lists.postgresql.org
In-Reply-To: <c039e269-49d4-4d27-913b-412fdd4c3cda@gmail.com>
To: Krishnakant Mane <kkproghub@gmail.com>
Archived-At: <https://www.postgresql.org/message-id/B33971DD-9219-4C76-8DA1-911F416F10EE%40gmail.com>
Precedence: bulk


> On Jul 9, 2024, at 7:21=E2=80=AFPM, Krishnakant Mane <kkproghub@gmail.com>=
 wrote:
>=20
> =EF=BB=BF
>> On 7/10/24 06:44, Guyren Howe wrote:
>>> On Jul 9, 2024, at 17:58, Krishnakant Mane <kkproghub@gmail.com> wrote:
>>> Hello.
>>>=20
>>> I have a straight forward question, but I am just trying to analyze the s=
pecifics.
>>>=20
>>> So I have a set of queries depending on each other in a sequence to comp=
ute some results for generating financial report.
>>>=20
>>> It involves summing up some amounts from tuns or of rows and also on cer=
tain conditions it categorizes the amounts into types (aka Debit Balance, Cr=
edit balance etc).
>>>=20
>>> There are at least 6 queries in this sequence and apart from 4 input par=
ameters. these queries never change.
>>>=20
>>> So will I get any performance benefit by having them in a stored procedu=
re rather than sending the queries from my Python based API?
>> Almost certainly.
>>=20
>> Doing it all in a stored procedure or likely even better a single query w=
ill remove all of the latency involved in going back and forth between your a=
pp and the database.
>>=20
>> Insofar as the queries you are running separately access similar data, a s=
ingle query will be able to do that work once.
>>=20
>> There are other potential benefits (a smaller number of queries reduces p=
lanning time, for example).
>=20
>=20
> Basically there are if else conditions and it's not just the queries but t=
he conditional sequence in which they execute.
>=20
> So one single query won't do the job.
>=20
Are you processing the results of each of the queries in your python code be=
fore sending the next query?  If so, i don't think you will see much improve=
ment per query=20


> But Thank you for confirming my understanding.
>=20
> I believe that the fact that stored procedures are compiled also makes the=
m perform faster, is that correct?
>=20
If the SP is fired in a loop or very frequently ( not monthly), yes=20
> Regards.
>=20
>=20
>=20