Re: Function call very slow from JDBC/java but super fast from DBear

On Tue, 8 Aug 2023 at 17:07, An, Hongguo (CORP) <Hongguo(dot)An(at)adp(dot)com> wrote:

> Hi:
>
> I have a function, if I call it from DBeaver, it returns within a minute.
>
>
>
> *call* commonhp.run_unified_profile_load_script_work_assignment_details(
> 'BACDHP', 'G3XPM6YE2JHMSQA2');
>
>
>
>
>
> but if I called it from spring jdbc template, it never comes back:
>
> *public* *void* runTransform(String proc, InitDataLoadEntity entity)
> {
>
> *log*.info("Initial data finished data migration for {},
> starting transform for {}...", entity.getOrganizationOid(), proc);
>
> *var* schema = clientDbInfo.getSchema(entity
> .getOrganizationOid())[1].toUpperCase();
>
> *var* count = unifiedProfileJdbcTemplate.update("call commonhp."
> + proc + "(?, ?)", schema, entity.getOrganizationOid());
>
> *log*.info("Initial data finished data migration for {}, end
> transform for {}, result is {}", entity.getOrganizationOid(), proc, count
> );
>
> }
>
>
>
>
>
> The server does show high CPU, the function has mainly just one insert
> command (batch insert), the target table has 3 FKs.
>

The main difference is that we are going to use an unnamed statement to run
this.

Do you have server logs to see the statement being executed ?

explain plan(s)

Dave

>
>
> Please help.
>
> Thanks
>
> Andrew
>
>
> This message and any attachments are intended only for the use of the
> addressee and may contain information that is privileged and confidential.
> If the reader of the message is not the intended recipient or an authorized
> representative of the intended recipient, you are hereby notified that any
> dissemination of this communication is strictly prohibited. If you have
> received this communication in error, notify the sender immediately by
> return email and delete the message and any attachments from your system.
>