Re: Top -N Query performance issue and high CPU usage

On 2026-02-01 01:16:56 +0530, yudhi s wrote:
> Thank you. 
>
>
> 1) Without even looking at the plan I'm going to say 2-VCPU and 16GB RAM
> and is insufficient resources for what you want to do.
>
>
>
> Can you please explain a bit in detail, how much minimum VCPU and RAM will be
> enough resources to suffice this requirement? and you normally do that
> calculation?

You wrote:

| This query is supposed to allow thousands of users to hit this same
| query at the first landing page at the same time.

If you meant that literally, you would need thousands of cores to handle
those thousands of simultaneous queries and enough RAM for thousands of
sessions, each performing a rather complex query. So possibly hundreds
of maybe even thousands of gigabytes, not 16.

However, maybe you didn't mean that. There are relatively few
applications where thousands of users log in within a second. Maybe you
just meant that there would be thousands of users logged in in total. If
so, how many simultaneus queries do you really expect?

If you do have that many simultaneous accesses to the landing page, and
you can't speed up the query significantly (I take it you've seen the
suggestion to check whether there's an index on
APP_schema.txn_tbl.tran_date), then maybe you don't need to perform it
for every user? I don't know what the query is supposed to do, but
unless the "ent_id" is really a user id, it doesn't seem to be specific
to the user. So maybe you can cache the result for a minute or an hour
and show the same result to everybody who logs in during that time.

hjp

--
_ | Peter J. Holzer | Story must make more sense than reality.
|_|_) | |
| | | hjp(at)hjp(dot)at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"