public inbox for [email protected]help / color / mirror / Atom feed
Cursor with hold for select takes too long 4+ messages / 3 participants [nested] [flat]
* Cursor with hold for select takes too long @ 2024-03-22 16:50 Bindra Bambharoliya <[email protected]> 0 siblings, 1 reply; 4+ messages in thread From: Bindra Bambharoliya @ 2024-03-22 16:50 UTC (permalink / raw) To: [email protected] Hi team, I am facing issues with cursor with hold for select. If I execute select quaery without cursor it takes 13 seconds, If execute it like Begin; Cursor "sql1" with hold for select.... ;fetch 100 to "sql1" It takes more than 3 minutes. Even sometimes it takes more than 10 minutes. What could be the problem? Request to help on this ASAP. I am facing this issue on production. I checked locks etc. But nothing like that. Thanks & Regards Bindra ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Cursor with hold for select takes too long @ 2024-03-22 17:39 Erik Brandsberg <[email protected]> parent: Bindra Bambharoliya <[email protected]> 0 siblings, 1 reply; 4+ messages in thread From: Erik Brandsberg @ 2024-03-22 17:39 UTC (permalink / raw) To: Bindra Bambharoliya <[email protected]>; +Cc: [email protected] What is the actual query being done, and how many rows are being returned? Depending on the query, it could change the execution path, so this is critical to understand. If you are doing an order by on a large result-set, it may be doing a huge amount of processing to get your first 100 rows. On Fri, Mar 22, 2024 at 12:50 PM Bindra Bambharoliya < [email protected]> wrote: > Hi team, > I am facing issues with cursor with hold for select. > > If I execute select quaery without cursor it takes 13 seconds, > If execute it like > Begin; > Cursor "sql1" with hold for select.... ;fetch 100 to "sql1" > It takes more than 3 minutes. > Even sometimes it takes more than 10 minutes. > > What could be the problem? > Request to help on this ASAP. > I am facing this issue on production. > I checked locks etc. But nothing like that. > > > Thanks & Regards > Bindra > ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Cursor with hold for select takes too long @ 2024-03-22 17:47 Bindra Bambharoliya <[email protected]> parent: Erik Brandsberg <[email protected]> 0 siblings, 1 reply; 4+ messages in thread From: Bindra Bambharoliya @ 2024-03-22 17:47 UTC (permalink / raw) To: Erik Brandsberg <[email protected]>; +Cc: [email protected] Hi team, There is no order by, query returns 5341 rows. Query is like Select a.id, sum(b.amount), Count(c.am_id) ... from fact.a join fact.b on a.id= b.id join Fact.vw_c on a.ida=vw_c.ida group by a.id; On Fri, 22 Mar 2024, 23:09 Erik Brandsberg, <[email protected]> wrote: > What is the actual query being done, and how many rows are being > returned? Depending on the query, it could change the execution path, so > this is critical to understand. If you are doing an order by on a large > result-set, it may be doing a huge amount of processing to get your first > 100 rows. > > On Fri, Mar 22, 2024 at 12:50 PM Bindra Bambharoliya < > [email protected]> wrote: > >> Hi team, >> I am facing issues with cursor with hold for select. >> >> If I execute select quaery without cursor it takes 13 seconds, >> If execute it like >> Begin; >> Cursor "sql1" with hold for select.... ;fetch 100 to "sql1" >> It takes more than 3 minutes. >> Even sometimes it takes more than 10 minutes. >> >> What could be the problem? >> Request to help on this ASAP. >> I am facing this issue on production. >> I checked locks etc. But nothing like that. >> >> >> Thanks & Regards >> Bindra >> > ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Cursor with hold for select takes too long @ 2024-03-22 17:57 Tom Lane <[email protected]> parent: Bindra Bambharoliya <[email protected]> 0 siblings, 0 replies; 4+ messages in thread From: Tom Lane @ 2024-03-22 17:57 UTC (permalink / raw) To: Bindra Bambharoliya <[email protected]>; +Cc: Erik Brandsberg <[email protected]>; [email protected] Bindra Bambharoliya <[email protected]> writes: > Query is like > Select a.id, sum(b.amount), > Count(c.am_id) ... from fact.a join fact.b on a.id= b.id join > Fact.vw_c on a.ida=vw_c.ida group by a.id; Is the query plan the same for cursor execution as regular? Compare EXPLAIN SELECT ... versus EXPLAIN DECLARE x CURSOR FOR SELECT ... (I'm not sure that WITH HOLD would make a difference, so maybe compare with and without that, too.) regards, tom lane ^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2024-03-22 17:57 UTC | newest] Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2024-03-22 16:50 Cursor with hold for select takes too long Bindra Bambharoliya <[email protected]> 2024-03-22 17:39 ` Erik Brandsberg <[email protected]> 2024-03-22 17:47 ` Bindra Bambharoliya <[email protected]> 2024-03-22 17:57 ` Tom Lane <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox