MIME-Version: 1.0
References: <da4a01d8-ba4e-4752-add4-531cea0e0ac4@gmail.com>
In-Reply-To: <da4a01d8-ba4e-4752-add4-531cea0e0ac4@gmail.com>
From: Olivier Gautherot <ogautherot@gautherot.net>
Date: Wed, 10 Jul 2024 19:33:19 +0200
Message-ID: <CAJ7S9TVhTrrjX4y=tOPOJp1dqGJDYeX0H_GMP_fiVuRRd2e7ww@mail.gmail.com>
Subject: Re: can stored procedures with computational sql queries improve API performance?
To: Krishnakant Mane <kkproghub@gmail.com>
Cc: pgsql-general@lists.postgresql.org
Content-Type: multipart/alternative; boundary="0000000000008bdbbf061ce80b24"
Archived-At: <https://www.postgresql.org/message-id/CAJ7S9TVhTrrjX4y%3DtOPOJp1dqGJDYeX0H_GMP_fiVuRRd2e7ww%40mail.gmail.com>
Precedence: bulk

--0000000000008bdbbf061ce80b24
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

Hi Krishnkant,

On Wed, Jul 10, 2024 at 2:58=E2=80=AFAM Krishnakant Mane <kkproghub@gmail.c=
om>
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?
>
> Regards.
>

Functions and procedures have a significant potential to improve
performance but there are a few things to watch. Return of experience after
having written a few hundreds for a project. The list below is by no mean
not exhaustive.

1) Warning: Compatibility with other database engines
If your model needs to run on other technologies (Oracle, MySQL, MS-SQL,
etc.), the increase in maintenance efforts may become prohibitive

2) Warning: deadlocks
Make sure to design the procedures in such a way that you don't mutually
depend on other parallel invocations

3) WARNING: Don't overload the server
Try to remain conservative in terms of computation in your server. Try to
stick to selects, updates, deletes, joins, and simple arithmetics and
strings manipulations. You can do a lot more but it may quickly affect the
overall performance.

The reasons why I would recommend to use them:

A) Reduction of round trips
Even though it may be a few ms at a time, it can add up and become
significant. This is where you gain performance.

B) Implicit transactions
A function will not free locks until it returns. This means that if your
queries depend on cells, or modify cells, the behavior will be coherent,
reducing the risk of race conditions. If other invocations depend on the
same data, the locks will take care of sequencing execution to maintain
integrity. In other words, you can safely invoke functions in parallel and
let the engine do the scheduling when necessary.

C) API evolution
As long as you have only 1 or 2 applications running against the database,
it may not be an issue. If you have more and your model needs to evolve,
you may get to a situation where updating them all at the same time can
become a challenge, especially if you depend on external providers. By
using procedures and functions, you can abstract the model and maintain a
standard interface to the application.

Note: * I DON'T RECOMMEND IT * but in some cases it can be handy to have
the same function name with different sets of parameters (for instance to
present a wrapper with default parameters, and other instances with a
finer, more complete control). It can happen if you don't clean up timely
older versions of the API when you upgrade your model - and it can become a
nightmare.

Last recommendation: activate the logs and review regularly the performance
of your functions. You may identify occurrences that run very fast and
others not so. It can help you identify potential conflicts or model
optimizations.

Hope it helps
--
Olivier Gautherot

--0000000000008bdbbf061ce80b24
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr">Hi Krishnkant,<div><div><div dir=3D"ltr" =
class=3D"gmail_signature"><div dir=3D"ltr"><div><div dir=3D"ltr"><div><div =
dir=3D"ltr"><br></div></div></div></div></div></div></div></div></div><div =
class=3D"gmail_quote"><div dir=3D"ltr" class=3D"gmail_attr">On Wed, Jul 10,=
 2024 at 2:58=E2=80=AFAM Krishnakant Mane &lt;<a href=3D"mailto:kkproghub@g=
mail.com">kkproghub@gmail.com</a>&gt; wrote:<br></div><blockquote class=3D"=
gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(20=
4,204,204);padding-left:1ex">Hello.<br>
<br>
I have a straight forward question, but I am just trying to analyze the <br=
>
specifics.<br>
<br>
So I have a set of queries depending on each other in a sequence to <br>
compute some results for generating financial report.<br>
<br>
It involves summing up some amounts from tuns or of rows and also on <br>
certain conditions it categorizes the amounts into types (aka Debit <br>
Balance, Credit balance etc).<br>
<br>
There are at least 6 queries in this sequence and apart from 4 input <br>
parameters. these queries never change.<br>
<br>
So will I get any performance benefit by having them in a stored <br>
procedure rather than sending the queries from my Python based API?<br>
<br>
Regards.<br></blockquote><div><br></div><div>Functions and procedures have =
a significant potential to improve performance but there are a few things t=
o watch. Return of experience after having written a few hundreds for a pro=
ject. The list below is by no mean not exhaustive.</div><div><br></div><div=
>1) Warning: Compatibility with other database engines</div><div>If your mo=
del needs to run on other technologies (Oracle, MySQL, MS-SQL, etc.), the i=
ncrease in maintenance efforts may become prohibitive</div><div><br></div><=
div>2) Warning: deadlocks</div><div>Make sure to design the procedures in s=
uch a way that you don&#39;t mutually depend on other parallel invocations<=
/div><div><br></div><div>3) WARNING: Don&#39;t overload the server</div><di=
v>Try to remain conservative in terms of computation in your server. Try to=
 stick to selects, updates, deletes, joins, and simple arithmetics and stri=
ngs manipulations. You can do a lot more but it may quickly affect the over=
all performance.</div><div><br></div><div>The reasons why I would recommend=
 to use them:</div><div><br></div><div>A) Reduction of round trips</div><di=
v>Even though it may be a few ms at a time, it can add up and become signif=
icant. This is where you gain performance.</div><div><br></div><div>B) Impl=
icit transactions</div><div>A function will not free locks until it returns=
. This means that if your queries depend on cells, or modify cells, the beh=
avior will be coherent, reducing the risk of race conditions. If other invo=
cations=C2=A0depend on the same data, the locks will take care of sequencin=
g execution to maintain integrity. In other words, you can safely invoke fu=
nctions in parallel and let the engine do the scheduling when necessary.</d=
iv><div><br></div><div>C) API evolution</div><div>As long as you have only =
1 or 2 applications running against the database, it may not be an issue. I=
f you have more and your model needs to evolve, you may get to a situation =
where updating them all at the same time can become a challenge, especially=
 if you depend on external providers. By using procedures and functions, yo=
u can abstract the model and maintain a standard interface to the applicati=
on.</div><div><br></div><div>Note: * I DON&#39;T RECOMMEND IT * but in some=
 cases it can be handy to have the same function name with different sets o=
f parameters (for instance to present a wrapper with default parameters, an=
d other instances with a finer, more complete control). It can happen if yo=
u don&#39;t clean up timely older versions of the API when you upgrade your=
 model - and it can become a nightmare.</div><div><br></div><div>Last recom=
mendation: activate the logs and review regularly the performance of your f=
unctions. You may identify occurrences=C2=A0that run very fast and others n=
ot so. It can help you identify potential conflicts or model optimizations.=
</div><div><br></div><div>Hope it helps</div><div dir=3D"ltr">--</div><div =
dir=3D"ltr">Olivier Gautherot</div><div dir=3D"ltr"><div><br></div></div><d=
iv>=C2=A0</div></div></div>

--0000000000008bdbbf061ce80b24--