Thanks for your inputs Ernest.
This is now resolved. Apparently, the performance issue happens when the query is returning null and it was resolved by implementing a sql plan from lower environment that successfully completed the query.
------------------------------
Dhinmar James Cayog
Knowledge Community Shared Account
------------------------------
Original Message:
Sent: 04-21-2023 09:02
From: Ernest Jones
Subject: DB Execute Node Performance
That last thing you said about "crappy execution plan" is what stands out to me. Has the DBA team made any suggestions as to why their database system would choose a different execution plan for one connection than it does for the other?
DBAs often have tools that can provide insight into what is running. Can they compare the SQL statements being executed on the database operations on both connections to see that they are the same? Are there database hints in one and not the other? Is it the same database username? Is it on the same network? Are more threads running on one connection than the other and if so, why? Do they run at a different time of day or somehow with a different concurrent demands on the database? What can a DBA point to that is different between the two connections in additional to efficiency of running the query?
------------------------------
Ernest Jones
Precisely Software Inc.
PEARL RIVER NY
Original Message:
Sent: 04-20-2023 11:20
From: Dhinmar James Cayog
Subject: DB Execute Node Performance
Hi,
Good day!
I would like to ask if you can provide insights or suggestions on the slowness we are experiencing in using the DB Execute node in Data360. As a background, the lavastorm job that runs the same node completes in under 3 minutes, while the migrated node to data360 is running for 3+ hours before completing.
The DB Execute Node takes the sql field bindings from the Data Analyzer node.
As per our DBA team, the node runs the SQL within the expected runtime at first, but as it loops through the sql field bindings, it then starts using the crappy execution plan.
Let me know if you need additional information.
Thank you.
------------------------------
Dhinmar James Cayog
Knowledge Community Shared Account
------------------------------