scripts performance

drumcircle
Rising Star

I've written some LUA load scripts to do pivot transform and am trying to get a sense of whether moving the logic outside of the database would improve performance.

It's my understanding that inserts are not committing until query("commit") is called, so this approach should give all the performance benefits of batch processing.

Also, I am to believe that LUA is running natively in the database and is optimized for performance.

The code has many nested loops, many string concatenations to build long INSERT statements, etc.

The server is currently quite small.

Is running EL"T" with LUA in the database, the best practice?

Thank you!

4 REPLIES 4

exa-Lennart
Team Exasol
Team Exasol

Hi there,

without knowing your code - so knowing exactly what you are trying to do it is a little bit hard to give you specific advice but let me try anyway. First of all you are correct. LUA scripts commit once they finished the end of their execution or if you call query('COMMIT').

It sounds a bit like you are doing heavy processing in a LUA script (one of the scripts that is called via EXECUTE SCRIPT ...). These scripts are stored procedures used to orchestrate for example ETL/ELT and are single threaded. In order to achieve maximum performance I would advise you to write your transformation logic into SCALAR or SET UDFs and call these scripts using a LUA script. This way you can parallelize the execution.

Here are some useful links on the topic:

 

Hope this helps!
Have a nice weekend!

Lennart

drumcircle
Rising Star

UDFs can't interact with the database directly, so each requires a separate connection.

What is the preferred approach for batch inserting 100,000 rows, each requiring a pivot transformation in a LUA script?

drumcircle
Rising Star

I'm seeing < 1% utilization running a very complex script on an x16 node.  It seems like LUA scripts make very modest use of hardware, as you suggest.  Perhaps they are more well suited to per query/session operations.

exa-Lennart
Team Exasol
Team Exasol

Hi,

could you elaborate what you mean by "pivot transformation" in this context?

A preferred approach for batch inserting would be to use the IMPORT statement. However this greatly depends on your use case.