Optimize SQL statements for better performance

I have a performance issue: when the engine flushes the DB I can see quite some statements being executed, ie ca 150 delete statements on ‘VariableInstanceEntity’ and those take a few seconds to complete altogether (longer then the process itself).
This might be caused by an issue in our environment but still I’d like to know if there is a way to optimize these statements? Eg. could they be executed in batch?
Thanks,
Gabor

Hi Gabor,

FYI, the next release includes batching of statements…

Significant improvement of engine’s performance due to batching of SQL

See latest blog for more detail. Note that MySQL does not seem to benefit from batching as much as other DBs…

regards

Rob

Which “next” release are you referring to, Rob?

Hi Michael,

Im referring to release 7.8 due in the next 24 hours or so…

regards

Rob

1 Like

Hi Webcyberrob,
Thanks for the information.
We switched to H2 DB which performs much faster and hope to find the right configuration to use it in production.
Cheers,
Gabor

Another option you could consider;

Reduce number of process variables to a minimum…
Consider using a single JSON ‘document’ to capture many name/value pairs
Customise history to not store variables in history…

regards

Rob