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: <da4a01d8-ba4e-4752-add4-531cea0e0ac4@gmail.com>
Date: Tue, 9 Jul 2024 18:14:42 -0700
Cc: "pgsql-general@lists.postgresql.org" <pgsql-general@lists.postgresql.org>
Content-Transfer-Encoding: quoted-printable
Message-Id: <04AE80B8-1F00-4758-A498-C1C7C1FC7A31@gmail.com>
References: <da4a01d8-ba4e-4752-add4-531cea0e0ac4@gmail.com>
To: Krishnakant Mane <kkproghub@gmail.com>
Archived-At: <https://www.postgresql.org/message-id/04AE80B8-1F00-4758-A498-C1C7C1FC7A31%40gmail.com>
Precedence: bulk

On Jul 9, 2024, at 17:58, Krishnakant Mane <kkproghub@gmail.com> wrote:
>=20
> 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.

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).=