Content-Type: text/plain;
	charset=us-ascii
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3774.600.62\))
Subject: Re: can stored procedures with computational sql queries improve API
 performance?
From: Guyren Howe <guyren@gmail.com>
In-Reply-To: <c039e269-49d4-4d27-913b-412fdd4c3cda@gmail.com>
Date: Tue, 9 Jul 2024 19:38:37 -0700
Cc: "pgsql-general@lists.postgresql.org" <pgsql-general@lists.postgresql.org>
Content-Transfer-Encoding: quoted-printable
Message-Id: <DD4467BE-F9EE-4869-B3A7-E0EA1FD82715@gmail.com>
References: <da4a01d8-ba4e-4752-add4-531cea0e0ac4@gmail.com>
 <04AE80B8-1F00-4758-A498-C1C7C1FC7A31@gmail.com>
 <c039e269-49d4-4d27-913b-412fdd4c3cda@gmail.com>
To: Krishnakant Mane <kkproghub@gmail.com>
Archived-At: <https://www.postgresql.org/message-id/DD4467BE-F9EE-4869-B3A7-E0EA1FD82715%40gmail.com>
Precedence: bulk

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 specifics.
>>>=20
>>> So I have a set of queries depending on each other in a sequence to =
compute some results for generating financial report.
>>>=20
>>> 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).
>>>=20
>>> There are at least 6 queries in this sequence and apart from 4 input =
parameters. these queries never change.
>>>=20
>>> 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.
>>=20
>> 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.
>>=20
>> Insofar as the queries you are running separately access similar =
data, a single query will be able to do that work once.
>>=20
>> There are other potential benefits (a smaller number of queries =
reduces planning time, for example).
>=20
>=20
> Basically there are if else conditions and it's not just the queries =
but the conditional sequence in which they execute.
>=20
> So one single query won't do the job.

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