Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sRM1O-00Ee1V-Av for pgsql-general@arkaria.postgresql.org; Wed, 10 Jul 2024 01:21:26 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1sRM1M-001Gz7-0q for pgsql-general@arkaria.postgresql.org; Wed, 10 Jul 2024 01:21:24 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sRM1L-001Gxg-Lj for pgsql-general@lists.postgresql.org; Wed, 10 Jul 2024 01:21:23 +0000 Received: from mail-pj1-x1036.google.com ([2607:f8b0:4864:20::1036]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1sRM1E-001IBq-Rt for pgsql-general@lists.postgresql.org; Wed, 10 Jul 2024 01:21:22 +0000 Received: by mail-pj1-x1036.google.com with SMTP id 98e67ed59e1d1-2c980b55741so3557351a91.2 for ; Tue, 09 Jul 2024 18:21:16 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1720574476; x=1721179276; darn=lists.postgresql.org; h=content-transfer-encoding:in-reply-to:from:content-language :references:cc:to:subject:user-agent:mime-version:date:message-id :from:to:cc:subject:date:message-id:reply-to; bh=Kyn221C37C6epWU9gG8yYjn4yF01MG245G6ChE96y2I=; b=aeTivdJGVHDKMDlVkPmvzU8dpPPZ4+rIxtnEoPKn9W+nuJAnKCC/oCb89toF7k+b3j gJ7M4hwUmCPyVfRBTRNkeMWgzEYuvoJMC7VjHE+C+XbIXrnNuHDimLvMygTNeb+6ogIW AaE5+KTKPAGx45IP2RjRWB3F0EnKrdFaynxUZEpgcgkZ4+uRG0FfGEcslC+R7O6YSMrJ MxURJJMqNaX6XhTkHWxnM1AUDsm+eppdvUgM5MYbEyNgne7+kbCUeD1Y316bgzakMsOC B3udTamf8nvyTzK62IXrcC87ltgTVl0r+XjjG7tTjxXyDs/N43IDE3dWZXhFoVQWoMUF qn+A== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1720574476; x=1721179276; h=content-transfer-encoding:in-reply-to:from:content-language :references:cc:to:subject:user-agent:mime-version:date:message-id :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=Kyn221C37C6epWU9gG8yYjn4yF01MG245G6ChE96y2I=; b=vXdAjzP9p05F6s4waOYkFqRrKCX4sebjX5EyG+PnUGo0YMOc4yLXHLaohTWw3zlWYn HbMWRGOByGAWRUv+tF4pQ1GbbW5DXd+pmR+28vP/KVB34SB5ZRRao98inwJfzoP8dGc6 ++E0GABdXTcn9oo2NNsXu+hljtZcODm2TY7pAPZacno49D8c6wBAMTxon9Ux1MFEKWO1 XX+zdV/N/z5ci3Nuax+6UflqtEo83nzuRHFtcGbuZE6wpx/7h8iJq7obzKFwmZ0+8a5Y eF2ncyOYSuQX1eCXPfuaSMYks6SU83IFL8zBtive86yvKax4FsFAcGBiW386R2FN61dI V/AQ== X-Gm-Message-State: AOJu0Yxb/8tZHGa7bTdaiAdi706U9iqrCfjHsnqgZHdOnXM1IqzLDfI8 cuLSvoJ/cLukpNLGX8u9Q5r4IdEPmcr2EoOjjK2Mc/4nkaf9GR0XWPmg4g== X-Google-Smtp-Source: AGHT+IH5FYLbFUG1vAoRmG88a0b/DjEW5qhKWrnItNhNBBTLkqrio/8WB7OF+WuyYK5wK2OXyzsHgw== X-Received: by 2002:a17:90b:a14:b0:2c9:635b:7271 with SMTP id 98e67ed59e1d1-2ca35c71f1fmr3588621a91.21.1720574475586; Tue, 09 Jul 2024 18:21:15 -0700 (PDT) Received: from ?IPV6:2402:e280:3e2e:391:a875:5ac2:5b0b:f105? ([2402:e280:3e2e:391:a875:5ac2:5b0b:f105]) by smtp.gmail.com with ESMTPSA id 98e67ed59e1d1-2ca353c7fe3sm2533021a91.48.2024.07.09.18.21.14 (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Tue, 09 Jul 2024 18:21:15 -0700 (PDT) Message-ID: Date: Wed, 10 Jul 2024 06:51:11 +0530 MIME-Version: 1.0 User-Agent: Betterbird (Linux) Subject: Re: can stored procedures with computational sql queries improve API performance? To: Guyren Howe Cc: "pgsql-general@lists.postgresql.org" References: <04AE80B8-1F00-4758-A498-C1C7C1FC7A31@gmail.com> Content-Language: en-US From: Krishnakant Mane In-Reply-To: <04AE80B8-1F00-4758-A498-C1C7C1FC7A31@gmail.com> Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On 7/10/24 06:44, Guyren Howe wrote: > On Jul 9, 2024, at 17:58, Krishnakant Mane 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? > 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). Basically there are if else conditions and it's not just the queries but the conditional sequence in which they execute. So one single query won't do the job. But Thank you for confirming my understanding. I believe that the fact that stored procedures are compiled also makes them perform faster, is that correct? Regards.