Re: slow performance with cursor

Justin,

Below is the stored procedure, is there any scope for improvement?

CREATE OR REPLACE PROCEDURE "new_api_pkg$get_menu_details_p"(
i_user_id bigint,
i_menu_item_id bigint,
INOUT o_menu refcursor,
INOUT o_item refcursor,
INOUT o_choice refcursor)
LANGUAGE 'plpgsql'
AS $BODY$
BEGIN
IF i_user_id IS NOT NULL THEN
OPEN o_menu FOR
SELECT
mi.menu_item_id, mi.menu_item_name, mi.menu_item_title,
mi.restaurant_id, case when mi.image !=null then 'Y' when mi.image is null
then 'N' end as has_image,
0.0 AS rating, 0 AS votes, 0 AS own_rating
FROM menu_item AS mi
WHERE mi.menu_item_id = i_menu_item_id AND mi.active = 'Y';
ELSE
OPEN o_menu FOR
SELECT mi.menu_item_id, mi.menu_item_name, mi.menu_item_title,
mi.restaurant_id, case when mi.image !=null then 'Y' when mi.image is null
then 'N' end as has_image,
0.0 AS rating, 0 AS votes, 0 AS own_rating
FROM menu_item AS mi
WHERE mi.menu_item_id = i_menu_item_id AND mi.active = 'Y';
END IF;
OPEN o_item FOR
SELECT
c.menu_item_variant_id, c.menu_item_variant_type_id,
c.package_type_code, c.packages_only, c.price,
CASE
WHEN c.package_type_code = 'P' THEN
(SELECT SUM(miv1.calories) FROM package_component AS
pkg_cpm1
INNER JOIN menu_item_variant AS miv1 ON
pkg_cpm1.component_id = miv1.menu_item_variant_id WHERE pkg_cpm1.package_id
= c.menu_item_variant_id)
ELSE c.calories
END AS calories, c.size_id, c.parent_menu_item_variant_id,
d.menu_item_variant_type_desc, d.menu_item_variant_type_desc_ar,
e.size_desc, e.size_desc_ar,15 AS preparation_time,

(SELECT STRING_AGG(CONCAT_WS('', mi.menu_item_name, ' ',
s.size_desc), ' + '::TEXT ORDER BY pc.component_id)
FROM package_component AS pc, menu_item_variant AS miv,
menu_item AS mi, menu_item_variant_type AS mivt, item_size AS s
WHERE pc.component_id = miv.menu_item_variant_id AND
miv.menu_item_id = mi.menu_item_id AND miv.size_id = s.size_id
AND pc.package_id = c.menu_item_variant_id AND mivt.is_hidden
= 'false' AND mivt.menu_item_variant_type_id = miv.menu_item_variant_type_id
GROUP BY pc.package_id) AS package_name
FROM menu_item AS a, menu_item_variant AS c, menu_item_variant_type
AS d, item_size AS e
WHERE a.menu_item_id = c.menu_item_id AND
c.menu_item_variant_type_id = d.menu_item_variant_type_id AND d.is_hidden =
'false'
AND c.size_id = e.size_id AND a.menu_item_id = i_menu_item_id AND
a.active = 'Y' AND c.deleted = 'N'
ORDER BY c.menu_item_variant_id;
OPEN o_choice FOR
SELECT
c.choice_id, c.choice_name, c.choice_name_ar, c.calories
FROM choice AS c, menu_item_choice AS mc, menu_item AS mi
WHERE c.choice_id = mc.choice_id AND mc.menu_item_id =
mi.menu_item_id AND mc.menu_item_id = i_menu_item_id AND mi.active = 'Y';
END;
$BODY$;

On Fri, Jun 25, 2021 at 7:09 PM Ayub Khan <ayub(dot)hp(at)gmail(dot)com> wrote:

>
> I am using postgresql 12 and using cursors in a stored procedure,
> executing procedure which has cursor is slowing down the call. However if I
> do not use the cursor and just execute the queries using JDBC (Java client)
> it's fast.
>
> Is there any setting which needs to be modified to improve the performance
> of cursors. Also facing slow response with reading blobs (images) from db.
> Not an ideal way for storing images in db but this is a legacy application
> and wanted to check if there a quick tweak which can improve the
> performance while reading blob data from db.
>
> --Ayub
>

--
--------------------------------------------------------------------
Sun Certified Enterprise Architect 1.5
Sun Certified Java Programmer 1.4
Microsoft Certified Systems Engineer 2000
http://in.linkedin.com/pub/ayub-khan/a/811/b81
mobile:+966-502674604
----------------------------------------------------------------------
It is proved that Hard Work and kowledge will get you close but attitude
will get you there. However, it's the Love
of God that will put you over the top!!