Date: Wed, 10 Jul 2024 16:33:42 +0200
From: "Peter J. Holzer" <hjp-pgsql@hjp.at>
To: pgsql-general@lists.postgresql.org
Subject: Re: can stored procedures with computational sql queries improve API
 performance?
Message-ID: <20240710143342.qqwfn6uc3hceqqvs@hjp.at>
Mail-Followup-To: pgsql-general@lists.postgresql.org
References: <da4a01d8-ba4e-4752-add4-531cea0e0ac4@gmail.com>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="f2o5ciau7b25dhva"
Content-Disposition: inline
In-Reply-To: <da4a01d8-ba4e-4752-add4-531cea0e0ac4@gmail.com>
Archived-At: <https://www.postgresql.org/message-id/20240710143342.qqwfn6uc3hceqqvs%40hjp.at>
Precedence: bulk


--f2o5ciau7b25dhva
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

On 2024-07-10 06:28:46 +0530, Krishnakant Mane wrote:
> 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 compu=
te
> some results for generating financial report.

I am assuming that you aren't creating hundreds of financial reports per
second. So you care about performance because each report takes
significant time (seconds, maybe even minutes). Right?


> It involves summing up some amounts from tuns or of rows and also on cert=
ain
> conditions it categorizes the amounts into types (aka Debit Balance, Cred=
it
> 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?

For just 6 queries I doubt that. You will save one round trip per query,
but that should only be a few milliseconds unless your database is on
the other side of the planet.

You might also get some performance improvement if your queries are
returning a significant amount of data which is only needed for
constructing further queries but doesn't enter the final report. In this
case keeping it in the database might be quite a bit faster than
transferring it back and forth between the database and the client.
OTOH, temporary tables or CTEs might be sufficient for that.

        hp

--=20
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"

--f2o5ciau7b25dhva
Content-Type: application/pgp-signature; name="signature.asc"

-----BEGIN PGP SIGNATURE-----

iQIzBAABCgAdFiEETtJbRjyPwVTYGJ5k8g5IURL+KF0FAmaOm7sACgkQ8g5IURL+
KF34+g//dgNaaifrmvIkMbbVGSsTJmWk9kTGrkxlx0gMBRtXt/9nBNLYNBXfJrYY
RQFMUNlLdeX+dItnzyYHxCv98tekV/U6qHHBEr6At3q8MZz4MhL1BHH9nHz27IzJ
gsDc9cYoAp58SeWCGQDxQupe2yY5McutdrKJY8MIOPqQgY/OjqPZjULqJElAD8Ce
bp9Jmr0RDAEd5r0Tcg6kBEYFwGYqiycoOdKFSdggzq8li/BqiPOlO26Al8XbwXnm
hbXueVeqlZWGyl8Drb1etQHOL3r3ZITFkk1FnwMVXpnjsqipQTM/36R+hIiR5pcm
JxgxVG+ihKBce7CDEiAvd0hjaWJ21GtaVJEb94CQEz3qeGMypGCT4fimztviY9gi
gkZJvgTDjqomhhzBFvLLh56qGBenizpY6F/E7IVlcUS9NhXMo7Gwl1hgpyj7cVrK
XcRKS02GeHN8TSfAvvPRhKQxSvR1mr+ZHf7o5JoGPrHTRf8e7FtvdEdQgKsYGawZ
7VQy/OOOcsIIlIvk2kja3FRbTL3sPkKt41ect4B9TIxACzAKIXTmeyDBOA4MJi/8
18XUaCcJpq8AB0HpJ3BwfKujATgnweWCIOsOrEmvzcfc+Z0p3ApsWJ21cn+Kq3+F
Jwhh8SjGeJ3k5Ukx7edWSaP2n2kWkpVjTECZb5jcM0HDWYO6uYY=
=PXjy
-----END PGP SIGNATURE-----

--f2o5ciau7b25dhva--