Re: waiting for client write

Would it be a cursor issue on postgres, as there seems to be a
difference in how cursors are handled in postgres and Oracle database. It
seems cursors are returned as buffers to the client side. Below are the
steps we take from jdbc side

below is the stored procedure code:

CREATE OR REPLACE PROCEDURE ."menu_pkg$get_menu_items_p_new"(
i_restaurant_id bigint,
i_category_id bigint,
i_check_availability text,
i_branch_id bigint,
INOUT o_items refcursor,
INOUT o_combo refcursor)
LANGUAGE 'plpgsql'

AS $BODY$

BEGIN

OPEN o_items FOR

SELECT

a.menu_item_id, a.menu_item_name, a.menu_item_category_id,
b.menu_item_category_desc, c.menu_item_variant_id,
c.menu_item_variant_type_id, c.price, c.size_id,
c.parent_menu_item_variant_id, d.menu_item_variant_type_desc, e.size_desc,
f.currency_code, a.image, a.mark_id, m.mark_name

FROM .menu_item_category AS b, .menu_item_variant AS c,
.menu_item_variant_type AS d, .item_size AS e, .restaurant AS f, .menu_item
AS a

LEFT OUTER JOIN .mark AS m ON (a.mark_id = m.mark_id) WHERE
a.menu_item_category_id = b.menu_item_category_id AND 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.restaurant_id =
f.restaurant_id AND f.restaurant_id = i_restaurant_id AND
(a.menu_item_category_id = i_category_id OR i_category_id IS NULL) AND
c.menu_item_variant_id =
(SELECT MIN(menu_item_variant_id) FROM .menu_item_variant WHERE
menu_item_id = a.menu_item_id AND deleted = 'N') AND a.active = 'Y' AND
(CONCAT_WS('', ',', a.hidden_branch_ids, ',') NOT LIKE CONCAT_WS('', '%,',
i_branch_id, ',%') OR i_branch_id IS NULL) AND
.is_menu_item_available(a.menu_item_id, i_check_availability) = 'Y'

ORDER BY a.row_order, menu_item_id;

OPEN o_combo FOR

SELECT

mc.*, f.currency_code, (CASE

WHEN blob_id IS NOT NULL THEN 'Y'

ELSE 'N'

END) AS has_image

FROM .menu_combo AS mc, .restaurant AS f

WHERE mc.restaurant_id = i_restaurant_id AND active = 'Y' AND
mc.restaurant_id = f.restaurant_id AND (menu_item_category_id =
i_category_id OR i_category_id IS NULL)

ORDER BY combo_id;

END;

$BODY$;

1. open connection
2. set auto commit to false
3. create callable statement
4. execute the call
5. get the results
6. set autocommit to true
7. close the resultset,callable statement and connection

On Wed, Jun 9, 2021 at 5:47 PM Ayub Khan <ayub(dot)hp(at)gmail(dot)com> wrote:

> attached is the screenshot of RDS performance insights for AWS and it
> shows high waiting client writes. The api performance is slow. I read that
> this might be due to IOPS on RDS. However we have 80k IOPS on this test
> RDS.
>
> Below is the query which is being load tested
>
> SELECT
>
> a.menu_item_id,
> a.menu_item_name,
> a.menu_item_category_id,
> b.menu_item_category_desc,
> c.menu_item_variant_id,
> c.menu_item_variant_type_id,
> c.price,
> c.size_id,
> c.parent_menu_item_variant_id,
> d.menu_item_variant_type_desc,
> e.size_desc,
> f.currency_code,
> a.image,
> a.mark_id,
> m.mark_name
>
> FROM .menu_item_category AS b, .menu_item_variant AS
> c,
> .menu_item_variant_type AS d, .item_size AS e,
> .restaurant AS f,
> .menu_item AS a
>
> LEFT OUTER JOIN .mark AS m
> ON (a.mark_id = m.mark_id)
>
> WHERE a.menu_item_category_id =
> b.menu_item_category_id AND 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.restaurant_id =
> f.restaurant_id AND f.restaurant_id = 1528 AND
> (a.menu_item_category_id = NULL OR NULL IS NULL)
>
> AND c.menu_item_variant_id = (SELECT
> min(menu_item_variant_id)
> FROM
> .menu_item_variant
> WHERE
> menu_item_id = a.menu_item_id AND deleted = 'N'
> LIMIT 1) AND
> a.active = 'Y'
> AND (CONCAT_WS('', ',', a.hidden_branch_ids,
> ',') NOT LIKE CONCAT_WS('', '%,4191,%') OR
> NULL IS NULL)
> AND .is_menu_item_available(a.menu_item_id, 'Y') = 'Y'
>
> ORDER BY a.row_order, menu_item_id;
>
> --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!!