Re: Function call very slow from JDBC/java but super fast from DBear
On Tue, 8 Aug 2023 at 17:07, An, Hongguo (CORP) 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.
>
Re: Function call very slow from JDBC/java but super fast from DBear
Hi Dave: Thanks for helping me out. However, I am not sure what do you mean unnamed statement. Which one is using unnamed statement ?(Dbeaver or JDBC), and if the named statement is good, how can I do it from JDBC? The server is in AWS RDS, I don’t see any log, should I reconfig the server to get logs? I tried to Explain call …, but it said syntax error. When the JDBC is running, I got the pid, is there any way for me to check what is it waiting for? There is no dead lock, but some relationship locks, all granted and no waiting, but why it never comes back? Thanks Andrew From: Dave Cramer Date: Wednesday, August 9, 2023 at 6:30 AM To: An, Hongguo (CORP) Cc: [email protected] Subject: Re: Function call very slow from JDBC/java but super fast from DBear WARNING: Do not click links or open attachments unless you recognize the source of the email and know the contents are safe. On Tue, 8 Aug 2023 at 17:07, An, Hongguo (CORP) mailto:[email protected]>> 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.
Re: Function call very slow from JDBC/java but super fast from DBear
On Tuesday, August 8, 2023, An, Hongguo (CORP) 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: > If you are passing in exactly the same text values and running this within the same database then executing the call command via any method should result in the same exact outcome in terms of execution. You can get shared buffer cache variations but that should be it. I’d want to exhaust the possibility that those preconditions are not met before investigating this as some sort of bug. At least, a PostgreSQL bug. I suppose the relative novelty of using call within the JBC driver leaves open the possibility of an issue there. Removing the driver by using PREPARE may help to move things forward (as part of a well written bug report). In any case this is not looking to be on-topic for the performance list. Some more research and proving out should be done then send it to either the jdbc list or maybe -bugs, if not then -general. You may want to install the auto-explain extension to get the inner query explain plan(s) into the logs for examination. David J.
