Re: slow performance with cursor

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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.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.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!!

[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux