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$;
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.hp@xxxxxxxxx> 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.5Sun 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!!
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!!
